Wednesday, February 27, 2013

Latest SQL Anywhere EBFs: 12.0.1 for Mac and 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.1018 Beta          09 Nov 2012
          12.0.1.3827 EBF (SA)      10 Dec 2012
          12.0.1.3798 EBF           07 Nov 2012
          11.0.1.2913 EBF (SA)      19 Dec 2012
          11.0.1.2879 EBF (All)     03 Jan 2013

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.1018 Beta          09 Nov 2012
          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...

 SQL Anywhere 16 Beta

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


Sunday, February 24, 2013

Taming A Runaway Temporary File

A temporary file, also known as a temporary dbspace, is automatically created whenever a SQL Anywhere database is started, and it is automatically deleted whenever that database is stopped. The files are named sqla0000.tmp, sqla0001.tmp and so on, and by default (on Windows 7) they are located in the C:\Users\[user-name]\AppData\Local\Temp folder:

 Directory of C:\Users\Breck\AppData\Local\Temp

02/23/2013  10:04 AM        26,353,664 sqla0000.tmp
02/23/2013  10:52 AM           200,704 sqla0001.tmp
02/23/2013  10:55 AM           102,400 sqla0003.tmp
The temporary file is used for temporary data. Exactly what constitutes "temporary data" is more or less a mystery, as are the conditions under which temporary data is written to the temporary file rather than stored in the database cache. You have control over the temporary file location, but the SQL Anywhere server has complete control over how, why and when the temporary file is used.

In terms of data nothing of lasting value is ever stored in these files. If for some reason (say, after a crash) one of these files remains in existence, it's just wasting space and you can go ahead and delete it. In case of doubt, delete: if it's still in use by SQL Anywhere you won't be able to.

In terms of performance, the temporary file is usually no problem. In fact, at most SQL Anywhere shops the temporary file is completely invisible: no one even knows it's there.

It is possible, however, for runaway temporary file usage to cause grief:
  • If SQL Anywhere decides to write the temporary data to the file instead of the database cache, excessive disk I/O may be the result.

  • If the temporary file is located on the same physical drive as other database files, the disk I/O may not be efficient.

  • Prior to SQL Anywhere 10, a runaway process could cause the server to crash because the drive holding the temporary file ran out of space. With version 10 and later, a runaway process is prevented from using more than 80% of the remaining disk space, which on today's enormous disk drives means...

  • ...if not a crash, then at least "Holy Cow!" moment when first discovering that sqla0000.tmp has grown to 500G for a database that is only 300M in size.
It's easy to tell SQL Anywhere where to put the temporary file: just use the dbsrv12 -dt option or one of the SATMP, TMP, TMPDIR or TEMP environment variables.

The next step, telling SQL Anywhere to stop runaway connections from using too much temporary space, is harder. Here's how...

Step 1: Put a limit on temporary space usage


Pick a limit, say 512M, on the amount of temporary storage used by each individual connection, and put that limit into effect:
SET OPTION PUBLIC.temp_space_limit_check = 'On';

SET OPTION PUBLIC.max_temp_space = '512M';
Double-check that these settings apply to end-user connections. One method is to have the end user connect via dbisql, run the SET command (no parameters, just "SET") and report the values displayed:





Step 2: Test the limit check


Here's command to start a connection named "adhoc-queries" that will be used to test the limit check:
"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=inventory_envy;DBN=inventory;UID=k.delacruz;PWD=sql;CON=adhoc-queries"
Here's a query that displays the size of the temporary file, plus the temporary space currently used by "adhoc-queries"; this query should be run on some OTHER connection:
SELECT STRING (
          CAST ( CAST ( DB_EXTENDED_PROPERTY ( 'FileSize', 'temporary' ) AS INTEGER )
             * 4096.0 / ( 1024 * 1024 )
             AS DECIMAL ( 11, 2 ) ),
          'M' ) 
          AS "Temporary FileSize",
       STRING ( 
          CAST ( CAST ( CONNECTION_PROPERTY ( 'TempfilePages', Number ) AS INTEGER )
             * 4096.0 / ( 1024 * 1024 )
              AS DECIMAL ( 11, 2 ) ),
          'M' ) 
          AS "TempFilePages"
  FROM sa_conn_properties() 
 WHERE PropName = 'Name' 
   AND Value = 'adhoc-queries'

Temporary FileSize,TempFilePages
'1.04M','.11M'
So far so good... but here's a query to run on the "adhoc-queries" connection that will cause the temporary space usage to soar:
SELECT a.*
  INTO #temp_inventory
  FROM inventory AS a,
       inventory AS b;
It's self-join between a million-row table and itself, and because the WHERE clause has been omitted (a common error with adhoc queries) it is effectively a CROSS JOIN.

