Monday, December 24, 2012

Latest SQL Anywhere EBF: 11.0.1.2913 for Linux, SPARC and Windows

Current builds for the active platforms...

HP-UX Itanium   12.0.1.3798 EBF        29 Oct 2012
                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 x86/x64   16.0.0.1018 Beta       09 Nov 2012
                12.0.1.3827 EBF        10 Dec 2012
                11.0.1.2913 EBF    *** 19 Dec 2012 ***

Mac OS          12.0.1.3819 EBF        10 Dec 2012
                11.0.1.2449 EBF        29 Jun 2010

Solaris SPARC   12.0.1.3798 EBF        24 Oct 2012
                11.0.1.2913 EBF    *** 19 Dec 2012 ***

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

Windows x86/x64 16.0.0.1018 Beta       09 Nov 2012
                12.0.1.3817 EBF        10 Dec 2012
                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...

 SQL Anywhere 16 Beta

 Older EBFs

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

 SQL Anywhere home page 

 Buy SQL Anywhere 

 Download the Developer Edition... 
   [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 Recommended...
   ODBC Drivers for MobiLink

The three asterisks "***" show which Express Bug Fixes (EBFs) and Beta builds have appeared on the website since the previous version of this page.
  • Only EBFs for the latest fully-supported versions of SQL Anywhere (11.0.1 and 12.0.1) are shown here, plus Beta builds for 16.0.0.

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


Friday, December 21, 2012

Breaking The Golden Rule: DEFAULT_TIMESTAMP_INCREMENT

The Golden Rule Of SQL Anywhere states "Watcom Does Things The Way They Should Be Done."

That rule has been so consistent over the years that any deviation comes as a shock, and therefore should be well publicized... like when the default page size was 2K instead of 4K once upon a time.

Here's another: Did you know that the DEFAULT_TIMESTAMP_INCREMENT database option does not affect columns defined with DEFAULT CURRENT TIMESTAMP, but only DEFAULT TIMESTAMP?

Why not?

Are DEFAULT CURRENT TIMESTAMP primary keys not worthy of uniqueness?

Does it make any sense to define a primary key with DEFAULT TIMESTAMP which implies the column is subject to update?

No, no sense at all... the DEFAULT_TIMESTAMP_INCREMENT option guarantees that default values assigned to a TIMESTAMP column will have ascending (and thus unique) values no matter how fast the INSERT statements are executed. Setting DEFAULT_TIMESTAMP_INCREMENT to '1' means each successive default value will be at least 0.000001 seconds later than the previous default value.

Great idea, right? Who can argue with that? If someone wants a repeating value they can bloody well provide it in the INSERT VALUES list, but I want my timestamp defaults to increment!

ALL my timestamp defaults, including DEFAULT CURRENT TIMESTAMP.

I thought it was a bug!

No, it's not a bug... it's even documented as a feature... and there was probably a "good reason" for not having DEFAULT_TIMESTAMP_INCREMENT work for DEFAULT CURRENT TIMESTAMP. Just like there once was a "good reason" for the 2K default page size... and a "good reason" for once not having row-level locking in Sybase SQL Server.

Need proof?

Here's proof that DEFAULT_TIMESTAMP_INCREMENT has no effect on DEFAULT CURRENT TIMESTAMP columns:
SET OPTION PUBLIC.DEFAULT_TIMESTAMP_INCREMENT = '1';
SET OPTION PUBLIC.TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';

CREATE TABLE t (
   a    TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
   b    INTEGER   NOT NULL );

BEGIN
INSERT t ( b ) VALUES ( 1 );
INSERT t ( b ) VALUES ( 2 );
INSERT t ( b ) VALUES ( 3 );
INSERT t ( b ) VALUES ( 4 );
INSERT t ( b ) VALUES ( 5 );
INSERT t ( b ) VALUES ( 6 );
INSERT t ( b ) VALUES ( 7 );
INSERT t ( b ) VALUES ( 8 );
INSERT t ( b ) VALUES ( 9 );
INSERT t ( b ) VALUES ( 10 );
COMMIT;
END;

SELECT * FROM t ORDER BY b;

a,b
'2012-12-20 09:41:58.717000',1
'2012-12-20 09:41:58.718000',2
'2012-12-20 09:41:58.718000',3
'2012-12-20 09:41:58.718000',4
'2012-12-20 09:41:58.718000',5
'2012-12-20 09:41:58.718000',6
'2012-12-20 09:41:58.718000',7
'2012-12-20 09:41:58.718000',8
'2012-12-20 09:41:58.719000',9
'2012-12-20 09:41:58.719000',10

Here's what happens if you try to make column "a" the PRIMARY KEY:
CREATE TABLE t (
   a    TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP PRIMARY KEY,
   b    INTEGER   NOT NULL );

BEGIN
INSERT t ( b ) VALUES ( 1 );
INSERT t ( b ) VALUES ( 2 );
INSERT t ( b ) VALUES ( 3 );
INSERT t ( b ) VALUES ( 4 );
INSERT t ( b ) VALUES ( 5 );
INSERT t ( b ) VALUES ( 6 );
INSERT t ( b ) VALUES ( 7 );
INSERT t ( b ) VALUES ( 8 );
INSERT t ( b ) VALUES ( 9 );
INSERT t ( b ) VALUES ( 10 );
COMMIT;
END;

Could not execute statement.
Primary key for table 't' is not unique: Primary key value ('2012-12-20 09:42:31.822000')
SQLCODE=-193, ODBC 3 State="23000"

So what?

By simply changing column "a" from DEFAULT CURRENT TIMESTAMP to DEFAULT TIMESTAMP you can make it work as the PRIMARY KEY because the DEFAULT_TIMESTAMP_INCREMENT option will now force all the values to be unique:
CREATE TABLE t (
   a    TIMESTAMP NOT NULL DEFAULT TIMESTAMP PRIMARY KEY,
   b    INTEGER   NOT NULL );

BEGIN
INSERT t ( b ) VALUES ( 1 );
INSERT t ( b ) VALUES ( 2 );
INSERT t ( b ) VALUES ( 3 );
INSERT t ( b ) VALUES ( 4 );
INSERT t ( b ) VALUES ( 5 );
INSERT t ( b ) VALUES ( 6 );
INSERT t ( b ) VALUES ( 7 );
INSERT t ( b ) VALUES ( 8 );
INSERT t ( b ) VALUES ( 9 );
INSERT t ( b ) VALUES ( 10 );
COMMIT;
END;

SELECT * FROM t ORDER BY b;

a,b
'2012-12-20 09:43:25.025000',1
'2012-12-20 09:43:25.038000',2
'2012-12-20 09:43:25.038001',3
'2012-12-20 09:43:25.038002',4
'2012-12-20 09:43:25.038003',5
'2012-12-20 09:43:25.038004',6
'2012-12-20 09:43:25.038005',7
'2012-12-20 09:43:25.038006',8
'2012-12-20 09:43:25.038007',9
'2012-12-20 09:43:25.039000',10

But wait!

DEFAULT TIMESTAMP columns exhibit a behavior you do not want in a primary key, at least you hardly ever want: When you UPDATE a row and don't explicitly SET a new value for the DEFAULT TIMESTAMP column, SQL Anywhere will set it for you.

DEFAULT TIMESTAMP is a "Magic Default" value: it works for UPDATE as well as INSERT. For all those "last_updated_datetime" columns, DEFAULT TIMESTAMP is a wonderful thing, you don't have SET it to CURRENT TIMESTAMP when you do an update, and you don't have to write an update trigger to keep it up to date, both of which are easy to forget.

But for a primary key? DEFAULT TIMESTAMP is a bad idea because changing a primary key value is (usually) a bad idea, especially behind-the-scenes (think MobiLink).
CREATE TABLE t (
   a    TIMESTAMP NOT NULL DEFAULT TIMESTAMP PRIMARY KEY,
   b    INTEGER   NOT NULL );

INSERT t ( b ) VALUES ( 1 );

SELECT 'before', * FROM t WHERE b = 1;

WAITFOR DELAY '00:00:10';

UPDATE t SET b = 1 WHERE b = 1;

SELECT 'after', * FROM t WHERE b = 1;

'before',a,b
'before','2012-12-20 09:43:48.269000',1

'after',a,b
'after','2012-12-20 09:43:58.435000',1

Product Suggestion: DEFAULT_CURRENT_TIMESTAMP_INCREMENT

This would work on DEFAULT CURRENT TIMESTAMP values the same way DEFAULT_TIMESTAMP_INCREMENT works on DEFAULT TIMESTAMP columns.

To preserve current behavior, the default setting for DEFAULT_CURRENT_TIMESTAMP_INCREMENT would be '0' instead of '1', where '0' means "do not increment".

Then, folks who wanted unique values assigned to TIMESTAMP primary key values on INSERT, but not changed on UPDATE, could use DEFAULT CURRENT TIMESTAMP plus this:
   SET OPTION PUBLIC.DEFAULT_CURRENT_TIMESTAMP_INCREMENT = '1';
A side effect of this would be when DEFAULT_CURRENT_TIMESTAMP_INCREMENT is set to something other than '0', all DEFAULT CURRENT TIMESTAMP columns would be included in the calculation of the @@DBTS global variable which returns the last unique generated timestamp values.


Wednesday, December 19, 2012

Calling GetOpenFileName() From SQL

Question: How do I call the Windows API function GetOpenFileName() to display an Open File dialog box from inside a SQL Anywhere stored procedure?

Nanny Answer: You can't! You mustn't! You shouldn't!

Libertine Answer: OK, calling GetOpenFileName from inside a database running on a multi-user network server is a Really Bad Idea... if it even works, the dialog box will pop up on the computer that's running the database, which is different from the computer running the client application, so the user never sees it.

But... if you're developing a standalone application using a local database, it might be perfectly OK to give the user full access to everything on that one single computer: application, database, files, features, everything.

Full Answer

Here's what the SQL code looks like...
CALL get_open_file_name ( @browse_file_spec );
... and here's the output (yes, you can display the Windows Open dialog box from a web page using SQL Anywhere, as long as the browser and the database are running on the same computer):


Here's an overview of the steps involved:

"Show me the code!"

Step 1: Create a folder for the C project: C:\projects\C

Step 2: Create the C source code file: get_open_file_name.cpp
#include <windows.h>
#include <Commdlg.h>
#include "extfnapi.h"

extern "C" __declspec ( dllexport ) a_sql_uint32 extfn_use_new_api ( void ) 
{
   return( EXTFN_API_VERSION );
}

__declspec ( dllexport ) void FAR __stdcall get_open_file_name ( an_extfn_api *api, void *arg_handle ) 
{
   an_extfn_value  api_file_spec;

   char *          file_spec; 
   OPENFILENAME    ofn;               // common dialog box structure
   char            szFile [ 260 ];    // buffer for file name
   BOOL            ok;

   file_spec  = ( char * ) malloc ( 260 );
   strcpy_s ( file_spec, 260, "" );

   ZeroMemory ( &ofn, sizeof ( ofn ) );

   ofn.lStructSize     = sizeof ( ofn );
   ofn.hwndOwner       = NULL;
   ofn.lpstrFile       = szFile;

   // Set lpstrFile [ 0 ] to '\0' so that GetOpenFileName does not 
   // use the contents of szFile to initialize itself.

   ofn.lpstrFile [ 0 ] = '\0';

   ofn.nMaxFile        = sizeof ( szFile );
   ofn.lpstrFilter     = "All\0*.*\0Text\0*.TXT\0";
   ofn.nFilterIndex    = 1;
   ofn.lpstrFileTitle  = NULL;
   ofn.nMaxFileTitle   = 0;
   ofn.lpstrInitialDir = NULL;
   ofn.Flags           = OFN_PATHMUSTEXIST | OFN_FILEMUSTEXIST;

   ok = GetOpenFileName ( &ofn );

   if( ok == TRUE ) {
      strcpy_s ( file_spec, 260, ofn.lpstrFile );
   }

   api_file_spec.type      = DT_VARCHAR;
   api_file_spec.data      = file_spec;
   api_file_spec.piece_len = ( a_sql_uint32 )( strlen ( file_spec ) );
   api_file_spec.len.total_len = ( a_sql_uint32 )( strlen ( file_spec ) );
   api -> set_value ( arg_handle, 1, &api_file_spec, 0 );

   free ( file_spec );

}
  • The includes on lines 1 and 2 refer to standard Windows headers files that are required when calling GetOpenFileName, as documented here.

  • The include on line 3 and the function on lines 5 through 8 are required when using the SQL Anywhere external call interface.

  • Everything about the function header on line 10 is a standard requirement for any C function to be called from SQL Anywhere... everything, that is, except the function name "get_open_file_name". In particular, the parameter list is always "( an_extfn_api *api, void *arg_handle )" which bears no obvious relationship to the actual function parameters.

  • One line of code like line 12 is required for every argument that appears in the CALL from SQL. The type is always "an_extfn_value", and by convention the name is "api_...".

  • The local variables declared on lines 14 through 17 are used in the body of the function, and the code on lines 19 and 20 allocates memory and initializes the file specification that is to be filled in by the call to GetOpenFileName.

  • The code on lines 22 through 41 was copied with very little change from an example in Using Common Dialog Boxes.

  • If the call to GetOpenFileName was successful, the code on lines 43 through 45 copies the return value into the local variable file_spec.

  • The code on lines 47 through 51 is standard fare for returning a local string to the SQL CALL via an OUT parameter.

  • The code on line 53 releases the memory allocated on line 19.

  • You can find more examples of external C functions by searching this blog on "EXTERNAL NAME".
Step 3: Create a "module definition file": get_open_file_name.def
EXPORTS extfn_use_new_api
EXPORTS get_open_file_name
Step 4: Create a "DLL Project" in Visual Studio.
All Programs 
    Microsoft Visual Studio 2008
      Microsoft Visual Studio 2008
        File - New - Project From Existing Code...
          What type of project? Visual C++
          Next
          Project file location: C:\projects\C
          Project name: get_open_file_name
          Next
          check: Use Visual Studio
          Project type: Dynamically linked library (DLL) project
          Finish


Step 5: Change "Debug" to "Release" on the toolbar.


Step 6: Specify the location of SQL Anywhere *.h include files...
Solution Explorer  
     select get_open_file_name 
       right mouse - Properties  
         Configuration Properties 
           C/C++
             General
               Additional Include Directories: ...

   For SQL Anywhere 10: "C:\Program Files\SQL Anywhere 10\h"
   For SQL Anywhere 11: "C:\Program Files\SQL Anywhere 11\SDK\Include"
   For SQL Anywhere 12: "C:\Program Files\SQL Anywhere 12\SDK\Include"




Step 7: Specify the location of get_open_file_name.def file...
Solution Explorer  
     select get_open_file_name 
       right mouse - Properties  
         Configuration Properties 
           Linker
             Input
               Module Definition File: get_open_file_name.def


Step 8: Build the dll...
Shut down any SQL Anywhere engine used for unit testing.
  Build 
    Build Solution
      - or -
    Rebuild Solution


Step 9: Run a simple test in ISQL...
CREATE PROCEDURE get_open_file_name ( 
   OUT file_spec VARCHAR ( 260 ) )
   EXTERNAL NAME 'get_open_file_name@C:\\projects\\C\\Release\\get_open_file_name';

BEGIN

DECLARE @file_spec  VARCHAR ( 260 );

CALL get_open_file_name ( @file_spec );

MESSAGE STRING ( '***** get_open_file_name: "', @file_spec, '"' ) TO CLIENT; 

END;




Step 10: Create the web service plus the supporting objects (table, procedures)...
CREATE SERVICE demo
   TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL demo ( :action, :file_spec );

CREATE TABLE persistent_storage (
   file_spec VARCHAR ( 260 ) NOT NULL );

INSERT persistent_storage VALUES ( '[Provide a file specification]' );
COMMIT;

CREATE PROCEDURE get_open_file_name ( 
   OUT file_spec VARCHAR ( 260 ) )
   EXTERNAL NAME 'get_open_file_name@C:\\projects\\C\\Release\\get_open_file_name';

CREATE PROCEDURE demo (
   IN @input_action     LONG VARCHAR,
   IN @input_file_spec  LONG VARCHAR )
   RESULT ( html_string LONG VARCHAR )
BEGIN

DECLARE @browse_file_spec  VARCHAR ( 260 );
DECLARE @file_spec         VARCHAR ( 260 );

SELECT file_spec
  INTO @file_spec
  FROM persistent_storage;

CASE 

   WHEN @input_action = 'browse' THEN
      CALL get_open_file_name ( @browse_file_spec );
      MESSAGE STRING ( '@browse_file_spec = "', @browse_file_spec, '"' ) TO CONSOLE;
      IF TRIM ( COALESCE ( @browse_file_spec, '' ) ) <> '' THEN
         SET @file_spec = TRIM ( @browse_file_spec );
      END IF;

   WHEN @input_action = 'save' THEN
      SET @file_spec = TRIM ( COALESCE ( @input_file_spec, '' ) );
      IF @file_spec = '' THEN
         SET @file_spec = '[Provide a file specification]';
      END IF;
      UPDATE persistent_storage
         SET file_spec = @file_spec;
      COMMIT;

   WHEN @input_action = 'refresh' THEN
      -- no further action

   ELSE -- no further action

END CASE;

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

SELECT STRING ( 
   '',
   '',
   ' ',
   '',
   '',

   '
', '', '

', '', '

', '', '

', '', '

', '', '' ); END;
  • The CREATE SERVICE on lines 1 through 3 defines a web service that accepts two parameters in the URL and is implemented as a CALL to a stored procedure.

  • The code on lines 5 through 9 creates and initializes some local persistant storage for the web service.

  • The CREATE PROCEDURE on lines 11 through 13 maps a SQL stored procedure to the C function in the DLL.

  • The procedure on lines 15 through 96 implements a simple web page with one input text field and three buttons, one of which calls get_open_file_name().

  • You can find more examples of web services by searching this blog on "CREATE SERVICE".
Step 11: Launch the 32-bit version of SQL Anywhere with the HTTP web server turned on...
"%SQLANY12%\bin32\dbspawn.exe"^
  -f^
  "%SQLANY12%\bin32\dbeng12.exe"^
  -xs http(port=80)^
  ddd12.db 
Step 12: Launch the web service in your favorite browser...
http://localhost/demo



Monday, December 17, 2012

Latest SQL Anywhere EBF: 12.0.1.3819 for Mac OS

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

  • Only EBFs for the latest fully-supported versions of SQL Anywhere (11.0.1 and 12.0.1) are shown here, plus Beta builds for 16.0.0.

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

Current builds for the active platforms...

HP-UX Itanium   12.0.1.3798 EBF        29 Oct 2012
                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 x86/x64   16.0.0.1018 Beta       09 Nov 2012
                12.0.1.3827 EBF        10 Dec 2012
                11.0.1.2879 EBF        31 Oct 2012 

Mac OS          12.0.1.3819 EBF    *** 10 Dec 2012 ***
                11.0.1.2449 EBF        29 Jun 2010

Solaris SPARC   12.0.1.3798 EBF        24 Oct 2012
                11.0.1.2879 EBF        24 Oct 2012

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

Windows x86/x64 16.0.0.1018 Beta       09 Nov 2012
                12.0.1.3817 EBF        10 Dec 2012
                12.0.1 French Docs,    25 Sep 2012
                       English Docs,   25 Sep 2012
                       German Docs     25 Sep 2012
                11.0.1.2878 EBF        23 Nov 2012

Other Stuff...

SQL Anywhere 16 Beta
  
Older EBFs
  
Free support! Q&A forum
...or, call Tech Support
  
SQL Anywhere home page 

Buy SQL Anywhere 

Download the Developer Edition... 
  [12.0.1] [11.0.1]

Download the...
   Educational Edition 
   Web Edition 

Supported Platforms...
  SQL Anywhere 
  Linux 
  OnDemand

Recommended...
  ODBC Drivers for MobiLink


Friday, December 14, 2012

Suspending RI, At Least For A Little While

Question: How do I temporarily disable referential integrity (RI) checking in SQL Anywhere so I can insert rows out of parent-child order? SQL Server lets me use ALTER TABLE NOCHECK CONSTRAINT.

Answer: The easiest, safest and most efficient technique is to use the WAIT_FOR_COMMIT option to defer all foreign key checking until the next COMMIT. This technique is available in SQL Anywhere but not SQL Server.

The next best technique (fairly safe, not so easy, and really inefficient for large tables) is to ALTER TABLE DROP CONSTRAINT on the foreign key constraints that are getting in your way, and ALTER TABLE ADD CONSTRAINT when all the data has been inserted. This technique is available in both SQL Anywhere and SQL Server.

The least-safe technique uses ALTER TABLE NOCHECK CONSTRAINT to disable (not defer) foreign key checking until the work is done, then use ALTER TABLE CHECK CONSTRAINT to enable checking. This technique is available in SQL Server, but it is unlikely ever to be supported in SQL Anywhere (more on this later).

Compare And Contrast, By Example

Here's what a referential integrity violation looks like in SQL Anywhere, when a child row is inserted before the parent row exists:
CREATE TABLE parent (
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT parent_pkey PRIMARY KEY ( parent_key ) );

CREATE TABLE child (
   child_key    INTEGER NOT NULL,
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY ( child_key ),
   CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key ) );

INSERT child VALUES ( 99, 1, 'child' );
-----
Could not execute statement.
No primary key value for foreign key 'fkey' in table 'child'
SQLCODE=-194, ODBC 3 State="23000"
Line 29, column 1
INSERT child VALUES ( 99, 1, 'child' )
Here's what the problem looks like in SQL Server... same referential integrity violation, different message:
CREATE TABLE parent (
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT parent_pkey PRIMARY KEY ( parent_key ) )
GO

CREATE TABLE child (
   child_key    INTEGER NOT NULL,
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY ( child_key ),
   CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key ) )
GO

INSERT child VALUES ( 99, 1, 'child' )
GO

-----

Msg 547, Level 16, State 1, Server ENVY, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "fkey". The
conflict occurred in database "test", table "dbo.parent", column 'parent_key'.
The statement has been terminated.
Here's how setting the WAIT_FOR_COMMIT option at the connection level solves the problem in SQL Anywhere; referential integrity checking is deferred until the COMMIT is executed, and by that time a matching row in the parent table has been inserted:
SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';
INSERT child VALUES ( 99, 1, 'child' );
INSERT parent VALUES ( 1, 'parent' );
COMMIT;

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'OFF';

SELECT * FROM parent;
SELECT * FROM child;

-----

parent_key,data
1,'parent'

child_key,parent_key,data
99,1,'child'
Here's what happens in SQL Anywhere when a referential integrity violation still exists when the COMMIT is issued; in other words, the data's bad and no amount of waiting is going to change that fact:
SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';
INSERT child VALUES ( 99, 2, 'child' );
INSERT parent VALUES ( 1, 'parent' );
COMMIT;

-----

Could not execute statement.
No primary key value for foreign key 'fkey' in table 'child'
SQLCODE=-194, ODBC 3 State="23000"
Line 32, column 1
COMMIT
Here's how ALTER TABLE DROP and ADD CONSTRAINT statements can be used to temporarily disable foreign key checking in SQL Anywhere, to allow a child row to be inserted before the corresponding parent:
CREATE TABLE parent (
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT parent_pkey PRIMARY KEY ( parent_key ) );

CREATE TABLE child (
   child_key    INTEGER NOT NULL,
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY ( child_key ),
   CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key ) );

ALTER TABLE child DROP CONSTRAINT fkey;

INSERT child VALUES ( 99, 1, 'child' );
INSERT parent VALUES ( 1, 'parent' );
COMMIT;

ALTER TABLE child ADD CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key );
The same ALTER TABLE DROP and ADD CONSTRAINT technique works in SQL Server:
CREATE TABLE parent (
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT parent_pkey PRIMARY KEY ( parent_key ) )
GO

CREATE TABLE child (
   child_key    INTEGER NOT NULL,
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY ( child_key ),
   CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key ) )
GO

ALTER TABLE child DROP CONSTRAINT fkey
GO

INSERT child VALUES ( 99, 1, 'child' )
GO
INSERT parent VALUES ( 1, 'parent' )
GO

ALTER TABLE child ADD CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key )
GO
SQL Anywhere checks all the rows when the ALTER TABLE ADD CONSTRAINT is executed. This doesn't prevent bad data from being inserted

... and committed

... and saved forever,

but it does raise a red flag by preventing the referential integrity constraint from being redefined... that's what "fairly safe" meant in the introduction:
ALTER TABLE child DROP CONSTRAINT fkey;

INSERT child VALUES ( 99, 2, 'child' );
INSERT parent VALUES ( 1, 'parent' );
COMMIT;

ALTER TABLE child ADD CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key );

-----

Could not execute statement.
No primary key value for foreign key 'fkey' in table 'child'
SQLCODE=-194, ODBC 3 State="23000"
Line 35, column 1
ALTER TABLE child ADD CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key )
The fact that ALTER TABLE ADD CONSTRAINT checks all the existing rows explains the earlier comment about ALTER TABLE ADD CONSTRAINT being "really inefficient for large tables": if you use this technique to insert a single row in a million-row table, the ALTER TABLE ADD CONSTRAINT will check all million rows, something the WAIT_FOR_COMMIT technique avoids.

The same behavior is true for SQL Server; the ALTER TABLE ADD CONSTRAINT doesn't allow the constraint to be redefined if any existing data fails the test... fairly safe, and possibly inefficient:
ALTER TABLE child DROP CONSTRAINT fkey
GO

INSERT child VALUES ( 99, 2, 'child' )
GO
INSERT parent VALUES ( 1, 'parent' )
GO

ALTER TABLE child ADD CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key )
GO

-----

Msg 547, Level 16, State 1, Server ENVY, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fkey".
The conflict occurred in database "test", table "dbo.parent", column
'parent_key'.
And that brings us to SQL Server's ALTER TABLE CHECK and NOCHECK CONSTRAINT technique; yes, it allows valid data to be inserted out of order:
CREATE TABLE parent (
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT parent_pkey PRIMARY KEY ( parent_key ) )
GO

CREATE TABLE child (
   child_key    INTEGER NOT NULL,
   parent_key   INTEGER NOT NULL,
   data         VARCHAR ( 10 ) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY ( child_key ),
   CONSTRAINT fkey FOREIGN KEY ( parent_key ) REFERENCES parent ( parent_key ) )
GO

ALTER TABLE child NOCHECK CONSTRAINT fkey
GO

INSERT child VALUES ( 99, 1, 'child' )
GO
INSERT parent VALUES ( 1, 'parent' )
GO

ALTER TABLE child CHECK CONSTRAINT fkey
GO

SELECT * FROM parent
GO
SELECT * FROM child
GO

-----

1> SELECT * FROM parent
2> GO
 parent_key  data
 ----------- ----------
           1 parent

(1 row affected)
1> SELECT * FROM child
2> GO
 child_key   parent_key  data
 ----------- ----------- ----------
          99           1 child

(1 row affected)

Sadly, it also allows bad data to be inserted, on a permanent basis, with no indication there's a problem; the ALTER TABLE CHECK CONSTRAINT just turns checking back on, it does not check existing rows for validity:
ALTER TABLE child NOCHECK CONSTRAINT fkey
GO

INSERT child VALUES ( 99, 2, 'child' )
GO
INSERT parent VALUES ( 1, 'parent' )
GO

ALTER TABLE child CHECK CONSTRAINT fkey
GO

SELECT * FROM parent
GO
SELECT * FROM child
GO

-----

1> SELECT * FROM parent
2> GO
 parent_key  data
 ----------- ----------
           1 parent

(1 row affected)
1> SELECT * FROM child
2> GO
 child_key   parent_key  data
 ----------- ----------- ----------
          99           2 child
That's why ALTER TABLE NOCHECK CONSTRAINT is unlikely to be introduced in SQL Anywhere: it's . . . just . . . not . . . safe.

When folks asked to temporarily disable referential integrity checking so they could insert rows in any order they wanted, they got WAIT_FOR_COMMIT and they were happy.

Since then, nobody's been asking for the ability to insert bad data... that happens often enough without new features to encourage bad behavior.


For another discussion of ALTER TABLE NOCHECK CONSTRAINT see Can you trust your constraints?

Wednesday, December 12, 2012

Latest SQL Anywhere EBFs: 12.0.1.3827 Linux, 12.0.1.3817 Windows

( Revised to add 12.0.1.3827 for Linux )

The three asterisks "***" show which Express Bug Fixes (EBFs) and Beta builds have been released since the previous version of this page.

  • Only EBFs for the latest fully-supported versions of SQL Anywhere (11.0.1 and 12.0.1) are shown here, plus Beta builds for 16.0.0.

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

Current builds for the active platforms...

HP-UX Itanium   12.0.1.3798 EBF        29 Oct 2012
                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 x86/x64   16.0.0.1018 Beta       09 Nov 2012
                12.0.1.3827 EBF    *** 10 Dec 2012 ***
                11.0.1.2879 EBF        31 Oct 2012 

Mac OS          12.0.1.3798 EBF        15 Oct 2012
                11.0.1.2449 EBF        29 Jun 2010

Solaris SPARC   12.0.1.3798 EBF        24 Oct 2012
                11.0.1.2879 EBF        24 Oct 2012

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

Windows x86/x64 16.0.0.1018 Beta       09 Nov 2012
                12.0.1.3817 EBF    *** 10 Dec 2012 ***
                12.0.1 French Docs,    25 Sep 2012
                       English Docs,   25 Sep 2012
                       German Docs     25 Sep 2012
                11.0.1.2878 EBF        23 Nov 2012

Other Stuff...

SQL Anywhere 16 Beta
  
Older EBFs
  
Free support! Q&A forum
...or, call Tech Support
  
SQL Anywhere home page 

Buy SQL Anywhere 

Download the Developer Edition... 
  [12.0.1] [11.0.1]

Download the Educational Edition 
Download the Web Edition 

Supported Platforms...
  [SQL Anywhere] [Linux] [OnDemand]

Recommended...
  ODBC Drivers for MobiLink


Monday, December 10, 2012

The "SAP Sybase SQL Anywhere" Community

Save this link! ...

... As far as I can tell, there's no way to reach this page if you don't already know where it is: The SAP Sybase SQL Anywhere Community page.

This (might be) where stuff is moving, and where new stuff (might be) appearing in the near future...


Wednesday, December 5, 2012

Intra-Procedure Parallelism

Question: How can I make a SQL Anywhere stored procedure run faster?

Answer: One way is to make use of the excess CPU capacity found on many computers by spreading the work across two or more CPUs... not by relying on intra-query parallelism which works on individual SQL statements, but by moving blocks of code out of a single stored procedure into separate events so they run in parallel on separate internal connections.


The title calls this technique "Intra-Procedure Parallelism" but it's really an example of "inter-query parallelism" which is descibed in the Help as "executing different requests simultaneously on separate CPUs"... in this case, the original code that executed as a single request (one procedure call) is changed into separate requests (event invocations) that execute simultaneously. Inter-query parallelism has been around forever in SQL Anywhere, and this article shows how it can be exploited to improve the performance of a single user connection.
Here's an example, a procedure called "serial" that performs two time-consuming steps one after the other:
CREATE TABLE t (
   result_number   INTEGER NOT NULL PRIMARY KEY,
   result          BIGINT NOT NULL );

CREATE PROCEDURE serial()
BEGIN

DECLARE @start        TIMESTAMP;
DECLARE @start_step_1 TIMESTAMP;
DECLARE @start_step_2 TIMESTAMP;

-- Step 1.

SET @start        = CURRENT TIMESTAMP;
SET @start_step_1 = CURRENT TIMESTAMP;

INSERT t
ON EXISTING UPDATE
SELECT 1,
       SUM ( CAST ( a.row_num AS BIGINT ) ) 
  FROM RowGenerator AS a 
       CROSS JOIN RowGenerator AS b 
       CROSS JOIN RowGenerator AS c;
COMMIT;

MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start_step_1, CURRENT TIMESTAMP ) 
                        AS DECIMAL ( 11, 2 ) ) / 1000.0,
                 ' seconds to perform step 1' ) TO CONSOLE;

-- Step 2.

SET @start_step_2 = CURRENT TIMESTAMP;

INSERT t
ON EXISTING UPDATE
SELECT 2,
       SUM ( CAST ( a.row_num AS BIGINT ) )
  FROM RowGenerator AS a 
       CROSS JOIN RowGenerator AS b 
       CROSS JOIN RowGenerator AS c
       CROSS JOIN RowGenerator AS d
 WHERE d.row_num <= 2;
COMMIT;

MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start_step_2, CURRENT TIMESTAMP ) 
                        AS DECIMAL ( 11, 2 ) ) / 1000.0,
                 ' seconds to perform step 2' ) TO CONSOLE;

MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start, CURRENT TIMESTAMP ) 
                        AS DECIMAL ( 11, 2 ) ) / 1000.0,
                 ' seconds to perform both steps' ) TO CONSOLE;

END;

CALL serial();

10.1680000 seconds to perform step 1
19.9700000 seconds to perform step 2
30.1440000 seconds to perform both steps
Both steps ran on the same CPU, ignoring all other processors that might be sitting idle, and together they took a total of 30 seconds to run.

Here's the alternative, a procedure called "parallel" that uses the TRIGGER EVENT statement to launch two events, "subtask1" and "subtasks2":

  • The new table "subtask_in_progress" on lines 1 through 5 keeps track of the event connections. The task_connection_number column holds the connection number of the calling procedure, the subtask_number serves to differentiate between different subtasks, and the connection number of each subtask event is stored in the subtask_connection_number column. Each subtask event (shown later) fills in the subtask_connection_number value when it starts. The caller can then determine when a subtask event has started by checking to see if subtask_connection_number has been filled in, and it can tell when the subtask has finished by checking to see that the actual connection no longer exists.

  • The code on lines 14 through 21 initializes subtask_in_progress with a "not yet started" row for each subtask; the CONNECTION_PROPERTY ( 'Number' ) function is used to fill in the task_connection_number, and subtask_connection_number is set to zero.

  • The TRIGGER EVENT statements on lines 25 and 26 take almost zero time to execute because they are "fire and forget" operations, not "call and return".

  • The code on lines 28 through 37 waits for all the subtask events to get underway, then the code on lines 39 through 57 waits until they are all complete.

  • It is important to note that the subtask events (shown later) fill in subtask_in_progress.subtask_connection_number values, but only the caller sets those values back to zero (lines 48 through 55) when they no longer match actual connections. Stuff happens; the subtask events can terminate abnormally, so they can't be relied upon to tell the caller when they're done.
CREATE TABLE subtask_in_progress (
   task_connection_number      INTEGER NOT NULL,
   subtask_number              INTEGER NOT NULL, -- 1, 2
   subtask_connection_number   INTEGER NOT NULL,
   PRIMARY KEY ( task_connection_number, subtask_number ) );

CREATE PROCEDURE parallel()
BEGIN

DECLARE @start TIMESTAMP;

SET @start = CURRENT TIMESTAMP;

-- Initialize subtasks as "not yet started".

DELETE subtask_in_progress
 WHERE task_connection_number = CONNECTION_PROPERTY ( 'Number' );

INSERT subtask_in_progress VALUES ( CONNECTION_PROPERTY ( 'Number' ), 1, 0 );
INSERT subtask_in_progress VALUES ( CONNECTION_PROPERTY ( 'Number' ), 2, 0 );
COMMIT;

-- Launch subtasks.

TRIGGER EVENT subtask1;
TRIGGER EVENT subtask2;

-- Wait for all the subtasks to start.

WHILE EXISTS ( SELECT * 
                 FROM subtask_in_progress
                WHERE task_connection_number = CONNECTION_PROPERTY ( 'Number' )
                  AND subtask_connection_number = 0 ) LOOP

   WAITFOR DELAY '00:00:00.5';

END LOOP;

-- Wait for all the subtasks to finish.

WHILE EXISTS ( SELECT * 
                 FROM subtask_in_progress
                WHERE task_connection_number = CONNECTION_PROPERTY ( 'Number' )
                  AND subtask_connection_number <> 0 ) LOOP

   WAITFOR DELAY '00:00:00.5';

   -- Update any of the subtasks that have finished.

   UPDATE subtask_in_progress
      SET subtask_connection_number = 0
    WHERE task_connection_number = CONNECTION_PROPERTY ( 'Number' )
      AND subtask_connection_number <> 0
      AND CONNECTION_PROPERTY ( 'Number', subtask_connection_number ) IS NULL;
   COMMIT;

END LOOP;

MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start, CURRENT TIMESTAMP ) 
                        AS DECIMAL ( 11, 2 ) ) / 1000.0,
                 ' seconds to perform both tasks' ) TO CONSOLE;

END;

CALL parallel();

11.7720000 seconds to perform subtask1
21.5570000 seconds to perform subtask2
21.8280000 seconds to perform both tasks
Each subtask event does take longer than corresponding step in the serial solution, but the whole process is a lot faster: 22 seconds instead of 30.

Here's the code for the two subtask events:

  • The CREATE EVENT statements on lines 1 and 33 show how to create "untyped" or "user-defined" events; i.e., events that have neither TYPE nor SCHEDULE clauses and can thus be fired only by TRIGGER EVENT statements.

  • Lines 8 through 14 and 40 through 46 show how the rows in subtask_in_progress are updated; EVENT_PARAMETER ( 'ConnectionID' ) provides the caller's connection number, and CONNECTION_PROPERTY ( 'Number' ) gives the event's connection number (developers from The Old School might prefer to use @@SPID).

  • Lines 16 through 25 is the code from step 1 in the serial solution, and 48 through 59 is the code from step 2.
CREATE EVENT subtask1
HANDLER BEGIN

DECLARE @start TIMESTAMP;

SET @start = CURRENT TIMESTAMP;

-- Mark subtask1 as "started".

UPDATE subtask_in_progress
   SET subtask_connection_number = CONNECTION_PROPERTY ( 'Number' )
 WHERE task_connection_number = EVENT_PARAMETER ( 'ConnectionID' )
   AND subtask_number         = 1;
COMMIT;

-- Do step 1.

INSERT t
ON EXISTING UPDATE
SELECT 1,
       SUM ( CAST ( a.row_num AS BIGINT ) ) 
  FROM RowGenerator AS a 
       CROSS JOIN RowGenerator AS b 
       CROSS JOIN RowGenerator AS c;
COMMIT;

MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start, CURRENT TIMESTAMP ) 
                        AS DECIMAL ( 11, 2 ) ) / 1000.0,
                 ' seconds to perform subtask1' ) TO CONSOLE;

END;

CREATE EVENT subtask2
HANDLER BEGIN

DECLARE @start TIMESTAMP;

SET @start = CURRENT TIMESTAMP;

-- Mark subtask2 as "started".

UPDATE subtask_in_progress
   SET subtask_connection_number = CONNECTION_PROPERTY ( 'Number' )
 WHERE task_connection_number = EVENT_PARAMETER ( 'ConnectionID' )
   AND subtask_number         = 2;
COMMIT;

-- Do step 2.

INSERT t
ON EXISTING UPDATE
SELECT 2,
       SUM ( CAST ( a.row_num AS BIGINT ) )
  FROM RowGenerator AS a 
       CROSS JOIN RowGenerator AS b 
       CROSS JOIN RowGenerator AS c
       CROSS JOIN RowGenerator AS d
 WHERE d.row_num <= 2;
COMMIT;

MESSAGE STRING ( CAST ( DATEDIFF ( MILLISECOND, @start, CURRENT TIMESTAMP ) 
                        AS DECIMAL ( 11, 2 ) ) / 1000.0,
                 ' seconds to perform subtask2' ) TO CONSOLE;

END;
The CPU usage for the serial and parallel solutions shows how using 2 processors instead of just one can reduce the overall elapsed time from 30 seconds to 22:
Serial CPU Usage

Parallel CPU Usage

Here's another look at the two tests using Foxhound, with the serial test appearing below (earlier in time) than the parallel test:
Serial and Parallel Tests

Here's what the connections look like during the serial and parallel tests:
Serial Connection

Parallel Connections

In this example it wasn't actually necessary to create two subtask events, one would have sufficed: the subtask1 event could have handled step 1 and the calling procedure could have performed step 2 and then checked to make sure subtask1 was finished.

As coded, there are three connections in use, two doing actual work (subtask1 and subtask1) and one leaning on a shovel... :)