Database Monitor Email Alerts

The ability to send email alerts warning of Bad Things Happening to your SQL Anywhere database server is the release-defining feature of the new SQL Anywhere Server Monitor now available in beta. Here's one of those emails, this one warning about high CPU usage:



The monitor lets you pick and choose what you want to be warned about:



It's a two-part process to set up who gets the warnings. First, you set up the email server...



Then, you define the email addresses:



Email alerts work OK with ordinary SMTP servers like the one shown here, and the monitor does allow input of a user name and password for authentication purposes.

As far as I can tell everything works fine with ordinary SMTP servers, but it chokes on a server like smtp.gmail.com with the message "Email Server for Alerts not Configured". This may be because the Google Mail server requires transport layer security (TLS) and SQL Anywhere's email code doesn't support that.

I'm just guessing at the connection between the SQL Anywhere Monitor and SQL Anywhere's email support, but it's an educated guess: a standalone test calling xp_startsmtp from a SQL Anywhere 11 database returns -530 which (I think) means "Must issue a STARTTLS command first."

But, that's a minor quibble. The SQL Anywhere Monitor's support for email alerts gives it a significant advantage over its only real competition, the Foxhound monitor.

First EBF for SQL Anywhere 11 Ships: 11.0.0.1490

Note: This is a re-posting of an item first posted on October 1... the Windows versions of the EBF had a problem with the installer but they're back now.

To get the latest Express Bug Fix for SQL Anywhere 11, go to here, click on SQL Anywhere, and scroll down to one of these choices:

Platform: Linux x86/Linux x64
SQL Anywhere - Express Bug Fix, Update of 11.0.0 to build 1490 (all components) - 11.0 - 01 Oct 2008 - EBF/Patch

Platform: Windows x64
SQL Anywhere - Express Bug Fix, Update of 11.0.0 to build 1490 (all components except SQL Anywhere for Windows CE) - 11.0 - 10 Oct 2008 - EBF/Patch

Platform: Windows x86
SQL Anywhere - Express Bug Fix, Update of 11.0.0 to build 1490 (all components except SQL Anywhere for Windows CE) - 11.0 - 10 Oct 2008 - EBF/Patch

Kudos on the fully-descriptive file names; e.g., SA11_Full-CE_x64.1100_1490_EBF.exe. Heads up on one part of the name, though: "Full-CE" doesn't mean "Full download for Windows CE", it means "Everything EXCEPT Windows CE".

Also shipping for the first time: SQL Anyhwhere 11.0 for MAC OS X.

No More Excuses...

...for not making regular database backups.



I'm used to seeing price drops, but once in a while one comes along that makes me sit up and take notice: $155 for a 1 TB (well, 1000 MB actually) 7200 rpm USB 2.0 external drive that transfers data up to 480 MB per second.

OK, ok, so you're not going to use it for TPC-C benchmarking, but for backing up your database? Perfect! Especially if you buy two or more for safety.

And now it's affordable:

$155 in the United States,

$170 in Canada.

"Sybase is donating its software"

Now here's something you don't see very often:

"Sybase is donating its software"

I hope the idea spreads!

Loading Empty Lines

From time to time, I need to load raw text data into a table, line by line, preserving the original text and the original order of lines. The LOAD TABLE command does an excellent job, as follows:

CREATE TABLE raw_text (
   line_number BIGINT NOT NULL 
                  DEFAULT AUTOINCREMENT
                  PRIMARY KEY CLUSTERED,
   line_text   LONG VARCHAR NOT NULL DEFAULT '' );

LOAD TABLE raw_text ( line_text )
   FROM 'c:/temp/xxx.txt'
   DEFAULTS ON 
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTES OFF 
   STRIP OFF;

SELECT * FROM raw_text ORDER BY line_number;

When the input file looks like this...

first line

third line

...the SELECT displays this...



Now, I said that LOAD TABLE did an "excellent job" and it does... if the input file doesn't have any empty lines, or if you don't want to preserve empty lines. Because they are completely ignored.

If you do want to preserve empty input lines, there is a solution: include the ROW DELIMITED BY option like this:

LOAD TABLE raw_text ( line_text )
   FROM 'c:/temp/xxx.txt'
   DEFAULTS ON 
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTES OFF 
   ROW DELIMITED BY '\x0d\x0a'
   STRIP OFF;

SELECT * FROM raw_text ORDER BY line_number;

Now the SELECT shows all three lines, including the empty one:



The ROW DELIMITED BY option was added in SQL Anywhere version 10, another reason to upgrade... but if you're not using version 10 already, consider moving straight to version 11.

Divide and Conquer by Working Backwards

In previous postings here and here I have talked about solving large SQL query problems by splitting them into smaller problems that are easier to solve: divide and conquer.

Yeah, right... easier said than done... what if it isn't obvious how to split up the big problem into manageable pieces?

One way is to propose "a perfect world" in which the large problem has a simple solution, and work backwards from there. For a database query problem, that means starting with "a perfect schema" designed with one purpose in mind, to make it easy to solve the large problem, and then work backwards step-by-step to devise less-perfect schemas.

At each step of the way the less-perfect schema should meet two criteria: it should be relatively easy to transform the less-perfect schema to the previous more-perfect schema, and each less-perfect schema should be closer to reality... where reality is the ugly real-world schema that made the query hard to write in the first place. The problem is solved when you have a series of queries that transforms the real-world schema into the solution result set.

In other words, "divide and conquer" describes the end result whereas "working backwards" provides a possible path to that result. Here's an example:

Using a bank account database containing separate tables for opening balance, deposits, withdrawals and checks, display each entry together with a closing balance after the entry is applied. The entries are to be ordered by date, then within date by opening balance first followed by deposits, withdrawals and checks.

date        description                    amount  closing_balance
2008-02-01  FY009 Opening Balance         2329.54          2329.54
2008-02-19  DEP - insurance - adjustment   174.29          2503.83
2008-02-21  DEP - tax - refund             482.95          2986.78
2008-02-21  WD - cash - ATM               -100.00          2886.78
2008-02-21  CHK 301 - Township - dogtags   -40.00          2846.78
2008-02-21  CHK 300 - hydro - invoice     -283.53          2563.25
2008-08-15  WD - cash - ATM               -100.00          2463.25

Start With The Perfect Schema

Here's what "the perfect schema" might look like, one single table containing the opening balance and all the deposits, withdrawals and checks as individual rows:

CREATE TABLE solution ( 
   transaction_number INTEGER NOT NULL,
   "date"             DATE NOT NULL,
   description        VARCHAR ( 200 ) NOT NULL,
   amount             NUMERIC ( 9, 2 ) NOT NULL,
   closing_balance    NUMERIC ( 9, 2 ) NOT NULL,
   PRIMARY KEY ( transaction_number );

The transaction_order column specifies the order in which the closing_balance values are calculated as well as the sort order for the final query:

SELECT solution."date",
       solution.description,
       solution.amount,
       solution.closing_balance
  FROM solution
 ORDER BY solution.transaction_number;

1st Step Back: Calculate Closing Balance

It should come as no surprise that the real-world schema looks nothing like the perfect solution table. In fact, there are four tables, not one, and there are no transaction_number or closing_balance columns:

CREATE TABLE opening_balance ( 
   opening_balance_date   DATE NOT NULL,
   amount                 NUMERIC ( 9, 2 ) NOT NULL,
   description            VARCHAR ( 200 ) NOT NULL,
   PRIMARY KEY ( opening_balance_date ) );

opening_balance_date   amount  description
--------------------  -------  ---------------------
2008-02-01            2329.54  FY009 Opening Balance 

CREATE TABLE deposit ( 
   deposit_number  INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   deposit_date    DATE NOT NULL,
   paid_by         VARCHAR ( 50 ) NOT NULL,
   amount          NUMERIC ( 9, 2 ) NOT NULL,
   description     VARCHAR ( 200 ) NOT NULL,
   PRIMARY KEY ( deposit_number ) );

deposit_number  deposit_date  paid_by    amount  description
--------------  ------------  ---------  ------  -----------
1               2008-02-19    insurance  174.29  adjustment 
2               2008-02-21    tax        482.95  refund 

CREATE TABLE withdrawal ( 
   withdrawal_number  INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   withdrawal_date    DATE NOT NULL,
   paid_to            VARCHAR ( 50 ) NOT NULL,
   amount             NUMERIC ( 9, 2 ) NOT NULL,
   description        VARCHAR ( 200 ) NOT NULL,
   PRIMARY KEY ( withdrawal_number ) );

withdrawal_number  withdrawal_date  paid_to  amount  description
-----------------  ---------------  -------  ------  -----------
1                  2008-02-21       cash     100.00  ATM 
2                  2008-08-15       cash     100.00  ATM 

CREATE TABLE "check" ( 
   check_number  INTEGER NOT NULL,
   issue_date    DATE NOT NULL,
   pay_to        VARCHAR ( 50 ) NOT NULL,
   amount        NUMERIC ( 9, 2 ) NOT NULL,
   check_for     VARCHAR ( 35 ) NOT NULL,
   PRIMARY KEY ( check_number ) );

check_number  issue_date  pay_to    amount  check_for
------------  ----------  --------  ------  ---------
300           2008-02-21  hydro     283.53  invoice 
301           2008-02-21  Township   40.00  dogtags 

But, we're working backwards... let's pretend there is a single table that holds all the entries, and it does have a transaction_number column but not closing_balance. Here's how it can be used to calculate closing_balance:

CREATE TABLE transaction ( 
   transaction_number INTEGER NOT NULL,
   "date"             DATE NOT NULL,
   description        VARCHAR ( 200 ) NOT NULL,
   amount             NUMERIC ( 9, 2 ) NOT NULL,
   PRIMARY KEY ( transaction_number );

CREATE VIEW solution AS
SELECT transaction.transaction_number,
       transaction."date",
       transaction.description,
       transaction.amount,
       SUM ( transaction.amount )
             OVER balance_window AS closing_balance
  FROM transaction
WINDOW balance_window AS (
           ORDER BY transaction.transaction_number
           RANGE BETWEEN UNBOUNDED PRECEDING
                 AND     CURRENT ROW );  

The transaction table is the "less-perfect schema", one step closer to reality (it doesn't have the balance column). The solution table has been replaced by a view which performs the transformation to the perfect schema as follows:

• A OLAP WINDOW clause is used to define a "balance_window" for each row in the result set.

• The balance_window is cumulative: it includes the current row as well as all the preceding rows: BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

• The transaction_number column controls the ordering of rows in the balance_window.

• The aggregate expression SUM ( transaction.amount ) OVER balance_window calculates the closing_balance value for each row.

Tip for English Speakers: The phrase "UNBOUNDED PRECEDING" may be translated from Pointy-Haired-ANSI-Speak to English as follows: "first row". In other words, the BETWEEN clause really means BETWEEN FIRST ROW AND CURRENT ROW. Similarly, "UNBOUNDED FOLLOWING" really means "last row".

2nd Step Back: Calculate Transaction Number

The next step backwards is to propose an even-less-perfect table raw_transaction that doesn't have either closing_balance or transaction_number, but does include a transaction_type column that differentiates between opening_balance, deposit, withdrawal and check as sort_order = '1', '2', '3' and '4':

CREATE TABLE raw_transaction ( 
   "date"       DATE NOT NULL,
   sort_order   VARCHAR ( 1 ) NOT NULL,
   description  VARCHAR ( 200 ) NOT NULL,
   amount       NUMERIC ( 9, 2 ) NOT NULL,
   PRIMARY KEY ( "date",
                 sort_order,
                 description,
                 amount );

The raw_transaction table may be transformed into the transaction view (formerly the transaction table) by using the OLAP ROW_NUMBER() aggregate function to calculate the transaction_number column:

CREATE VIEW transaction (
   transaction_number,
   "date",
   description,
   amount )   
AS
SELECT ROW_NUMBER() OVER ( 
          ORDER BY 
             raw_transaction."date",
             raw_transaction.sort_order ) 
          AS transaction_number,  
       raw_transaction."date",
       raw_transaction.description,
       raw_transaction.amount
  FROM raw_transaction;  

The OVER ( ORDER BY ... ) clause tells ROW_NUMBER() what row order to use when doing the numbering. Astute observers will note that date and sort_order aren't fully deterministic because more than one row can have the same values, but that doesn't matter because the original specs weren't fully deterministic either. In other words, if there are two withdrawals on the same day it doesn't matter which order they appear in, only that they appear together, after all the deposits and before all the checks on that day.

3rd Step Back: Gather The Raw Transactions

The final step backwards replaces the raw_transaction table with a view that uses a UNION of four selects that pound the individual tables into one lowest-common-denominator representation of the data.

Here is that view, plus the other three queries that together form the fully set oriented "divide and conquer" solution:

Correction:The first two columns in the CREATE VIEW column list appeared in the wrong order when this article was first posted. The error might have been caught if the actual output had been included (it is now, down at the bottom) instead of just the original "specs".

CREATE VIEW raw_transaction (
   "date",
   sort_order,
   description,
   amount )   
AS
SELECT opening_balance.opening_balance_date,
       '1',
       opening_balance.description,
       opening_balance.amount
  FROM opening_balance
UNION ALL
SELECT deposit.deposit_date,
       '2',
       STRING (
          'DEP - ',
          deposit.paid_by,
          ' - ',
          deposit.description ),
       deposit.amount
  FROM deposit
UNION ALL
SELECT withdrawal.withdrawal_date,
       '3',
       STRING (
          'WD - ',
          withdrawal.paid_to,
          ' - ',
          withdrawal.description ),
       -withdrawal.amount
  FROM withdrawal
 UNION ALL
SELECT "check".issue_date,
       '4',
       STRING (
          'CHK ',
          "check".check_number,
          ' - ',
          "check".pay_to,
          ' - ',
          "check".check_for),
       -"check".amount
  FROM "check";

CREATE VIEW transaction (
   transaction_number,
   "date",
   description,
   amount )   
AS
SELECT ROW_NUMBER() OVER ( 
          ORDER BY 
             raw_transaction."date",
             raw_transaction.sort_order ) 
          AS transaction_number,  
       raw_transaction."date",
       raw_transaction.description,
       raw_transaction.amount
  FROM raw_transaction;  

CREATE VIEW solution AS
SELECT transaction.transaction_number,
       transaction."date",
       transaction.description,
       transaction.amount,
       SUM ( transaction.amount )
             OVER balance_window AS closing_balance
  FROM transaction
WINDOW balance_window AS (
           ORDER BY transaction.transaction_number
           RANGE BETWEEN UNBOUNDED PRECEDING
                 AND     CURRENT ROW );  

SELECT solution."date",
       solution.description,
       solution.amount,
       solution.closing_balance
  FROM solution
 ORDER BY solution.transaction_number;

The final output shows the two checks in a different order from the original example, but that's OK because the specs didn't say how they should be sorted:

date        description                    amount  closing_balance
2008-02-01  FY009 Opening Balance         2329.54          2329.54
2008-02-19  DEP - insurance - adjustment   174.29          2503.83
2008-02-21  DEP - tax - refund             482.95          2986.78
2008-02-21  WD - cash - ATM               -100.00          2886.78
2008-02-21  CHK 300 - hydro - invoice     -283.53          2603.25
2008-02-21  CHK 301 - Township - dogtags   -40.00          2563.25
2008-08-15  WD - cash - ATM               -100.00          2463.25

SQL Anywhere Monitor Beta

The SQL Anywhere Monitor is an add-on to SQL Anywhere 11:

"... a browser-based administration tool that provides you with information about the health and availability of SQL Anywhere databases and MobiLink servers. The SQL Anywhere Monitor collects metrics and performance data from databases and MobiLink servers running on other computers, while a separate computer accesses the SQL Anywhere Monitor via a web browser."

Here's where you can download a beta copy:

http://www.sybase.com/panoramabeta