In testing that query took less than a minute to reach the max_temp_space limit:
Temporary FileSize,TempFilePages
'41.04M','35.90M'

Temporary FileSize,TempFilePages
'462.97M','461.87M'
When the max_temp_space limit kicked in, SQL Anywhere stopped the SELECT INTO and started rolling it back. The connection-level TempFilePages value started to sink, but the temporary file size remained at the "high water mark":
Temporary FileSize,TempFilePages
'526.97M','302.60M'
Eventually, the failing connection received a SQLCODE -1000 error message, but not until the connection-level TempFilePages had sunk back to pre-SELECT INTO levels:
Could not execute statement.
Temporary space limit exceeded
SQLCODE=-1000, ODBC 3 State="HY000"

Temporary FileSize,TempFilePages
'526.97M','.23M'
Here's what the total temporary space usage looked like from Foxhound's point of view:


  • At first (bottom line), before the runaway SELECT INTO started executing, the total "Temp Space" amount was 760K.

  • In less than a minute (6th line up from the bottom), the amount reached 503M.
    At this point, the "adhoc-queries" connection (see the lower frame in the image below) was responsible for 502M of the total:

  • The next sample (7th line up from the bottom of the first image) shows the total amount has started to drop (463M). At this point SQL Anywhere has cancelled the SELECT INTO operation and started to roll it back.

  • Two minutes later (2nd line from the top), SQL Anywhere has finished cancelling the SELECT INTO.
    The total Temp Space is back down to 868K, and at 3:50:18 the SQLCODE -1000 error was returned to the "adhoc-queries" connection:



"Why bother setting a limit, just increase the database cache."


Here's why: In the test above, the dbsrv12 -c 1G -ch 2G options were used even though the entire database file was only 288M. The database was completely idle except for the runaway SELECT INTO test, yet SQL Anywhere chose to grow the temporary file to 527M instead of using (or growing) the cache.

Hence the earlier comment about the conditions under which temporary data is written to the temporary file being "more or less a mystery".


Wednesday, February 20, 2013

SQL Anywhere 16 Sneak Peek: ISQL Text Completion

The ISQL Text Completion feature in SQL Anywhere 16 has been improved in three ways:

  • it shows the parameter list when you type the opening "(" of a procedure or function call,

  • it shows the closing bracket when you type an opening bracket, and

  • it shows the closing quote when you type an opening quote.
Here's what the first two look look like:



The parameter list completion works for user-defined functions as well:
CREATE FUNCTION f (
   in parm1 INTEGER,
   in parm2 VARCHAR ( 100 ) )
   RETURNS VARCHAR ( 100 )
BEGIN
   RETURN 'Hello';
END;


The bracket completion works for all three "{", "[" and "(", and the quote completion works for double and single quotes:



To set your preferences for text completion see the ISQL Tools - Options... - Editor - Text Completion dialog box:




Friday, February 15, 2013

Product Suggestion: DEPENDENT AUTOINCREMENT

You're probably familiar with DEFAULT AUTOINCREMENT which can be used to very simply, efficiently and safely initialize a numeric primary key column with the sequence 1, 2, 3, ...

If you use SQL Remote or MobiLink synchronization you're probably also familiar with DEFAULT GLOBAL AUTOINCREMENT which creates the partitioned sequence

  • 1, 2, 3, ... for a database with SET OPTION PUBLIC.global_database_id = '0',

  • 10000001, 10000002, 10000003, ... for a database with global_database_id = '1',

  • 20000001, 20000002, 20000003, ... for a database with global_database_id = '2', and so on,
so that a primary key column can be globally unique across hundreds or thousands of separate databases.

But what about initializing columns in dependent tables, like line_number 1, 2, 3 within order_number 1, then line_number 1, 2, 3 again within order_number 2?

Suggestion: DEFAULT DEPENDENT AUTOINCREMENT

The DEFAULT DEPENDENT AUTOINCREMENT ( column-name ) clause would initialize the column to values 1, 2, 3 within each distinct value of another column-name in the same table, like this:

CREATE TABLE parent (
   pkey   INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data   INTEGER NOT NULL );

CREATE TABLE child (
   fkey   INTEGER NOT NULL REFERENCES parent ( pkey ),
   dkey   INTEGER NOT NULL DEFAULT DEPENDENT AUTOINCREMENT ( fkey ),
   data   INTEGER NOT NULL,
   PRIMARY KEY ( fkey, dkey ) );

BEGIN
   DECLARE @pkey INTEGER;
   INSERT parent VALUES ( DEFAULT, 1 );
   SET @pkey = @@IDENTITY;
   INSERT child VALUES ( @pkey, DEFAULT, 10 );
   INSERT child VALUES ( @pkey, DEFAULT, 20 );
   INSERT parent VALUES ( DEFAULT, 2 );
   SET @pkey = @@IDENTITY;
   INSERT child VALUES ( @pkey, DEFAULT, 30 );
   INSERT child VALUES ( @pkey, DEFAULT, 40 );
   COMMIT;
   SELECT * FROM parent ORDER BY pkey;
   SELECT * FROM child ORDER BY fkey, dkey;
END;

pkey        data        
----------- ----------- 
1           1           
2           2           


fkey        dkey        data        
----------- ----------- ----------- 
1           1           10          
1           2           20          
2           1           30          
2           2           40          

As with other kinds of AUTOINCREMENT columns, the @@IDENTITY connection-level variable would return the most recent value calculated across all columns; i.e, in the example above, @@IDENTITY would contain the successive values 1, 1, 2, 2, 1, 2 after each of the six INSERT statements.


Monday, February 11, 2013

SQL Anywhere 16 Sneak Peek: xp_getenv()

Now you can get the value of server-side environment variables like PATH and LOCALAPPDATA and TEMP inside your SQL scripts, even inside stored procedures, triggers and web services.

When you call xp_getenv ( 'variable' ) from a SQL Anywhere 16 database running on Windows 7, it works for

  • all the environment variables that appear when you run the SET command at the command prompt on the database server,

  • which is a superset of the variables in the Control Panel - All Control Panel Items - System - Advanced System Settings - Environment Variables window,

  • which also includes custom SET WHATEVER values you have defined before starting the database engine,

  • but xp_getenv() doesn't work for the dynamic environment variables like CD and ERRORLEVEL; e.g., %CD% returns the current directory inside a batch file but xp_getenv ( 'CD' ) returns NULL,

  • nor does xp_getenv() work for custom SET variables that fall out of scope before xp_getenv() is called; e.g., the following SELECT returns NULL:
       CALL xp_cmdshell ( 'SET WHATEVER=123' );
       SELECT xp_getenv ( 'WHATEVER' );
    
In other words, environment variables are just a teeny bit mysterious, so check your assumptions at the door and test your xp_getenv calls.

But... but... xp_getenv() is still cool...


Here's how it works:
SELECT xp_getenv ( 'APPDATA' );

xp_getenv('APPDATA')
--------------------------------------------------------------
0x433a5c55736572735c427265636b5c417070446174615c526f616d696e67
Oops, xp_getenv() returns a LONG BINARY string, so CAST is your friend when you're using ISQL:
SELECT CAST ( xp_getenv ( 'APPDATA' ) AS VARCHAR );

xp_getenv('APPDATA')
------------------------------
C:\Users\Breck\AppData\Roaming        
If you need to search the PATH list, you can combine sa_split_list() with xp_getenv() to break it down into its component parts like this:
SELECT row_value AS "Path"
  FROM sa_split_list ( CAST ( xp_getenv ( 'PATH' ) AS VARCHAR ), ';' )
 ORDER BY line_num;

Path
-----------------------------------------------------------------
C:\Program Files\Common Files\Microsoft Shared\Windows Live
C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live
C:\Windows\system32
C:\Windows
...
C:\Program Files (x86)\Sybase\Shared\win32
C:\Program Files (x86)\Sybase\Shared\Sybase Central 4.3\win32
C:\Program Files\SQL Anywhere 16\bin64
C:\Program Files\SQL Anywhere 16\bin32
Here's an example of a server startup script that uses a custom SET VCD=%CD% command to create a copy of the CD dynamic environment variable that (unlike CD) is available to xp_getenv():
SET VCD=%CD%

"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  ddd16.db 

SELECT CAST ( xp_getenv ( 'VCD' ) AS VARCHAR );

xp_getenv('VCD')
----------------
C:\data\xpdemo


Thursday, February 7, 2013

Latest SQL Anywhere EBFs for January 2013

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.1018 Beta          09 Nov 2012
          12.0.1.3827 EBF (SA)      10 Dec 2012
          12.0.1.3798 EBF           07 Nov 2012
          11.0.1.2913 EBF (SA)      19 Dec 2012
          11.0.1.2879 EBF (All) *** 03 Jan 2013 ***

Mac OS    12.0.1.3819 EBF           10 Dec 2012
          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.1018 Beta          09 Nov 2012
          12.0.1.3840 EBF       *** 28 Jan 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...

 SQL Anywhere 16 Beta

 Older EBFs

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

 SQL Anywhere home page 

 Buy SQL Anywhere 

 Developer Edition... 
   [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 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.