The three asterisks "***" show which Express Bug Fixes (EBFs) and GA builds have appeared on the website since the previous version of this page.

Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.

The three asterisks "***" show which Express Bug Fixes (EBFs) and GA builds have appeared on the website since the previous version of this page.

Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0.0) are shown here.

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.

Let's say you want to keep a complete record of every INSERT, UPDATE and DELETE made to a particular table. Furthermore, let's say you want to store this audit trail in the database itself to make it easy to write queries.

You can use SQL Anywhere's CREATE TRIGGER feature to do all that, and with the help of several other features like INSERT WITH AUTO NAME the code becomes quite simple.

Because of the way the audit trail triggers work, the rows in logged_item with the same sku (stock keeping unit) value can be ordered on log_id to give the complete chronological history of that sku...

...and the last row inserted before January 1, 2013 tells you the state of that sku in the year-end inventory.

A perfect application for the LAST_VALUE() function and the WINDOW clause, right?

SELECT LAST_VALUE ( log_id ) OVER sku_window AS last_log_id
FROM logged_item
WHERE logged_at < '2013-01-01'
WINDOW sku_window AS ( PARTITION BY sku
ORDER BY log_id )
ORDER BY last_log_id;

The WHERE clause grabs everything in logged_item up to midnight on December 31, 2012,

the PARTITION BY sku clause creates a separate partition in the window for each sku,

the inner ORDER BY log_id clause sorts the partition so the LAST_VALUE ( log_id ) function will return the last row in the chronological history of each partition,

the "LAST_VALUE ( log_id ) OVER sku_window AS last_log_id" select-list entry computes the last log_id for each sku and gives it an alias name "last_log_id", and

It turns out that LAST_VALUE() needs the WINDOW clause to have a full-tilt-boogie RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING specification (the docs say that's the default but reality intrudes):

SELECT LAST_VALUE ( log_id ) OVER sku_window AS last_log_id
FROM logged_item
WHERE logged_at < '2013-01-01'
WINDOW sku_window AS ( PARTITION BY sku
ORDER BY log_id
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING )
ORDER BY last_log_id;

The result isn't much better, still WAY too many rows (16,806), but at least they look different:

At this point, one must have faith: the WINDOW clause works, and so does LAST_VALUE(), and they aren't just powerful, they are fast too!

The problem here is it's returning one row for every row in logged_item, and those rows repeat the LAST_VALUE() for each partition. That's the way partitions work, it's something you (I) must get used to.

...unlike the RANGE nonsense, which may forever remain a mystery. Like waving a dead chicken over the keyboard, if your WINDOW query doesn't work try RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING!

What's the obvious solution to duplicate rows?

Why, the DISTINCT keyword, of course!

...one of the most loved, most used, most mis-used dead chickens in the SQL toolbox!

Here it is, solving the repeating-LAST_VALUE problem:

SELECT DISTINCT LAST_VALUE ( log_id ) OVER sku_window AS last_log_id
FROM logged_item
WHERE logged_at < '2013-01-01'
WINDOW sku_window AS ( PARTITION BY sku
ORDER BY log_id
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING )
ORDER BY last_log_id;

Now instead of 16,806 rows there are only 856, one row per sku:

last_log_id
-----------
297
369
1084
1164
...
16806

Each row in that result set points to a single row in logged_item, and that row shows the last action before year end for the corresponding item row:

logged_action = 'after INSERT' for an item that was inserted before year-end,

logged_action = 'after UPDATE' for an item that was updated before year-end, and

logged_action = 'before DELETE' for an item that was deleted before year-end.

Monday, March 4, 2013

"...we plan to make changes to allow corrupted databases to be stopped without bringing down the server. Printing database information to related assertion failures should be part of that."

Ta Daaaa!

Relief is here, now, with SQL Anywhere 16's new dbsrv16 -ufd abort, restart, escalate option that specifies "the action that the database server takes when a fatal error or assertion failure occurs on a database."

Well, it's ALMOST here, and will be as soon as SAP sets SQL Anywhere 16 loose.

The dbsrv16 -ufd option applies to database fatal errors and assertions, not server errors... if you get an error related to the server itself, then presumably the server still stops, just like it does now no matter which kind of fatal error you get.

Caveat Emptor: The word "presumably" is shorthand for "I have not seen dbsrv16 -ufd in operation yet"... eventually, I will... I get more than enough assertion errors without having to make one happen on purpose.

But, for now, this article is really just me reading the Help to you :)

Here are the choices:

dbsrv16 -ufd abort "The affected database is shut down. The statuses of the database server and other databases remain unchanged."

This is the new default, which is different from the current behavior in Version 12 (see "escalate" below). This setting makes sense if you're really not expecting assertions, and you want to kick the users offline until you fix the database.

dbsrv16 -ufd restart "The affected database is shut down with an attempt to restart the database. If the restart attempt fails, a database server assertion failure is raised."

This setting makes sense for lights-out operations at both ends of the spectrum: A single assertion in one database among hundreds on a SQL Anywhere server doesn't cause them all to halt, and an embedded database application may be designed so the user can seamlessly reconnect and carry on past a transient database assertion. The latter is what Foxhound's going to do in its next release... and, I think, it's going to be a popular choice for a lot of people.

You can use Sybase Central to compare two databases. The comparison generates SQL statements that you can review to determine the differences between two databases. You can execute the SQL statements to make the one database the same as the other database.

I like the "making them the same" part... let's see how it all works:

The "Objects" tab lists all the bits and pieces in the database, from tables down to user ids and unique constraints:

The filter-as-you-type "Search" field is very fast,

which more than makes up for the strange default sort order,

plus you can change the sort order by clicking on the column headings, and

clicking on a matching pair in the top frame brings up a side-by-side comparison of those objects in the bottom frame.

The "SQL Script" tab presents a dbunload-style script that changes all the objects in Database 1 to look (more or less) like the objects in Database 2:

Without a Search field, however, the SQL Script display is singularly useless... it's way too big to scroll through, and even if you find what you're looking for you can't do anything with it (no select, so no copy and paste).

What you CAN do, however, is press the Save As... button and then use your favorite editor (ISQL, Wordpad, whatever) to yank out the bits you want, like this:

There are some restrictions, like it only works on database files created with SQL Anywhere 10 or later, plus those databases have to be running on SQL Anywhere 12 engines, and it won't change the order of columns in a table: