Thursday, March 28, 2013

Latest SQL Anywhere EBF 12.0.1.3867 for Windows

Current builds for the active platforms...

HP-UX     12.0.1.3798 EBF           29 Oct 2012
 Itanium  11.0.1.2879 EBF           31 Oct 2012

IBM AIX   12.0.1.3798 EBF           24 Oct 2012
          11.0.1.2879 EBF           29 Oct 2012

Linux     16.0.0.1324 GA            05 Mar 2013
          12.0.1.3853 EBF           19 Mar 2013
          11.0.1.2913 EBF           19 Dec 2012

Mac OS    12.0.1.3853 EBF           22 Feb 2013
          11.0.1.2449 EBF           29 Jun 2010

Solaris   12.0.1.3798 EBF           24 Oct 2012
 SPARC    11.0.1.2913 EBF (SA)      19 Dec 2012

Solaris   12.0.1.3798 EBF           29 Oct 2012
 x64      11.0.1.2879 EBF           29 Oct 2012

Windows   16.0.0.1324 GA            05 Mar 2013
          12.0.1.3867 EBF       *** 22 Mar 2013 ***
          12.0.1 French Docs,       25 Sep 2012
                 English Docs,      25 Sep 2012
                 German Docs        25 Sep 2012
          11.0.1.2913 EBF           21 Dec 2012

Other Stuff...

 Older EBFs

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

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.


Thursday, March 21, 2013

Latest SQL Anywhere EBF: 12.0.1.3853 for Linux

Current builds for the active platforms...

HP-UX     12.0.1.3798 EBF           29 Oct 2012
 Itanium  11.0.1.2879 EBF           31 Oct 2012

IBM AIX   12.0.1.3798 EBF           24 Oct 2012
          11.0.1.2879 EBF           29 Oct 2012

Linux     16.0.0.1324 GA        *** 05 Mar 2013 ****
          12.0.1.3853 EBF       *** 19 Mar 2013 ****
          11.0.1.2913 EBF           19 Dec 2012

Mac OS    12.0.1.3853 EBF           22 Feb 2013
          11.0.1.2449 EBF           29 Jun 2010

Solaris   12.0.1.3798 EBF           24 Oct 2012
 SPARC    11.0.1.2913 EBF (SA)      19 Dec 2012

Solaris   12.0.1.3798 EBF           29 Oct 2012
 x64      11.0.1.2879 EBF           29 Oct 2012

Windows   16.0.0.1324 GA        *** 05 Mar 2013 ****
          12.0.1.3851 EBF           22 Feb 2013
          12.0.1 French Docs,       25 Sep 2012
                 English Docs,      25 Sep 2012
                 German Docs        25 Sep 2012
          11.0.1.2913 EBF           21 Dec 2012

Other Stuff...

 Older EBFs

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

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.


Monday, March 18, 2013

Reporting and the Audit Trail

Index to all the "Audit Trail" posts
February 23, 2008 Tip: Triggering an Audit Trail
February 28, 2009 Revisited: Triggering an Audit Trail
March 24, 2009 ALTER TABLE and the Audit Trail
March 18, 2013 Reporting and the Audit Trail

One of the most popular articles on this blog was published in February 2008: Tip: Triggering an Audit Trail.

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.

[ ...more ]
A year later, after some real-world experience, the code was rewritten and the article republished: Revisited: Triggering an Audit Trail.

And shortly after that, the question "How do I add a column to a table being audited?" was answered in ALTER TABLE and the Audit Trail.

Now it's four years since the original article and the technique is still working, still answering those "What the heck happened?" questions that crop up from time to time.

Not just bug hunting, however; the audit trail can answer questions like "What exactly was in the inventory back on December 31, 2012? The accountants are asking..."
  • "Didn't you remember to take a snapshot of the inventory at midnight on December the 31st?"
    ...nope, forgot

  • "Didn't you build something into the application to record the year-end inventory?"
    ...nope, that didn't happen either

  • "Can't you restore the last backup taken on December 31 and run a query?"
    ...nope, don't keep them around that long
What can be done, and it requires no more effort (or planning) than to write a SELECT, is to use the data in the audit trail to compute the state of the database at year end.

Here's what the tables look like, first the table holding the current inventory, and then the shadow table holding 17,000 before-and-after-images of rows in the item table:
CREATE TABLE item ( -- 873 rows, 3.7M total = 152k table + 3.5M ext + 32k index, 4,401 bytes per row
   sku                      VARCHAR ( 20 ) NOT NULL,
   sku_integer_suffix       UNSIGNED INT NOT NULL 
                               COMPUTE ( integer_suffix(sku) ),
   show_on_website          VARCHAR ( 1 ) NOT NULL DEFAULT 'N' 
                               CONSTRAINT ASA90 CHECK (  
                                  show_on_website in( 'Y','N' )  ),
   usd_price                DECIMAL ( 11, 2 ) NOT NULL DEFAULT 0.0,
   usd_shipping             DECIMAL ( 11, 2 ) NOT NULL DEFAULT 0.0,
   cad_shipping             DECIMAL ( 11, 2 ) NOT NULL DEFAULT 0.0,
   featured                 VARCHAR ( 1 ) NOT NULL DEFAULT 'N' 
                               CONSTRAINT ASA91 CHECK (  
                                  featured in( 'Y','N' )  ),
   stock                    INTEGER NOT NULL DEFAULT 1,
   main_category            VARCHAR ( 50 ) NOT NULL,
   updated_at               TIMESTAMP NOT NULL DEFAULT timestamp,
   title                    VARCHAR ( 100 ) NOT NULL DEFAULT '',
   text_description         LONG VARCHAR NOT NULL DEFAULT '',
   active                   VARCHAR ( 1 ) INLINE 1 PREFIX 1 NOT NULL DEFAULT 'Y' 
                               CONSTRAINT ASA92 CHECK (  
                                  active in( 'Y','N' )  ),
   display_order            BIGINT NOT NULL DEFAULT 0,
   promotional_message      LONG VARCHAR INLINE 256 PREFIX 8 NOT NULL DEFAULT '',
   promotional_message_at   TIMESTAMP NOT NULL DEFAULT '1900-01-01',
   CONSTRAINT ASA93 PRIMARY KEY ( -- 16k
      sku )
 );

CREATE TABLE logged_item ( -- 17,073 rows, 20.5M total = 3.3M table + 17M ext + 160k index, 1,260 bytes per row
   log_id                   UNSIGNED BIGINT NOT NULL DEFAULT autoincrement,
   logged_action            VARCHAR ( 50 ) NOT NULL 
                               CONSTRAINT ASA94 CHECK ( logged_action in( 
                                  'after INSERT', 
                                  'before UPDATE', 
                                  'after UPDATE', 
                                  'before DELETE' )  ),
   logged_at                TIMESTAMP NOT NULL DEFAULT timestamp,
   sku                      VARCHAR ( 20 ) NULL,
   sku_integer_suffix       UNSIGNED INT NULL,
   show_on_website          VARCHAR ( 1 ) NULL,
   usd_price                DECIMAL ( 11, 2 ) NULL,
   usd_shipping             DECIMAL ( 11, 2 ) NULL,
   cad_shipping             DECIMAL ( 11, 2 ) NULL,
   featured                 VARCHAR ( 1 ) NULL,
   stock                    INTEGER NULL,
   main_category            VARCHAR ( 50 ) NULL,
   updated_at               TIMESTAMP NULL,
   title                    VARCHAR ( 100 ) NULL,
   text_description         LONG VARCHAR NULL,
   active                   VARCHAR ( 1 ) NULL,
   display_order            BIGINT NULL,
   promotional_message      LONG VARCHAR NULL,
   promotional_message_at   TIMESTAMP NULL,
   CONSTRAINT ASA95 PRIMARY KEY ( -- 160k
      log_id )
 );
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

  • the outer ORDER BY last_log_id clause sorts the final result set.
Alas, the results are worse-than-useless, just the numbers 1, 2, 3:
last_log_id
----------- 
1
2
3
4
5
6
7
8
9
10
11
12
...
16804
16805
16806
Heck, I can do that by calling sa_rowgenerator!

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:
last_log_id          
----------- 
297                  
297                  
297                  
369                  
369                  
369                  
1084                 
1084                 
1084                 
1164                 
1164                 
1164       
...          
16806
16806
16806
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.
There are no entries where logged_action = 'before UPDATE' because that action is always followed by an 'after UPDATE' with the same logged_at timestamp (once again, that's the way the audit trail triggers work).

In other words, that query embodies the year-end snapshot that accounting wants, and it can be made useful as a view:

CREATE VIEW year_end_2012_logged_item AS 
   ( 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 ) );
Here's an example of how that view was used to summarize the year-end inventory by category:
SELECT logged_item.main_category                           AS Category,
       COUNT(*)                                            AS SKU_count,
       SUM ( logged_item.stock )                           AS item_count,
       SUM ( logged_item.stock * logged_item.usd_price )   AS inventory_value
  FROM logged_item
          INNER JOIN year_end_2012_logged_item
                  ON year_end_2012_logged_item.last_log_id = logged_item.log_id
 WHERE logged_item.logged_action   IN ( 'after INSERT', 'after UPDATE' )
   AND logged_item.show_on_website = 'Y'
   AND logged_item.main_category   <> 'BOOKS'
 GROUP BY logged_item.main_category
 ORDER BY logged_item.main_category;

Category             SKU_count   item_count  inventory_value                 
-------------------- ----------- ----------- ---------------
COLLECTIBLES         18          18          4409.00
GIFTWARE             19          25          663.00
LINENS & TEXTILES    48          48          1804.00
POTTERY & GLASS      61          152         5566.00
SILVERWARE           106         111         4342.00
  • The INNER JOIN uses the year_end_2012_logged_item view to identify which logged_item rows form the year-end snapshot,

  • the WHERE eliminates the deletions, and applies two business-related predicates (include active items, exclude books), and

  • the GROUP BY enables the COUNT() and SUM() calculations.
It's worth noting that the item table doesn't take part in any of these queries, nor can it because it represents the current state of inventory and accounting only cares about year end.

Other queries can be written, to show detail or summaries, by using this template:

SELECT [whatever columns and/or aggregate function calls you want]
  FROM logged_item
          INNER JOIN year_end_2012_logged_item
                  ON year_end_2012_logged_item.last_log_id = logged_item.log_id
 WHERE logged_item.logged_action   IN ( 'after INSERT', 'after UPDATE' )
   AND [whatever other predicates you want]
 [plus whatever GROUP BY and ORDER BY clauses you might need]
Suddenly, the audit trail tables and triggers are justified not only because of safety and security, but because they provide significant reporting support with zero extra effort.

Monday, March 11, 2013

SQL Anywhere 16 Synchronization Webcast

Tom Slee's webcast Discover the New Data Synchronization Features in SAP Sybase SQL Anywhere 16 is two days from today, on Wednesday, March 13, 2013 at 1:00 PM EST...


During this Webcast, you’ll see live demos and hear Tom Slee, product manager at SAP Canada, discuss new data synchronization highlights and benefits.

You’ll also learn about:
  • Improved synchronization and mobile database performance features that help ensure best-in-class performance of your mobile apps – regardless of deployment platform

  • Innovative profiling tools that allow you to drill down into your synchronization environment – so you can identify potential issues quickly and efficiently

  • Enhanced design and development tools that enable you to more easily build and deploy custom-designed synchronization scripts

  • Greater support for data synchronization with SAP HANA – allowing you to easily extend your next-generation in-memory applications to mobile environments
This is your opportunity to learn about all that SAP Sybase SQL Anywhere 16.0 has to offer in data synchronization.

We hope you’ll attend this valuable Webcast.


Some other links...



Friday, March 8, 2013

Tuesday, March 5, 2013

It's Here! SQL Anywhere 16 Developer Edition Download

Ready for download now: SAP Sybase SQL Anywhere 16 Developer Edition Registration.

Also available: The docs for SQL Anywhere 16.

Coming soon: Jason Hinsperger's webcast Data Management Features in SAP Sybase SQL Anywhere 16.0 on Wednesday, March 6, 2013 1:00 PM EST.


