Latest SQL Anywhere Updates: 12.0.1.3994 for Linux and Windows

Current builds for the active platforms... HP-UX 16.0 GA Dev Edition download (date n/a) Dev Edition registration Itanium 12.0.1.3894 (EBF 21788 SP60) 16 May 2013 11.0.1.2958 (EBF 21793 SP96) 08 Apr 2013 End of Life 31 May 2014 IBM AIX 16.0 GA Dev Edition download (date n/a) Dev Edition registration 12.0.1.3894 (EBF 21787 SP60) 16 May 2013 11.0.1.2958 (EBF 21792 SP96) 08 Apr 2013 End of Life 31 May 2014 Linux 16.0.0.1691 (EBF 21981 SP6) 31 Oct 2013 On Demand 1.0.4613 (SP3) 13 Sep 2013 12.0.1.3994 (EBF 22111 SP66) *** 27 Nov 2013 *** 12.0.1 Chinese, 16 Apr 2013 Japanese Docs (Eclipse) 16 Apr 2013 11.0.1.3027 (EBF 21786 SP98) 13 Sep 2013 End of Life 31 May 2014 Mac OS 16.0.0.1565 (EBF 21800 SP3) 13 Sep 2013 12.0.1.3958 (EBF 21796 SP64) 19 Sep 2013 11.0.1.2449 Update 29 Jun 2010 End of Life 31 May 2014 Solaris 16.0 GA Dev Edition download (date n/a) Dev Edition registration SPARC 12.0.1.3894 (EBF 21789 SP60) 16 May 2013 11.0.1.2958 (EBF 21794 SP96) 08 Apr 2013 End of Life 31 May 2014 Solaris 16.0 GA Dev Edition download (date n/a) Dev Edition registration x64 12.0.1.3894 (EBF 21790 SP60) 16 May 2013 11.0.1.2958 (EBF 21750 SP96) 08 Apr 2013 End of Life 31 May 2014 Windows 16.0.0.1691 (EBF 21980 SP6) 31 Oct 2013 On Demand 1.0.4613 (SP3) 13 Sep 2013 12.0.1.3994 (EBF 22109 SP66) *** 27 Nov 2013 *** 12.0.1 French, 25 Sep 2012 English, 25 Sep 2012 German, 25 Sep 2012 Chinese, 28 Mar 2013 Japanese Docs (HTML/PDF) 28 Mar 2013 11.0.1.2960 (EBF 21751 SP97) 16 Apr 2013 End of Life 31 May 2014

Other Stuff... Older Updates 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 asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.

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

• The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".
  

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

Beware The Slow Subquery

Here's a snippet from a very curious graphical plan for a large, slow 4-way UNION in a SQL Anywhere 16 stored procedure:



The selected DT (Derived Table) node represents the 3rd SELECT statement in the UNION, and the Subtree Statistics - PercentTotalCost of 66.979% shows that this SELECT is the slowest of the four.

So, why is it curious?

It's curious because the node immediately below the DT, the JNL Nested Loop Join node, shows a Node Statistics - PercentTotalCost of zero and a Subtree Statistics - PercentTotalCost of only 8%... where did the rest of the 66.979% go?

Did it disappear somewhere between the DT and the JNL?

In an effort to find where the time was going, the third SELECT was moved out of the UNION and executed separately. Here's an even-more-curious snippet from the graphical plan for that SELECT:



The DT node has now become the SELECT node, with 100% for the Subtree Statistics - PercentTotalCost.

However, the Work node immediately underneath the SELECT node shows zero for both the Node and Subtree Statistics - PercentTotalCost.

Now we're not just looking for most of the time, we're looking for ALL the time . . .

Where did it go?

It turns out there's more to this query than one giant SELECT FROM... there are a bunch of subqueries in the SELECT list, and one of them is consuming . . . wait for it . . . 98.88% of the time.

To see the subqueries in the SQL Anywhere Plan Viewer, you have to click on the dropdown list that says "Main Query" and look at the "SubQ" entries, in this case SubQ 4:



Back in the "Main Query" display, the SubQ 4 subquery appears as the third-last item in the SELECT list:

expr166[EXISTS(SubQ 4) : 50% Guess,1,0]

The subquery itself isn't so bad, it only took 0.19238% of the time (the Actual(Avg) column in the Node Statistics above)... it's the 514 separate executions, one for each row in the outer SELECT, that brought the total to 98.88% (the Actual(Total) column).

The solution wasn't so hard, either... one more CREATE INDEX, and the execution time for the stored procedure call dropped from 42 seconds to 3.4 seconds.

But the solution's not the point here... the point is, just because a SELECT contains a dozen tables in the FROM clause doesn't mean that's where the time is going.

And just because the Plan Viewer doesn't shout "Hey, look at this slow subquery!" doesn't mean you shouldn't dig down and look for yourself... especially when the Main Query display doesn't account for all the time being spent.

Note: The plans shown here were obtained using the technique shown in Capture Plans With GRAPHICAL_PLAN().

Capture Plans With GRAPHICAL_PLAN()

Sometimes, you already know where the slow queries are in your stored procedures. Other times, a quick run using SQL Anywhere's procedure profiling feature will tell you.

However, knowing the "what" and "where" of query bottlenecks may not be enough, you also need to know the "why". For that, you need to see the graphical plan with statistics for each slow query... and you often can't simply copy and paste the queries into ISQL to see the plans because you won't get realistic results,

You have to run the procedures in their natural habitat

One way to gather plans at runtime is described in Database Tracing In 50 Easy Steps, but that approach has some drawbacks:

• The Database Tracing process is painstaking and the resulting number of plans and level of detail is overwhelming,
  

• there's no easy way to determine which plans goes with which SQL statements in the stored procedures; in other words, Database Tracing doesn't tell you "where", and
  

• the plans are stored in a tracing database rather than written to separate *.saplan files.
  

Another alternative method is to go Old School: modify the stored procedure code to call GRAPHICAL_PLAN() and xp_write_file for each slow query; here's how...

Step 1: Find a slow SQL statement.

Here's an example of a stored procedure that contains a slow query:

CREATE PROCEDURE slow() BEGIN DECLARE @local_variable INTEGER DEFAULT 1; SELECT 'string literal', @local_variable, COUNT_BIG(*) -- trailing comment INTO #temp FROM SYSTAB /* embedded comment */ AS A CROSS JOIN SYSTAB AS B /* multi-line comment */ CROSS JOIN SYSTABCOL AS C; SELECT * FROM #temp; END; SELECT * FROM slow(); expression @local_variable expression1 -------------- --------------- ----------- string literal 1 178123800 (1 rows) Execution time: 44.845 seconds

Here's what it looks like in the SQL Anywhere 16 stored procedure profiler:

Step 2: Add code to capture the plan.

Here's the code template for calling GRAPHICAL_PLAN() to capture a graphical plan with statistics from within a stored procedure:

/* START TEMPORARY CODE */ CALL xp_write_file ( 'C:/temp/graphical_plan.saplan', GRAPHICAL_PLAN ( ' [PASTE SQL STATEMENT HERE] ', 2, -- Detailed statistics including node statistics 'asensitive', 'READ-ONLY' ) ); /* END TEMPORARY CODE */

Here's how to use the code template...

• Insert the code template immediately ahead of the slow query in the stored procedure.
  

• Copy and paste the slow query where it says [PASTE SQL STATEMENT HERE].
  

• Change all embedded single quotes ' to be quote pairs '' (not doublequotes, but two single quotes).
  

• References to local variables are OK; so are trailing --comments, embedded /*comments*/ and the closing semicolon.
  

• Make sure you preserve the correct functionality of the stored procedure. In particular, make sure it's OK to execute the query twice (once by the GRAPHICAL_PLAN() function, and once by the original SQL statement). In this case, the "INTO #temp" must be removed from the query passed to GRAPHICAL_PLAN() for two reasons; first, so multiple rows aren't inserted to the temporary table by two query executions, and second, because GRAPHICAL_PLAN() fails with SQLCODE=-894 for a query that contains an INTO #temp clause.
  

• You may choose to pass only portions of a slow query to GRAPHICAL_PLAN(); for example, the SELECT portion of a DELETE SELECT, or one of many SELECT statements in a large UNION.
  

• You may also choose to code multiple CALL xp_write_file statements, with different *.saplan file names, for multiple slow queries or separate portions of one slow query.
  

Here's what the slow() procedure looks like after the code's been added:

CREATE PROCEDURE slow() BEGIN DECLARE @local_variable INTEGER DEFAULT 1; /* START TEMPORARY CODE */ CALL xp_write_file ( 'C:/temp/graphical_plan.saplan', GRAPHICAL_PLAN ( ' SELECT ''string literal'', @local_variable, COUNT_BIG(*) -- trailing comment -- INTO #temp FROM SYSTAB /* embedded comment */ AS A CROSS JOIN SYSTAB AS B /* multi-line comment */ CROSS JOIN SYSTABCOL AS C; ', 2, -- Detailed statistics including node statistics 'asensitive', 'READ-ONLY' ) ); /* END TEMPORARY CODE */ SELECT 'string literal', @local_variable, COUNT_BIG(*) -- trailing comment INTO #temp FROM SYSTAB /* embedded comment */ AS A CROSS JOIN SYSTAB AS B /* multi-line comment */ CROSS JOIN SYSTABCOL AS C; SELECT * FROM #temp; END; SELECT * FROM slow();

Step 3: Run the procedure to capture the plan.

Here's what graphical_plan.saplan looks like in the SQL Anywhere 16 plan viewer:

---

Fast Random File Read With xp_read_file()

The description of the new READ_SERVER_FILE() procedure introduced in SQL Anywhere 16.0.0.1675 leads one to ask the question, "What makes READ_SERVER_FILE() different from the good old xp_read_file()?

One obvious difference is that READ_SERVER_FILE() lets you specify a substring of the file while xp_read_file() doesn't.

Another difference is that xp_read_file() has a mysterious "lazy" parameter that was introduced in Version 12 of SQL Anywhere:

Enhancements to the xp_read_file system procedure The xp_read_file system procedure now includes an optional parameter that allows you to specify lazy reads. When you specify this optional parameter and its value is not zero, the file is read and then immediately unlocked.

Did you understand that?

No, neither did I, which might be why this new feature didn't get mentioned in the Top 10 Features in SQL Anywhere 12.

Experimentation shows that the xp_read_file "lazy" parameter might as well be called "fast" if you're interested in reading small substrings from large files; here's how you can use it:

• Execute a SET @variable = xp_read_file ( 'filespec', 1 ) statement to open the file but NOT actually read all the data into @variable.
  

• Read substrings of data from the file via SUBSTR ( @variable, @start, @length ) calls.
  

Here are the effects:

• Both operations, the xp_read_file() call and the SUBSTR(), are much faster with lazy = 1 than with the default lazy = 0 . . . not just one of them is faster, but both of them are.
  

  Lazy xp_read_file() call .28900 seconds. Lazy SELECT SUBSTR() .32400 seconds. Non-lazy xp_read_file() call 74.70200 seconds. Non-lazy SELECT SUBSTR() 211.44400 seconds.

• Setting lazy = 1 effectively implements the substring start and length parameters introduced with the new READ_FILE_SERVER() procedure.
  

• For large files and small substrings, setting lazy = 1 virtually eliminates extreme RAM cache growth. In some cases this makes the difference between success and an unresponsive server.
  

Here's the code for the two tests:

-- Lazy test of 352485376 bytes BEGIN DECLARE @large_file_1 LONG VARCHAR; DECLARE @substring_1 VARCHAR ( 100 ); DECLARE @started_at TIMESTAMP; MESSAGE STRING ( 'Lazy test starting.' ) TO CONSOLE; SET @started_at = CURRENT TIMESTAMP; SET @large_file_1 = xp_read_file ( 'D:/Foxhound1 - 3738 after long benchmark tests/foxhound1.db', 1 ); MESSAGE STRING ( 'Lazy xp_read_file() call took ', DATEDIFF ( MILLISECOND, @started_at, CURRENT TIMESTAMP ) / 1000.0, ' seconds.' ) TO CONSOLE; SET @started_at = CURRENT TIMESTAMP; SELECT SUBSTR ( @large_file_1, 200000000, 100 ); MESSAGE STRING ( 'Lazy SELECT SUBSTR() took ', DATEDIFF ( MILLISECOND, @started_at, CURRENT TIMESTAMP ) / 1000.0, ' seconds.' ) TO CONSOLE; END; Now accepting requests Cache size adjusted to 6816K Cache size adjusted to 8532K Lazy test starting. Lazy xp_read_file() call took .28900 seconds. Lazy SELECT SUBSTR() took .32400 seconds. SUBSTR(@large_file_1,200000000,100) 'N@SiyŒŸ²ÅØë¨G$7J]pƒ–©¼Ïâõ\x08\x1b/ATgz\x8d ³ÆÙìÿ\x12%8K^q ... '

-- Non-lazy test of 352485376 bytes BEGIN DECLARE @large_file_2 LONG VARCHAR; DECLARE @substring_2 VARCHAR ( 100 ); DECLARE @started_at TIMESTAMP; MESSAGE STRING ( 'Non-lazy test starting.' ) TO CONSOLE; SET @started_at = CURRENT TIMESTAMP; SET @large_file_2 = xp_read_file ( 'D:/Foxhound1 - 3738 after long benchmark tests/foxhound1.db', 0 ); MESSAGE STRING ( 'Non-lazy xp_read_file() call took ', DATEDIFF ( MILLISECOND, @started_at, CURRENT TIMESTAMP ) / 1000.0, ' seconds.' ) TO CONSOLE; SET @started_at = CURRENT TIMESTAMP; SELECT SUBSTR ( @large_file_2, 200000000, 100 ); MESSAGE STRING ( 'Non-lazy SELECT SUBSTR() took ', DATEDIFF ( MILLISECOND, @started_at, CURRENT TIMESTAMP ) / 1000.0, ' seconds.' ) TO CONSOLE; END; Now accepting requests Cache size adjusted to 8008K Cache size adjusted to 8808K Non-lazy test starting. Cache size adjusted to 9192K Cache size adjusted to 53956K Cache size adjusted to 257692K Cache size adjusted to 409424K Cache size adjusted to 825356K Non-lazy xp_read_file() call took 74.70200 seconds. Non-lazy SELECT SUBSTR() took 211.44400 seconds. Cache size adjusted to 1282136K SUBSTR(@large_file_2,200000000,100) 'N@SiyŒŸ²ÅØë¨G$7J]pƒ–©¼Ïâõ\x08\x1b/ATgz\x8d ³ÆÙìÿ\x12%8K^q ... '

---

Techwave 2014

The official title is "ISUG Tech" but the URL says "techwave2014":

ISUG Tech Annual Conference, April 14-17, 2014 in Atlanta GA.

There's a "Call for Papers" open now, but not much information is available yet.

---

New Features Since GA: SQL Anywhere 16

Since the SQL Anywhere 16.0 "General Availability" build 1324 was released, there have been a number of EBFs that contained new features as well as fixes. Chances are, this trend will continue, and there won't be any interim "16.1" or "16.0.1" releases before Version 17 is released (which won't be happening any time soon).

That means if you want to know What's New in SQL Anywhere 16 since it first came out, you have to read the "read me" file for the latest EBF... or read this excerpt, where some the more interesting entries have been highlighted)...

1444 The new TIMESTAMPADD and TIMESTAMPDIFF functions have been added to ODBC and JDBC {escape sequence} support.
1451 The MobiLink plug-in test window records changes in SQL files before applying them to the databases.
1453 The new MobiLink plug-in "Duplicate" item creates a copy of the synchronization model in the same project.
1453 The Relay Server Record affinity flag now allows the value 'x' to expire the affinity cookie for troubleshooting.
1484 The MobiLink server now supports consolidated databases running on Sybase IQ 16.0 servers.
1537 The Sybase Central Plug-in has a new "Show Inherited" to show inherited object provileges.
1537 CREATE INDEX for local temporary tables is now allowed on read-only databases.
1614 Support files for new versions of the PHP interpreter will be available as separate downloads in the future.
1648 The OData Producer now respects Content-Encoding and Accept-Encoding HTTP request headers
1665 The dbo.sp_list_directory() stored procedure returns 5 additional columns.
1670 Strong encryption now achieved using OpenSSL instead of Certicom.
1673 The geospatial method ST_BUFFER is now supported for all geometry types.
1674 The new BackupInProgress database property returns 'on' when a backup is running and 'off' otherwise.
1674 The new TimeWithoutClientConnection database property returns the time since a connection existed.
1675 The new READ_SERVER_FILE() system function reads a file on the server.
1688 Error detection has been improved for additional FROM clauses in Syntax 2 of the DELETE and UPDATE statements.
1691 The Oracle JRE previously shipped with SQL Anywhere 16 has now been replaced by the SAP JRE.
1691 The minimum supported CPU architecture for UltraLiteJ for Android is now ARMv7.

  MobiLink - Java Plugin for Sybase Central

    ================(Build #1453  - Engineering Case #733180)================

 In the MobiLink plug-in, the popup menu for a synchronization model now contains 
 a new item, “Duplicate”. This item creates a copy of the synchronization 
 model in the same project. The name the user provides is used for the name 
 of the copy, as well as the script version and publication name values of 
 the new synchronization model. This feature is useful, when there is a working 
 synchronization system and a copy of it is required as a starting place for 
 making the next version of the system.

    ================(Build #1451  - Engineering Case #733174)================

 When the test window in the MobiLink plug-in is opened, it first deploys 
 the synchronization model to the consolidated database and to a newly created 
 remote database.  In the past, changes were made directly to the databases 
 to prepare for synchronization.  This behavior is now changed so that SQL 
 files are generated containing the changes to be made.  The SQL files are 
 then automatically applied to the databases.  This is consistent with the 
 way deployment is handled when the deployment wizard is used. 
 
 This should result in no user visible change in behavior, but it will ensure 
 that going forward behavior seen when testing a synchronization model in 
 the test window is consistent with that seen when the model is actually deployed 
 using the deployment wizard.

  MobiLink - Relay Server

    ================(Build #1453  - Engineering Case #732958)================

 The existing affinity flag in the Relay Server Record has been extended to 
 carry a value of ‘x’ when the Relay Server told the client to expire the 
 affinity cookie. This can be useful for troubleshooting.

  MobiLink - iAS Branded ODBC Drivers

    ================(Build #1484  - Engineering Case #735343)================

 The MobiLink server now supports consolidated databases running on Sybase 
 IQ 16.0 servers.  For the recommended ODBC drivers for Windows and Linux, 
 please visit the following link: 
   http://www.sybase.com/detail?id=1011880 
 
 The Row Level Versioning (RLV) feature introduced in Sybase IQ 16.0 has 
 removed the “single-writer” limitation.  Therefore the IQ 16.0 server now 
 allows multiple connections modifying a RLV enabled table concurrently. Based 
 on testing, the upload would be ten times faster for synchronizations with 
 RLV enabled tables than with RLV disabled tables.  Therefore, in order to 
 get better upload performance, all sync tables are recommended to be RLV 
 enabled.  However, if there is any table that cannot be RLV enabled, for 
 instance a sync table that contains BLOBs and/or foreign keys, the upload 
 phase must be serialized.  This requirement can be achieved, if the begin_upload 
 connection script is written to include or to use the following SQL statement:
   LOCK TABLE table_name IN WRITE MODE WAIT time_string
 
 where table_name is the name of a table that is defined on the IQ store 
 and the time_string gives the maximum time period to lock the table.  The 
 table can be as simple as the one defined as:
   create table coordinate_upload ( c1 int )
 
 It is not required to have any data. If any of the other MobiLink server 
 transactions is required to modify any IQ tables, all of these transactions 
 must be serialized as well.  The same logic mentioned above can be used.  
 This technique is considered more efficient than retries on each of the transactions 
 by the MobiLink server.

  SQL Anywhere - ODBC Client Library

    ================(Build #1444  - Engineering Case #731978)================

 ODBC (and JDBC) escape sequence support has been enhanced to include the 
 following functions.
 
 {fn TIMESTAMPADD(<interval>, <integer-expr>, <timestamp-expr>)}
 
 Returns the timestamp calculated by adding <integer-expr> intervals 
 of type <interval> to <timestamp-expr>. Valid values of <interval> 
 are shown below.
 
 {fn TIMESTAMPDIFF(<interval>, <timestamp-expr1>, <timestamp-expr2>)}
 
 Returns the integer number of intervals of type <interval> by which 
 <timestamp-expr2> is greater than <timestamp-expr1>. Valid values 
 of <interval> are shown below.
 
 These escape functions are mapped directly to the SQL Anywhere DATEADD/DATEDIFF 
 functions.
 
 The <interval> type can be one of the following:
 <interval>                 SQL Anywhere DATEADD/DATEDIFF date-part 
 mapping
 =========================  ==========================================
 SQL_TSI_YEAR               YEAR
 SQL_TSI_QUARTER            QUARTER
 SQL_TSI_MONTH              MONTH
 SQL_TSI_WEEK               WEEK
 SQL_TSI_DAY                DAY
 SQL_TSI_HOUR               HOUR
 SQL_TSI_MINUTE             MINUTE
 SQL_TSI_SECOND             SECOND
 SQL_TSI_FRAC_SECOND        MICROSECOND
 
 Examples:
 // Number of days in February, 2013
 SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, '2013-02-01T00:00:00', '2013-03-01T00:00:00'  
 )}
 28
 // Timestamp for 28 days after February 1, 2013
 SELECT {fn TIMESTAMPADD(SQL_TSI_DAY, 28, '2013-02-01T00:00:00' )}
 2013-03-01 00:00:00.000000

  SQL Anywhere - OData Server

    ================(Build #1648  - Engineering Case #746461)================

 The OData Producer now respects Content-Encoding and Accept-Encoding HTTP 
 request headers as specified by the HTTP 1.1 spec:
  http://www.w3.org/Protocols/rfc2616/rfc2616-sec14.html
 
 The Content-Encoding header is used by clients to indicate the encoding 
 of the request body. The Accept-Encoding header is used by clients to indicate 
 the preferred encoding of the response body.

  SQL Anywhere - Other

    ================(Build #1691  - Engineering Case #749465)================

 Previously, an Oracle JRE was shipped with the software for use by clients. 
 Now, the SAP JRE is shipped instead.
 
 Upgrading overwrites the JRE directory (%SQLANY16%\binXX\jre170) and its 
 subdirectories. If you are using certificates, then your certificate store 
 (%SQLANY16%\binXX\jre170\lib\security\cacerts) is overwritten, including 
 your certificates. Similarly, fonts you added to the %SQLANY16%\binXX\jre170\lib\fonts\fallback 
 directory to help display characters in the administration tools may be lost. 
 To minimize upgrading steps with regards to the JRE change, create a backup 
 copy of the JRE directory and all of its subdirectories before you upgrade 
 so that you can refer to or restore files (such as cacerts) from the backup, 
 as needed. To restore settings, use the java_vm_options option (SQL Anywhere), 
 and/or the -sl java option (MobiLink) to optimize your Java VM startup settings.

    ================(Build #1670  - Engineering Case #749256)================

 Strong encryption now achieved using OpenSSL
 --------------------------------------------
 
 Prior to this change, SQL Anywhere included a Certicom encryption module 
 that provided strong encryption used throughout the software. Now, SQL Anywhere 
 includes an OpenSSL encryption module for the strong encryption. The Certicom 
 encryption module has been removed.
 
 Read the following descriptions to determine how you may be impacted by 
 this change.
 
 FIPS encryption now requires the private key of an identity file to be encrypted 
 using AES 
 
 -  OpenSSL FIPS supports AES encryption for the private key of an identity 
 file. New servers using the OpenSSL FIPS encryption module will not start 
 when using an identity file that has its private key encrypted with 3DES. 
 You must re-encrypt the identity file using AES. To do this, run a command 
 similar to the following using an upgraded viewcert utility:
 
 viewcert -p -o new-file-name -op new-password -ip old-password old-file-name
 
 The new and old passwords can be the same.
 
 -  The sample server identity file (rsaserver.id) and client identity file 
 (rsaclient.id) have been modified so that the private keys are encrypted 
 using AES rather than 3DES.
 
 - Versions of the server that use the Certicom encryption module will not 
 start when using an identity file that has its private key encrypted using 
 AES. Trusted root certificate files specified using trusted_certificates 
 do not need to be modified.
 
 Self-signed certificates must now have the Certificate Signing attribute 
 set
  
 Self-signed certificates must now have the Certificate Signing attribute 
 set when using the identity encryption option (for example, the -x mlsrvXX 
 and -xs dbsrvXX options). To determine if a certificate has the Certificate 
 Signing attribute set, use the viewcert utility and look for the Certificate 
 Signing attribute in the Key Usage portion of the output. If your self-signed 
 certificates do not have the Certificate Signing attribute set, then you 
 must regenerate the certificates.
 
 Create Certificate utility (createcert) now uses AES encryption instead 
 of 3DES 
 
 The Create Certificate utility (createcert) now uses AES rather than 3DES 
 encryption for encrypting the private key in the server identity file.
 
 A new option, -3des, has been added to the Create Certificate utility. Use 
 this option when you want to create a 3DES-encrypted server identity file 
 that can be used by both new and old servers. Note that new servers running 
 in FIPS mode cannot start using 3DES-encrypted certificates; however, if 
 you are not running in FIPS mode, then you can use 3DES-encrypted certificates.
 
 View Certificate utility (viewcert) now uses AES encryption instead of 3DES 
 
 The View Certificate utility (viewcert) now uses AES rather than 3DES encryption 
 when you specify the -p option to PEM-encode the output and when you specify 
 the -ip and -op options to set the password.
 
 A new option, -3des, has been added to the View Certificate utility to allow 
 you encrypt output and passwords using 3DES instead of AES.
 
 Database server now loads the FIPS driver file, dbfipsXX.dll, at startup
  
 Previously, the 32-bit Windows database server loaded the FIPS driver file, 
 dbfipsXX.dll, only when needed. Now, the 32-bit Windows database server always 
 attempts to load dbfipsXX.dll at startup, and keeps it loaded for the life 
 of the server. If loading dbfipsXX.dll fails, then an error is returned only 
 when an attempt is made to use FIPS encryption.
 
 Deploying FIPS 
 
 If you are deploying FIPS encryption, then there are new shared libraries 
 to deploy; these files are included in your software. The former files, sbgse2.dll 
 and libsbgse2.so, are no longer installed by the software. The new files 
 to deploy are:
  - Windows 64-bit: libeay32.dll, ssleay32.dll, and msvcr100.dll
  - Windows 32-bit: libeay32.dll, ssleay32.dll, and msvcr90.dll
  - Linux: libcrypto.so and libssl.so
 
 Note: On Windows, although 32-bit and 64-bit FIPS-certified OpenSSL libraries 
 for encryption are provided, you must use the 64-bit libraries on a 64-bit 
 system.
  
 MobiLink-related changes and information 
 
 Connecting to a MobiLink server using client-side certificates now requires 
 the Digital Signature certificate attribute to be set 
 TLS/SSL connections to a MobiLink server using client-side certificates 
 now require the client-side certificate to have the Digital Signature attribute 
 set. If the attribute is not set, then the connection will fail.
 To determine if a certificate has the Digital Signature attribute set, use 
 the View Certificate utility (viewcert) and look for the Digital Signature 
 attribute in the Key Usage portion of the output. If your client-side certificates 
 do not have the Digital Signature attribute set, then you must regenerate 
 the certificates.
 
 FIPS-based end-to-end encryption now requires the private key to be encrypted 
 using AES 
 If the private key file provided to a MobiLink server by the e2ee_private_key 
 file option of the –x command-line option is encoded using 3DES and you are 
 running in FIPS mode, then the private key file needs to be regenerated with 
 the private key encrypted using AES.
 
 How to update a MobiLink deployment that uses non-FIPS TLS/SSL (includes 
 HTTPS) and client-side certificates 
 1. If your client-side identity certificates do not have the Digital Signature 
 attribute set and the client connects directly to the MobiLink server, then 
 you must regenerate and deploy client-side certificates with the Digital 
 Signature attribute set. 
 2. Update the server-side binaries. 
 3. Update the client-side binaries. 
 
 How to update a MobiLink deployment that uses FIPS, TLS/SSL (includes HTTPS) 
 and client-side certificates 
 These steps update the client identity certificates twice if the Digital 
 Signature attribute is missing from client-side identity certificates. This 
 procedure can make the update less disruptive because synchronizations can 
 continue without having to coordinate the client-side and server-side updates 
 to occur at the same time. 
 1. If your current client-side identity certificates do not have the Digital 
 Signature attribute set and the client connects directly to the MobiLink 
 server, then you must regenerate and deploy client-side certificates with 
 the Digital Signature attribute set. 
 2. Update the server-side binaries (remembering to include the new FIPS 
 driver files) and deploy server identity certificates with AES-encrypted 
 private keys. 
 3. Update the client-side binaries (remembering to include the new FIPS 
 driver files) and deploy client identity certificates with AES-encrypted 
 private keys.
  
 How to update a MobiLink deployment that uses FIPS and end-to-end encryption 
 1. Regenerate the primary key file referenced by the e2ee_private_key encryption 
 option. 
 2. Shut down the MobiLink server. 
 3. Update the MobiLink server binaries, remembering to include the new required 
 FIPS driver files. 
 4. Change the e2ee_private_key option to point to the new private key file 
 (or replace the old file), updating the e2ee_private_key_password, if required. 
 5. Restart the MobiLink server. 

  SQL Anywhere - Server

    ================(Build #1688  - Engineering Case #747805)================

 For Syntax 2 of the DELETE statement and Syntax 2 of the UPDATE statement 
 the error detection behaviour of the server has been improved. These two 
 syntax forms allow an additional FROM clause that may contain the table-name 
 of the updated or deleted table, for example:
 
             DELETE
             FROM  [owner.]table_1 [ [ AS ] correlation-name ]
             FROM  [owner.]table_1 [ [ AS ] correlation-name ] ...
             WHERE  ...
 and
             UPDATE  [owner.]table_1 [ [ AS ] correlation-name ]
             SET  columns_1 = ...
             FROM     [owner.]table_1 [ [ AS ] correlation-name ] ...
             WHERE ...
             
 If the DELETE or UPDATE clause and the additional FROM clause have a table 
 reference that contains the same table name, in the above example "table_1", 
 then the server can only decide whether both are identical table references 
 if one of the following conditions is true:
     - both table references are not qualified by specifying a user ID
     - both table references are qualified by specifying a user ID
     - both table references are specified with a correlation name
 
 In cases where the server cannot decide whether the above table references 
 are identical or not it will now return an SQL error to prevent the user 
 from unintended semantics like deleting and updating to many rows.

    ================(Build #1675  - Engineering Case #747798)================

 A new system function has been added, READ_SERVER_FILE(). This function reads 
 data from a specified file on the server and returns the full or partial 
 contents of the file as a LONG BINARY value.
 
 Syntax:
 READ_SERVER_FILE( filename ) [, start [ , length] ] 
 
 Parameters:
 - filename  LONG VARCHAR value indicating the path and name of the file 
             on the server. 
   
 - start     The start position of the file to read, in bytes. The first byte in 
        the file is at position 1. A negative starting position specifies 
        the number of bytes from the end of the file rather than from the 
        beginning. 
        * If start is not specified, a value of 0 is used. 
        * If start is zero and length is non-negative, a start value of 1 
   is used. 
        * If start is zero and length is negative, a start value of -1 is 
   used. 
   
 - length    The length of the file to read, in bytes. 
          * If length is not specified, the function reads from the starting 
   position to the end of the file. 
        * If length is positive, the function read ends length bytes to the 
   right of the starting position. 
        * If length is negative, the function returns at most length bytes 
   up to, and including, the starting position, from the left of the 
   starting position. 
 
 Returns:
 LONG BINARY
 
 Remarks:
 This function returns the full or partial (if start and/or length are specified) 
 contents of the named file as a LONG BINARY value. If the file does not 
 exist or cannot be read, NULL is returned. 
 
 filename is relative to the starting directory of the database server.
  
 The READ_SERVER_FILE function supports reading files larger than 2GB. However, 
 the returned content is limited to 2GB. If the returned content exceeds 
 this limit, a SQL error is returned. 
 
 If the data file is in a different character set, you can use the CSCONVERT 
 function to convert it. You can also use the CSCONVERT function to address 
 the character set conversion requirements you may have when using the 
 READ_SERVER_FILE server function. 
 
 If disk sandboxing is enabled, the file referenced in filename must in an 
 accessible location. 
 
 Privileges:
 When reading from a file on a client computer:
   * You must have the READ FILE system privilege.
   * You must have read permissions on the directory being read from.
 
 Standards: SQL/2008   Vendor extension. 
 
 Example:
 The following statement reads 20 bytes in a file, starting from byte 100 
 of the file.
   SELECT READ_SERVER_FILE( 'c:\\data.txt', 100, 20 )
 
 See also
   * xp_read_file system procedure
   * CSCONVERT function [String]
   * Disk sandboxing

    ================(Build #1674  - Engineering Case #747277)================

 A new database property, BackupInProgress, has been added. Querying the property 
 will return 'on' when there is a backup happening, and 'off' otherwise.

    ================(Build #1673  - Engineering Case #747205)================

 The geospatial method ST_BUFFER is now supported for all geometry types.  
 This method is compatible with the SQL/MM and OGC standards. ST_BUFFER returns 
 the ST_Geometry value that represents all points whose distance from any 
 point of an ST_Geometry value is less than or equal to a specified distance 
 in the given units.
 
 ST_GEOMETRY::ST_BUFFER( distance double, unit_name long varchar )
  - distance: The distance the buffer should be from the geometry value.  
 Must be greater than or equal to 0.
  - unit_name: The units in which the distance parameter should be interpreted. 
 Defaults to the unit of the spatial reference system.  The unit name must 
 match the UNIT_NAME column of a row in the ST_UNITS_OF_MEASURE view where 
 UNIT_TYPE is 'LINEAR'.
  - Returns the ST_Geometry value representing all points within the specified 
 distance of the original geometry.
 
 The ST_Buffer method generates a geometry that expands a geometry by the 
 specified distance. This method can be used, for example, to find all points 
 in geometry A that are within a specified distance of geometry B. The distance 
 parameter must be a positive value.  This method will return an error if 
 distance is negative. If the distance parameter is equal to 0, the original 
 geometry is returned. The ST_Buffer method is best used only when the actual 
 buffer geometry is required.  Determining whether two geometries are within 
 a specified distance of each other should be done using ST_WithinDistance 
 instead.

    ================(Build #1665  - Engineering Case #746935)================

 The dbo.sp_list_directory() stored procedure can be used to obtain information 
 about directories and files that are accessible to the SQL Anywhere Server. 
 Currently the sp_list_directory() procedure returns the following three columns:
 
 file_path long nvarchar the path of the server accessible file or directory
 file_type nvarchar(1) either F for file or D for directory
 file_size unsigned bigint the size of the file or NULL for directories
 
 In order to provide more information about the various files and directories, 
 dbo.sp_list_directory() has now been enhanced to return five additional columns. 
 These five additional columns are:
 
 owner   nvarchar(128)   the owner of the file or directory
 create_date_time* timestamp with time zone the date and time the file or 
 directory was created
 modified_date_time* timestamp with time zone the date and time the file 
 or directory was last modified
 access_date_time* timestamp with time zone the date and time the file or 
 directory was last accessed
 permissions  varchar(10)   the set of access permissions for the file or 
 directory
 
 All other aspects of dbo.sp_list_directory() – including the set of system 
 privileges and secure feature privileges – remain unchanged.
 
 A database either has to be upgraded or initialized in order for applications 
 to obtain this new information from dbo.sp_list_directory(). In addition, 
 if an upgraded or newly initialized database is subsequently moved to an 
 older version of the server, then the new columns will continue to be returned 
 but the values of the new columns will be NULL.

    ================(Build #1614  - Engineering Case #744027)================

 The SQL Anywhere PHP External Environment supports several versions of the 
 PHP interpreter. The SQL Anywhere install bundle includes a separate PHP 
 external environment dll or shared object for each supported version of PHP. 
 In addition, whenever support for a new version of the PHP interpreter is 
 added, the SQL Anywhere install bundle is updated to include the new PHP 
 external environment dll or shared object for the new version of the PHP 
 interpreter. Going forward, the SQL Anywhere install bundle will no longer 
 be updated with additional PHP external environment dlls or shared objects 
 when support for new versions of the PHP interpreter are added. Instead, 
 the new PHP external environment dlls and shared objects will now only be 
 available on the download site.

    ================(Build #1537  - Engineering Case #737497)================

 Previously, the CREATE INDEX statement for local temporary tables on read-only 
 nodes had been disallowed. This has been changed, and now local temporary 
 tables are the only tables where index creation is allowed on the read-only 
 databases.

    ================(Build #1473  - Engineering Case #734038)================

 The database property TimeWithoutClientConnection has been added.
 
 The description for this database property is:
 
 Returns the elapsed time in seconds since a CmdSeq or TDS client connection 
 to the database existed.  If there has not been a CmdSeq or TDS connection 
 since the database started then the time since the database started is returned.  
 If one or more CmdSeq or TDS connections are currently connected, 0 is returned.

  SQL Anywhere - Sybase Central Plug-in

    ================(Build #1537  - Engineering Case #739081)================

 Inherited object privileges can now be viewed for any table, view, procedure, 
 function, sequence generator, or dbspace via the “Privileges” tabs.
 
 Also, inherited object privileges can now be viewed for any user or role 
 via the “Table Privileges”, “View Privileges”, “Procedure Privileges”, “Sequence 
 Privileges”, and “Dbspace Privileges” tabs.
 
 In both cases, a new “Show Inherited” check box has been added to the tabs. 
 With the check box checked, the tabs show privileges that are inherited through 
 role inheritance, in addition to privileges that are granted explicitly.

  UltraLiteJ - Runtime

    ================(Build #1691  - Engineering Case #748717)================

 UltraLiteJ for Android no longer supports ARMv5 CPU architectures. The minimum 
 supported CPU architecture is now ARMv7.

---

Latest SQL Anywhere Updates: 16.0.0.1691 for Linux and Windows

Current builds for the active platforms... HP-UX 16.0 GA Dev Edition download (date n/a) Dev Edition registration Itanium 12.0.1.3894 (EBF 21788 SP60) 16 May 2013 11.0.1.2958 (EBF 21793 SP96) 08 Apr 2013 End of Life 31 May 2014 IBM AIX 16.0 GA Dev Edition download (date n/a) Dev Edition registration 12.0.1.3894 (EBF 21787 SP60) 16 May 2013 11.0.1.2958 (EBF 21792 SP96) 08 Apr 2013 End of Life 31 May 2014 Linux 16.0.0.1691 (EBF 21981 SP6) *** 31 Oct 2013 *** On Demand 1.0.4613 (SP3) 13 Sep 2013 12.0.1.3967 (EBF 21835 SP65) 30 Sep 2013 12.0.1 Chinese, 16 Apr 2013 Japanese Docs (Eclipse) 16 Apr 2013 11.0.1.3027 (EBF 21786 SP98) 13 Sep 2013 End of Life 31 May 2014 Mac OS 16.0.0.1565 (EBF 21800 SP3) 13 Sep 2013 12.0.1.3958 (EBF 21796 SP64) 19 Sep 2013 11.0.1.2449 Update 29 Jun 2010 End of Life 31 May 2014 Solaris 16.0 GA Dev Edition download (date n/a) Dev Edition registration SPARC 12.0.1.3894 (EBF 21789 SP60) 16 May 2013 11.0.1.2958 (EBF 21794 SP96) 08 Apr 2013 End of Life 31 May 2014 Solaris 16.0 GA Dev Edition download (date n/a) Dev Edition registration x64 12.0.1.3894 (EBF 21790 SP60) 16 May 2013 11.0.1.2958 (EBF 21750 SP96) 08 Apr 2013 End of Life 31 May 2014 Windows 16.0.0.1691 (EBF 21980 SP6) *** 31 Oct 2013 *** On Demand 1.0.4613 (SP3) 13 Sep 2013 12.0.1.3967 (EBF 21834 SP65) 30 Sep 2013 12.0.1 French, 25 Sep 2012 English, 25 Sep 2012 German, 25 Sep 2012 Chinese, 28 Mar 2013 Japanese Docs (HTML/PDF) 28 Mar 2013 11.0.1.2960 (EBF 21751 SP97) 16 Apr 2013 End of Life 31 May 2014

Other Stuff... Older Updates 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 asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.

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

• The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".
  

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

Protecting OLTP Performance After Failover

An earlier article High Availability Demo, Revised discussed the possibility that read-only connections to the secondary or mirror database in a High Availability setup could suddenly be allowed to perform updates after a failover when the read-only secondary database suddenly becomes the updatable primary.

This happens because read-only connections don't get dropped when the failover occurs because the database they're connected to (the secondary) is still up and running. This behavior is intended, as discussed in Configuring read-only access to a database running on the mirror server.

Updates aren't the problem . . .

Chances are, a read-only connection isn't suddenly going to start performing updates when the DB_PROPERTY ( 'ReadOnly' ) changes from 'On' to 'Off' after a failover... so maybe nobody cares about this behavior.

. . . performance is

The risk of updates may not be a concern to anyone, even if those read-only connections did start updating data... that's not the reason the read-only connections were segregated to run on a separate server. They were moved so the resource-intensive OLAP queries would not affect OLTP response time on the primary database server.

After a failover, even if the read-only connections don't start performing updates, all the OLTP work moves over to the same server that's doing the OLAP Queries From Hell... there's nothing automatic to tell the OLAP workload to go away, and the OLTP workload has to share the same server.

Preserving The Divine Right Of OLTP After FailOver

That's what this article is about: Implementing a process that will automatically disconnect all the read-only connections immediately after a failover, forcing them to wait and reconnect when the read-only secondary server becomes available again.

Here's how it's done, with a CREATE EVENT TYPE MirrorFailover that is executed when either of the following events occur:

• A database is started up and it determines that it should be the primary database in a High Availabiliy setup, or
  

• a secondary (mirror) database in a High Availability setup becomes the primary database after a failover.
  

The first case is not a problem: the High Availability setup is just starting up and there are no connections worth dropping.

In the second case, however, all of the connections on the former-secondary-and-now-primary database should be dropped because they are all OLAP connections which will soon be joined by all those OLTP connections that were dropped during failover and are now going to reconnect to the new primary. In other words, everything needs to be dropped... or almost everything as the code will show:

CREATE EVENT failover TYPE MirrorFailover
HANDLER BEGIN

DECLARE @other_connection_id   INTEGER;
DECLARE @sql                   VARCHAR ( 1000 );

MESSAGE STRING ( CURRENT TIMESTAMP, ' MirrorFailover event has started...' ) TO CONSOLE;
MESSAGE which_database() TO CONSOLE;

IF DB_PROPERTY ( 'MirrorRole' ) = 'primary' THEN

   SET @other_connection_id = NEXT_CONNECTION ( NULL );

   WHILE @other_connection_id IS NOT NULL LOOP

      IF  @other_connection_id <> @@SPID  
      AND @other_connection_id < 1000000000 
      AND CONNECTION_PROPERTY ( 'Name', @other_connection_id ) LIKE 'OLAP%' THEN
         MESSAGE STRING ( 
            CURRENT TIMESTAMP,   
            ' MirrorFailover event is dropping connection ',  
            @other_connection_id,
            ' ("',
            CONNECTION_PROPERTY ( 'Name', @other_connection_id ),  
            '")...' ) TO CONSOLE;
         SET @sql = STRING (  
            'DROP CONNECTION ',  
            @other_connection_id );
         EXECUTE IMMEDIATE @sql;
      END IF;

      SET @other_connection_id = NEXT_CONNECTION ( @other_connection_id ) 

   END LOOP;

END IF;

END;

Here's how the code works:

The IF statement on line 10 is probably unnecessary because the MirrorFailover is only fired on a primary database, but it can't hurt.

The SET ... WHILE ... SET ... END LOOP statements on lines 12, 14, 32 and 34 step through all of the connections to the database, including client connections and internal connections like the one this EVENT is running on. It's the same logic that appeared previously in the article on p_drop_other_connections.

The IF statement on lines 16 through 18 determines which connections should be dropped. How you write this IF is up to you, but it's important not to drop the current connection, or any internal connections (ones that have large connection numbers like "INT: ApplyRecovery" or events like "DatabaseStart"). In the code shown here, the CON= connection name property is checked to make sure that only "OLAP..." connections are dropped.

The SET and EXECUTE statements on lines 26 through 29 do the actual work of dropping the OLAP connections.

The MESSAGE statements writes a record of what happened to the server's diagnostic console log file, including the MESSAGE statement on line 8 which calls this procedure:

CREATE FUNCTION which_database() RETURNS LONG VARCHAR BEGIN RETURN STRING ( 'PROPERTY ( ''MachineName'' ) = ', PROPERTY ( 'MachineName' ), '\x0d\x0a', 'PROPERTY ( ''Name'' ) = ', PROPERTY ( 'Name' ), '\x0d\x0a', 'PROPERTY ( ''ServerName'' ) = ', PROPERTY ( 'ServerName' ), '\x0d\x0a', 'PROPERTY ( ''TcpIpAddresses'' ) = ', PROPERTY ( 'TcpIpAddresses' ), '\x0d\x0a', 'DB_PROPERTY ( ''Name'' ) = ', DB_PROPERTY ( 'Name' ), '\x0d\x0a', 'DB_PROPERTY ( ''ReadOnly'' ) = ', DB_PROPERTY ( 'ReadOnly' ), '\x0d\x0a', 'DB_PROPERTY ( ''ArbiterState'' ) = ', DB_PROPERTY ( 'ArbiterState' ), '\x0d\x0a', 'DB_PROPERTY ( ''PartnerState'' ) = ', DB_PROPERTY ( 'PartnerState' ), '\x0d\x0a', 'DB_PROPERTY ( ''MirrorMode'' ) = ', DB_PROPERTY ( 'MirrorMode' ), '\x0d\x0a', 'DB_PROPERTY ( ''MirrorRole'' ) = ', DB_PROPERTY ( 'MirrorRole' ), '\x0d\x0a', 'DB_PROPERTY ( ''MirrorServerState'' ) = ', DB_PROPERTY ( 'MirrorServerState' ), '\x0d\x0a', 'DB_PROPERTY ( ''MirrorServerWaits'' ) = ', DB_PROPERTY ( 'MirrorServerWaits' ), '\x0d\x0a', 'DB_PROPERTY ( ''MirrorState'' ) = ', DB_PROPERTY ( 'MirrorState' ), '\x0d\x0a', 'DB_PROPERTY ( ''File'' ) = ', DB_PROPERTY ( 'File' ), '\x0d\x0a', 'CONNECTION_PROPERTY ( ''Number'' ) = ', CONNECTION_PROPERTY ( 'Number' ), '\x0d\x0a', 'CONNECTION_PROPERTY ( ''Name'' ) = ', CONNECTION_PROPERTY ( 'Name' ), '\x0d\x0a', 'CONNECTION_PROPERTY ( ''CommLink'' ) = ', CONNECTION_PROPERTY ( 'CommLink' ), '\x0d\x0a', 'CONNECTION_PROPERTY ( ''CommNetworkLink'' ) = ', CONNECTION_PROPERTY ( 'CommNetworkLink' ) ); END;

Here's what the failover looks like, as shown by the diagnostic console log for the secondary-becoming-primary database server:

I. 11/03 09:27:41. Database server started at Sun Nov 03 2013 09:27 I. 11/03 09:27:41. Starting database "demo" (C:\temp\server2\demo.db) at Sun Nov 03 2013 09:27 I. 11/03 09:27:41. Trying to start SharedMemory link ... I. 11/03 09:27:41. Performance warning: Database file "C:\temp\server2\demo.db" consists of 3 disk fragments I. 11/03 09:27:41. SharedMemory link started successfully I. 11/03 09:27:41. Database recovery in progress I. 11/03 09:27:41. Trying to start TCPIP link ... I. 11/03 09:27:41. Last checkpoint at Sun Nov 03 2013 09:27 I. 11/03 09:27:41. Checkpoint log... I. 11/03 09:27:41. Starting on port 55502 I. 11/03 09:27:41. TCPIP link started successfully I. 11/03 09:27:41. Now accepting requests I. 11/03 09:27:51. Database "demo" mirroring: local status: role=primary, state=synchronizing, sequence=1, yielding=N I. 11/03 09:27:51. Transaction log: demo.log... I. 11/03 09:27:51. Database "demo" mirroring: determining mirror role ... E. 11/03 09:27:51. Database "demo" mirroring: mirror connection to server "partner1_demo" failed: database not found I. 11/03 09:27:52. Database "demo" mirroring: mirror partner connected I. 11/03 09:27:52. Database "demo" mirroring: mirror arbiter connected I. 11/03 09:27:52. Database "demo" mirroring: arbiter status: role=mirror, state=synchronizing, sequence=1, yielding=N I. 11/03 09:27:53. Database "demo" mirroring: partner status: role=mirror, state=synchronizing, sequence=1, yielding=N I. 11/03 09:27:53. Database "demo" mirroring: synchronizing ... I. 11/03 09:27:53. Database "demo" (demo.db) started as mirror at Sun Nov 03 2013 09:27 I. 11/03 09:27:53. Starting checkpoint of "demo" (demo.db) at Sun Nov 03 2013 09:27 I. 11/03 09:27:53. Finished checkpoint of "demo" (demo.db) at Sun Nov 03 2013 09:27 I. 11/03 09:27:53. Database "demo" mirroring: synchronized I. 11/03 09:32:19. Database "demo" mirroring: mirror partner disconnected I. 11/03 09:32:19. Database "demo" mirroring: becoming primary server I. 11/03 09:32:20. Rollback log... I. 11/03 09:32:20. Checkpointing... I. 11/03 09:32:20. Starting checkpoint of "demo" (demo.db) at Sun Nov 03 2013 09:32 I. 11/03 09:32:20. Finished checkpoint of "demo" (demo.db) at Sun Nov 03 2013 09:32 I. 11/03 09:32:20. Recovery complete I. 11/03 09:32:20. Database "demo" (demo.db) started at Sun Nov 03 2013 09:32 I. 11/03 09:32:20. 2013-11-03 09:32:20.740 MirrorFailover event has started... I. 11/03 09:32:20. PROPERTY ( 'MachineName' ) = ENVY PROPERTY ( 'Name' ) = partner2_demo PROPERTY ( 'ServerName' ) = partner2_demo PROPERTY ( 'TcpIpAddresses' ) = 192.168.1.100:55502 DB_PROPERTY ( 'Name' ) = demo DB_PROPERTY ( 'ReadOnly' ) = Off DB_PROPERTY ( 'ArbiterState' ) = connected DB_PROPERTY ( 'PartnerState' ) = disconnected DB_PROPERTY ( 'MirrorMode' ) = synchronous DB_PROPERTY ( 'MirrorRole' ) = primary DB_PROPERTY ( 'MirrorServerState' ) = DB_PROPERTY ( 'MirrorServerWaits' ) = 0 DB_PROPERTY ( 'MirrorState' ) = synchronizing DB_PROPERTY ( 'File' ) = C:\temp\server2\demo.db CONNECTION_PROPERTY ( 'Number' ) = 1000000134 CONNECTION_PROPERTY ( 'Name' ) = failover CONNECTION_PROPERTY ( 'CommLink' ) = NA CONNECTION_PROPERTY ( 'CommNetworkLink' ) = NA I. 11/03 09:32:20. 2013-11-03 09:32:20.751 MirrorFailover event is dropping connection 223 ("OLAP Runaway")... I. 11/03 09:32:20. User "DBA" dropped connection 223 ("DBA") I. 11/03 09:32:20. 2013-11-03 09:32:20.758 MirrorFailover event is dropping connection 221 ("OLAP Rogue")... I. 11/03 09:32:20. Connection terminated abnormally I. 11/03 09:32:20. User "DBA" dropped connection 221 ("DBA") I. 11/03 09:32:20. Disconnected TCPIP client's AppInfo: IP=192.168.1.100;HOST=ENVY; OSUSER=Breck;OS='Windows 7 Build 7601 Service Pack 1'; EXE='C:\Program Files\SQL Anywhere 12\bin32\dbisql.exe'; PID=0xf48;THREAD=0x2cf0;VERSION=12.0.1.3298;API=iAnywhereJDBC;TIMEZONEADJUSTMENT=-300 I. 11/03 09:32:20. 2013-11-03 09:32:20.760 MirrorFailover event is dropping connection 220 ("OLAP Read-Only")... I. 11/03 09:32:20. Connection terminated abnormally I. 11/03 09:32:20. User "DBA" dropped connection 220 ("DBA") I. 11/03 09:32:20. Disconnected TCPIP client's AppInfo: IP=192.168.1.100;HOST=ENVY; OSUSER=Breck;OS='Windows 7 Build 7601 Service Pack 1'; EXE='C:\Program Files\SQL Anywhere 12\bin32\dbisql.exe'; PID=0xf48;THREAD=0x3354;VERSION=12.0.1.3298;API=iAnywhereJDBC;TIMEZONEADJUSTMENT=-300 I. 11/03 09:32:20. Connection terminated abnormally I. 11/03 09:32:20. Disconnected TCPIP client's AppInfo: IP=192.168.1.100;HOST=ENVY; OSUSER=Breck;OS='Windows 7 Build 7601 Service Pack 1'; EXE='C:\Program Files\SQL Anywhere 12\bin32\dbisql.exe'; PID=0xf48;THREAD=0x1398;VERSION=12.0.1.3298;API=iAnywhereJDBC;TIMEZONEADJUSTMENT=-300

After the "OLAP Rogue" connection is dropped during the failover process, it cannot reconnect because there is no server named "secondary_demo" running:

Once the secondary (mirror) server is started again, the "OLAP Rogue" ISQL session can connect again:

Everything's OK now; "OLAP Rogue" is connected to the new read-only secondary server where it cannot interfere with the OLTP workload:

---

High Availability plus Read-Only Scale-Out

The Read-Only Scale-Out feature was introduced in SQL Anywhere 12. It's a little bit like using the secondary or mirror database in a High Availability setup for read-only processing, with the following differences:

• You can have more than one Read-Only Scale-Out copy database... in fact, you can have a whole tree structure of copy databases.
  

• A copy database will never become the primary database in a High Availability setup, unlike a secondary database being used for read-only connections,
  

• which means a copy database will never suddenly allow updates, not like a secondary database which suddenly becomes the primary.
  

• You can implement copy databases without creating a High Availability setup; i.e., you can have an updatable database and a read-only copy (or multiple copies) without bothering with failover... but that's not what this article is about:
  

This article shows how to add a single Read-Only Scale-Out copy database to the High Availability setup described in High Availability Demo, Revised.

Start With The High Availability Setup

The following command file, together with the SQL script following it, takes care of creating and starting the entire setup described in High Availability Demo, Revised. The code's repeated here so you can run it all-at-once without going step-by-step through the previous article:

Command file: 01_High_Availability_Demo.bat

ECHO OFF ECHO ------------------------------ ECHO Step 1: Prepare the folders and ECHO the database file in server1. PAUSE ECHO Create the HA subfolders... MD server1 MD server2 MD arbiter ECHO Copy the V12 demo as a working database in server1... PUSHD server1 COPY "C:\Documents and Settings\All Users\Documents\SQL Anywhere 12\Samples\demo.db" "%SQLANY12%\bin32\dblog.exe"^ -t demo.log^ demo.db "%SQLANY12%\bin32\dbspawn.exe"^ -f "%SQLANY12%\bin32\dbeng12.exe"^ -o "dbsrv12_demo_log.txt"^ demo.db "%SQLANY12%\bin32\dbstop.exe"^ -c "ENG=demo; UID=dba; PWD=sql"^ -y^ demo POPD ECHO ------------------------------ ECHO Step 2: Start the partner1_demo ECHO server. PAUSE "%SQLANY12%\bin32\dbspawn.exe"^ -f "%SQLANY12%\bin32\dbsrv12.exe"^ -n partner1_demo^ -o "server1\dbsrv12_partner1_demo_log.txt"^ -su sql^ -x tcpip(port=55501;dobroadcast=no)^ "server1\demo.db"^ -xp on ECHO ------------------------------ ECHO Step 3: Wait until the partner1_demo ECHO server is REALLY up and running. PAUSE :dbping ECHO Waiting until partner1_demo is up and running... "%SQLANY12%\bin32\dbping.exe"^ -c "SERVER=partner1_demo; DBN=demo; UID=dba; PWD=sql; HOST=localhost:55501;"^ -d IF ERRORLEVEL 1 ( GOTO dbping ) ELSE ( GOTO continue ) :continue ECHO ------------------------------ ECHO Step 4: Create the database objects ECHO to support HA. PAUSE "%SQLANY12%\bin32\dbisql.com"^ -c "SERVER=partner1_demo; DBN=demo; UID=dba; PWD=sql; HOST=localhost:55501;"^ READ ENCODING Cp1252 "04s_script_to_create_supporting_objects.sql" ECHO ------------------------------ ECHO Step 5: Create the database file ECHO in server2. PAUSE "%SQLANY12%\bin32\dbbackup.exe"^ -c "SERVER=partner1_demo; DBN=demo; UID=dba; PWD=sql; HOST=localhost:55501;"^ -o "server2\dbbackup_partner1_demo_log.txt"^ server2 ECHO ------------------------------ ECHO Step 6: Start the partner2_demo ECHO server. PAUSE "%SQLANY12%\bin32\dbspawn.exe"^ -f "%SQLANY12%\bin32\dbsrv12.exe"^ -n partner2_demo^ -o "server2\dbsrv12_partner2_demo_log.txt"^ -su sql^ -x tcpip(port=55502;dobroadcast=no)^ "server2\demo.db"^ -xp on ECHO ------------------------------ ECHO Step 7: Start the arbiter server. PAUSE "%SQLANY12%\bin32\dbspawn.exe"^ -f "%SQLANY12%\bin32\dbsrv12.exe"^ -n arbiter_demo^ -o "arbiter\dbsrv12_arbiter_demo_log.txt"^ -su sql^ -x tcpip(port=55500;dobroadcast=no)^ -xf "arbiter\arbiter_demo.state"^ -xa "AUTH=abc;DBN=demo" ECHO ------------------------------ ECHO Step 8: Connect to the primary ECHO and secondary databases. PAUSE PAUSE Wait until the servers are up and running, then "%SQLANY12%\bin32\dbisql.com"^ -c "SERVER=primary_demo; UID=dba; PWD=sql; HOST=localhost:55501,localhost:55502; CON=Updatable" "%SQLANY12%\bin32\dbisql.com"^ -c "SERVER=secondary_demo; UID=dba; PWD=sql; HOST=localhost:55501,localhost:55502; CON=Read-Only" PAUSE All done

SQL script: 04s_script_to_create_supporting_objects.sql

-- Describe the first of two physical mirror partners. -- When this script is first executed, this is the only server actually running. CREATE MIRROR SERVER partner1_demo AS PARTNER connection_string = 'SERVER=partner1_demo; host=localhost:55501' state_file = 'server1/partner1_demo.state'; -- Describe the second of two physical mirror partners. CREATE MIRROR SERVER partner2_demo AS PARTNER connection_string = 'SERVER=partner2_demo; host=localhost:55502' state_file = 'server2/partner2_demo.state'; -- Describe the logical primary server. CREATE MIRROR SERVER primary_demo AS PRIMARY connection_string = 'SERVER=primary_demo; host=localhost:55501,localhost:55502'; -- Describe the logical secondary (mirror) server. CREATE MIRROR SERVER secondary_demo AS MIRROR connection_string = 'SERVER=secondary_demo; host=localhost:55501,localhost:55502'; -- Describe the physical arbiter server. -- The information recorded here is only for use by the partner servers. The arbiter -- server itself doesn't have any physical database associated with it, so all the options -- required by the arbiter server must be specified on the arbiter server command line. CREATE MIRROR SERVER arbiter_demo AS ARBITER connection_string ='SERVER=arbiter_demo; HOST=localhost:55500'; -- Define the authentication string. SET MIRROR OPTION authentication_string = 'abc'; -- Set the HA synchronization mode to synchronous (default), asynchronous or asyncfullpage. SET MIRROR OPTION synchronization_mode = 'synchronous'; -- Request automatic failover when synchronization_mode set to asynchronous or asyncfullpage. SET MIRROR OPTION auto_failover = 'on'; ----------------------------------------------------------------------------------------------- CREATE FUNCTION which_database() RETURNS LONG VARCHAR BEGIN RETURN STRING ( 'PROPERTY ( ''MachineName'' ) = ', PROPERTY ( 'MachineName' ), '\x0d\x0a', 'PROPERTY ( ''Name'' ) = ', PROPERTY ( 'Name' ), '\x0d\x0a', 'PROPERTY ( ''ServerName'' ) = ', PROPERTY ( 'ServerName' ), '\x0d\x0a', 'PROPERTY ( ''TcpIpAddresses'' ) = ', PROPERTY ( 'TcpIpAddresses' ), '\x0d\x0a', 'DB_PROPERTY ( ''Name'' ) = ', DB_PROPERTY ( 'Name' ), '\x0d\x0a', 'DB_PROPERTY ( ''ReadOnly'' ) = ', DB_PROPERTY ( 'ReadOnly' ), '\x0d\x0a', 'DB_PROPERTY ( ''ArbiterState'' ) = ', DB_PROPERTY ( 'ArbiterState' ), '\x0d\x0a', 'DB_PROPERTY ( ''PartnerState'' ) = ', DB_PROPERTY ( 'PartnerState' ), '\x0d\x0a', 'DB_PROPERTY ( ''MirrorState'' ) = ', DB_PROPERTY ( 'MirrorState' ), '\x0d\x0a', 'DB_PROPERTY ( ''File'' ) = ', DB_PROPERTY ( 'File' ), '\x0d\x0a', 'CONNECTION_PROPERTY ( ''Number'' ) = ', CONNECTION_PROPERTY ( 'Number' ), '\x0d\x0a', 'CONNECTION_PROPERTY ( ''Name'' ) = ', CONNECTION_PROPERTY ( 'Name' ), '\x0d\x0a', 'CONNECTION_PROPERTY ( ''CommLink'' ) = ', CONNECTION_PROPERTY ( 'CommLink' ), '\x0d\x0a', 'CONNECTION_PROPERTY ( ''CommNetworkLink'' ) = ', CONNECTION_PROPERTY ( 'CommNetworkLink' ) ); END;

Start The Read-Only Scale-Out Copy Database

Here's the Windows command file for creating, starting and connecting to a new copy database:

REM Create the copy subfolder...

MD copy1

REM Create the supporting objects in the primary database...

"%SQLANY12%\bin32\dbisql.com"^
  -c "SERVER=primary_demo; UID=dba; PWD=sql; HOST=localhost:55501,localhost:55502;"^
  READ ENCODING Cp1252 "10s_script_to_prepare_copy.sql"

REM Copy the primary database into copy1...

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "SERVER=primary_demo; UID=dba; PWD=sql; HOST=localhost:55501,localhost:55502;"^
  -o "copy1\dbbackup_primary_demo_log.txt"^
  copy1

REM Start the database in copy1...

"%SQLANY12%\bin32\dbspawn.exe"^
  -f "%SQLANY12%\bin32\dbsrv12.exe"^
  -n copy1_demo^
  -o "copy1\dbsrv12_copy1_demo_log.txt"^
  -su sql^
  -x tcpip(port=55503;dobroadcast=no)^
  "copy1\demo.db"^
  -xp on

PAUSE Wait until the copy is up and running, then

"%SQLANY12%\bin32\dbisql.com"^
  -c "SERVER=copy1_demo; UID=dba; PWD=sql; HOST=localhost:55503; CON=COPY1_query;" 

The MD command on line 3 above creates a new subfolder for the copy database.

The dbisql command on lines 7 through 9 runs a SQL script to run a CREATE MIRROR SERVER statement on the primary database to tell it (and the secondary database) about the new copy database that's about to be created.

The dbbackup command on lines 13 through 16 creates the copy database file and transaction log in the copy1 subfolder.

The dbsrv12 command on lines 20 through 27 starts the copy database using these options:

-n copy1_demo^ the server name for the copy server -o "copy1\dbsrv12_copy1_demo_log.txt"^ where to write the diagnostic console log text file -su sql^ the DBA password for connecting via DBN=utility_db -x tcpip(port=55503; the port for all connections to the copy database dobroadcast=no)^ to speed up connections between HA servers "copy1\demo.db"^ the first physical database file -xp on enables mirroring and read-only scale-out

The dbisql command on lines 31 and 32 starts an ISQL session connected to the copy database.

Here's the SQL script that is used in the command file above:

CREATE MIRROR SERVER copy1_demo AS COPY
   FROM SERVER primary_demo
   connection_string = 'SERVER=copy1_demo; host=localhost:55503';

-- Disable automatic creation of copy nodes and force usage of 
-- explicit CREATE MIRROR SERVER ... AS COPY statements

SET MIRROR OPTION child_creation = 'off';

The CREATE MIRROR SERVER statement on lines 1 through 3 defines "copy1_demo" as the server name for a physical "COPY" based on the logical "primary_demo" database:

• The server name copy1_demo matches the dbsrv12 -n option that's used to actually start the database,
  

• the keyword COPY identifies this database as a physical COPY database in a read-only scale-out setup, as opposed to a physical PARTNER or a logical MIRROR in an HA setup, and
  

• the FROM SERVER clause associates the copy with the HA setup as a whole (primary_demo) rather than a specific physical partner (partner1_demo or partner2_demo).
  

The SET MIRROR OPTION statement on line 8 disables a sophisticated feature available in a read-only scale-out setup: the ability for the primary database to automatically recognize freshly-created copy databases without the need for explicit CREATE MIRROR SERVER ... AS COPY statements, a feature which is not discussed in this article.

Here's what the three ISQL sessions look like immediately after connecting to the fresh HA plus read-only scale-out setups:



As expected,

• the "Updatable" session is connected to primary_demo which is currently associated with partner1_demo,
  

• the "Read-Only" session is connected to secondary_demo, currently associated with partner2_demo, with DB_PROPERTY ( 'ReadOnly' ) set to 'On', and
  

• the "COPY1_query" session is connected to copy1_demo, with DB_PROPERTY ( 'ReadOnly' ) also set to 'On'.
  

Here's what the three ISQL sessions look like immediately after failover (the partner1_demo database has stopped, and partner2_demo has taken over the role of primary):



After the failover,

• the "Updatable" session has been reconnected to the new primary_demo, which has been "Reconnected to database" partner2_demo,
  

• the "Read-Only" session is still connected to "secondary_demo", still associated with partner2_demo even though that database is now really the primary, and DB_PROPERTY ( 'ReadOnly' ) is now set to 'Off' as warned about in the Help, and
  

• the "COPY1_query" session is still connected to copy1_demo, with DB_PROPERTY ( 'ReadOnly' ) still set to 'On'.
  

As mentioned earlier in this article, that's a significant difference between read-only connection to an HA mirror database and a read-only connection to a read-only scale-out database: the read-only scale-out connection isn't going to suddenly become updatable.

Another difference is that OLAP connections to a read-only scale-out database STAY there, they don't suddenly find themselves sharing the primary database with time-critical OLTP sessions (to be clear, it isn't the OLAP connections to the mirror database that move, it's the OLTP connections that get re-connected to the new primary... but try explaining THAT to your customers :)

---