SQL Anywhere 16 Webcast and Docs

The docs for SQL Anywhere 16 are now online, can the actual software be far behind?

In the meantime, you can attend Jason Hinsperger's webcast Data Management Features in SAP Sybase SQL Anywhere 16.0 on Wednesday, March 6, 2013 1:00 PM EST.



Monday, March 4, 2013

SQL Anywhere 16 Sneak Peek: Abort, Retry, Escalate

Today marks the second anniversary of a promise reported in this request for "Assertion Relief":

"...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.

  • dbsrv16 -ufd escalate "The database assertion failure or fatal error is treated as a database server assertion failure or fatal error."

    That is the Old Way Of Doing Things... when a light burns out in the lavatory on the flight from LA to Seoul, the whole fleet of aircraft shuts down and crashes.
The fact that concerns about backward compatiblity didn't result in escalate being the new default is further proof of the saying "Watcom does things the way they should be done!"



Friday, March 1, 2013

SQL Anywhere 12.0.1 Sneak Peek: Compare Databases

Yes, yes, SQL Anywhere 12.0.1 is two years old so this is hardly a "Sneak Peak".

But, this feature is new to me so maybe you missed it too:


Support added for comparing database schemas and making them the same

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:
ALTER TABLE "DBA"."rroad_group_1_property_pivot" ADD "CPU_count" integer NOT NULL COMPUTE (case when COALESCE(NumLogicalProcessorsUsed,0) > 0 then NumLogicalProcessorsUsed
when COALESCE(NumProcessorsAvail,0) > 0 then NumProcessorsAvail
else 1
end)
go

ALTER TABLE "DBA"."rroad_group_1_property_pivot" ADD "autodropped_connection_count" integer NOT NULL DEFAULT 0
go

CREATE INDEX "ix_number_DESC_id_lost" ON "DBA"."rroad_group_1_property_pivot"
    ( "sample_set_number" DESC,"sampling_id","sample_lost" )
go

CREATE INDEX "ix_id_lost" ON "DBA"."rroad_group_1_property_pivot"
    ( "sampling_id","sample_lost" )
go
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:
ALTER TABLE "DBA"."rroad_odbc_dsn" DROP PRIMARY KEY
go

ALTER TABLE "DBA"."rroad_odbc_dsn" ADD PRIMARY KEY ("odbc_dsn" ASC,"session_id" ASC)
go

// Can't reorder columns for table "DBA"."rroad_odbc_dsn"
// ("ALTER TABLE ... ADD column-name ... " adds columns to end of table only)
// Database 'f - DBA' (database 1):
// CREATE TABLE "DBA"."rroad_odbc_dsn" (
//     "session_id"                     varchar(36) NOT NULL
//    ,"odbc_dsn"                       varchar(255) NOT NULL
//    ,PRIMARY KEY ("session_id" ASC,"odbc_dsn" ASC) 
// )
// Database 'f - DBA' (database 2):
// CREATE TABLE "DBA"."rroad_odbc_dsn" (
//     "odbc_dsn"                       varchar(255) NOT NULL
//    ,"session_id"                     varchar(36) NOT NULL
//    ,PRIMARY KEY ("odbc_dsn" ASC,"session_id" ASC) 
// )
Other limitations are described here, including some that might involve loss of data.


In SQL Anywhere 16, Compare Databases is pretty much the same except...
  • it's now called "Compare Database Schemas..." to reduce expectations about the data,

  • the password is no longer blanked out (grrr!) every time you open the Connect window, and

  • it now insists you start both databases using SQL Anywhere 16 rather than version 12.
As features go, it's a good start. At the very least, it will be immediately useful for generating those funky ALTER statements needed to deploy changes that were initially coded by DROP and CREATE.

And for double-checking "what's different" between old and new versions of a database... especially when you need to see all the nooks and crannies in the schema.

For daily use, however, not so much... not until it gets some more ease-of-use enhancements, like the ability to search and select in the SQL Script pane, and to change the order of the scripts.

Yes, the generated order of all those ALTER statements is important if you're going to run the whole thing at once, but...

...that's an unlikely use for it.