Beware Drifting Options

One of the most common problems when upgrading a SQL Anywhere database from one version to another is "option drift":

---

Option drift: An unwanted, unnoticed, unexplained (and generally unexplainable) change in the value of some important database option; e.g., when SET OPTION PUBLIC.Optimization_Goal changes from 'All-rows' to 'First-row', or vice versa.

---

It's 11AM. Do you know what your OPTION settings are?

Let's say you have two databases, old and new, and you want to see if any of the OPTION settings have changed.

It's easy to do with a simple stored procedure that uses proxy tables and a FULL OUTER JOIN to compare the contents of the SYSOPTION system view in both databases.

Or maybe you just have one database, and you want to see if any the OPTION settings are different from the defaults: just create a new empty database to use as the "old" database when you call the stored procedure.

Here's how it works: Create (yet another?) empty SQL Anywhere 12 database, store the procedure in it, and call it like this:

"%SQLANY12%\Bin32\dbinit.exe" dddmaster.db

"%SQLANY12%\Bin32\dbspawn.exe" -f "%SQLANY12%\Bin32\dbeng12.exe" dddmaster.db

"%SQLANY12%\Bin32\dbisql.com" -c "ENG=dddmaster;DBN=dddmaster;UID=dba;PWD=sql"

CREATE PROCEDURE compare_sysoptions (
   ...details later...

CALL compare_sysoptions (
   'DRIVER=SQL Anywhere 12;ENG=ddd1;DBN=ddd1',
   'DRIVER=SQL Anywhere 12;ENG=ddd2;DBN=ddd2' );

user_name, option,                              Different,   database1_setting, database2_setting
...
'PUBLIC',  'nearest_century',                   '',          50,                50
'PUBLIC',  'non_keywords',                      '',          ,
'PUBLIC',  'odbc_describe_binary_as_varbinary', '',          Off,               Off
'PUBLIC',  'odbc_distinguish_char_and_varchar', '',          Off,               Off
'PUBLIC',  'oem_string',                        '',          ,
'PUBLIC',  'on_charset_conversion_failure',     '',          Ignore,            Ignore
'PUBLIC',  'on_tsql_error',                     '',          Conditional,       Conditional
'PUBLIC',  'optimization_goal',                 'Different', first-row,         All-rows
'PUBLIC',  'optimization_level',                '',          9,                 9
'PUBLIC',  'optimization_workload',             '',          Mixed,             Mixed
'PUBLIC',  'pinned_cursor_percent_of_cache',    '',          10,                10
...

Simple, yet Funky

Here's the code for the stored procedure:

CREATE PROCEDURE compare_sysoptions (
   IN @database1_ODBC_connection_string   LONG VARCHAR, 
   IN @database2_ODBC_connection_string   LONG VARCHAR )
BEGIN 

---------------------------------------------------------------------
-- Drop proxy objects.

BEGIN
   DROP TABLE database1_SYSOPTION;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP TABLE database1_SYSUSER;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER database1;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP TABLE database2_SYSOPTION;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP TABLE database2_SYSUSER;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER database2;
   EXCEPTION WHEN OTHERS THEN
END;

---------------------------------------------------------------------
-- Create proxy objects.

CREATE SERVER database1 CLASS 'SAODBC' USING '{@database1_ODBC_connection_string}';

CREATE EXISTING TABLE database1_SYSOPTION AT 'database1..SYS.SYSOPTION';

CREATE EXISTING TABLE database1_SYSUSER   AT 'database1..SYS.SYSUSER';

CREATE SERVER database2 CLASS 'SAODBC' USING '{@database2_ODBC_connection_string}';

CREATE EXISTING TABLE database2_SYSOPTION AT 'database2..SYS.SYSOPTION';

CREATE EXISTING TABLE database2_SYSUSER   AT 'database2..SYS.SYSUSER';

---------------------------------------------------------------------
-- Compare options.

SELECT COALESCE ( database1_option.user_name, database2_option.user_name ) AS user_name,
       COALESCE ( database1_option."option",  database2_option."option"  ) AS "option",
       IF database1_setting <> database2_setting
          THEN 'Different'
          ELSE ''
       END IF AS Different,
       COALESCE ( database1_option.setting,   '(NULL)' ) AS database1_setting,
       COALESCE ( database2_option.setting,   '(NULL)' ) AS database2_setting
  FROM ( 
         SELECT database1_SYSUSER.user_name,
                database1_SYSOPTION."option",
                database1_SYSOPTION.setting
           FROM database1_SYSUSER
                   INNER JOIN database1_SYSOPTION
                           ON database1_SYSOPTION.user_id = database1_SYSUSER.user_id
        ) AS database1_option
  FULL OUTER JOIN 
        (
         SELECT database2_SYSUSER.user_name,
                database2_SYSOPTION."option",
                database2_SYSOPTION.setting
           FROM database2_SYSUSER
                   INNER JOIN database2_SYSOPTION
                           ON database2_SYSOPTION.user_id = database2_SYSUSER.user_id
        ) AS database2_option
  ON database1_option.user_name = database2_option.user_name
 AND database1_option."option"  = database2_option."option"
 ORDER BY user_name, "option";

END;

The CREATE SERVER statement at line 42 starts the ball rolling by creating a "remote server" pointing at database 1.

The USING clause on line 42 is where the funkiness comes into play: You can't just code an ordinary variable reference like USING @database1_ODBC_connection_string, you have to wrap the variable name in 'single quotes' and THEN wrap it again in {curly braces} so SQL Anywhere won't think that it's just an ordinary 'string literal'. This is explained somewhere in the Help, but that explanation leads you to believe a CREATE VARIABLE statement is required; not true, all you need is the '{wrappers}' around an '{ordinary_variable_name}'.

The CREATE EXISTING TABLE statements on lines 44 and 46 create two proxy tables: local definitions for tables where the data sits somewhere else. When you SELECT from these tables, you're actually getting the data from the other database.

The SELECT on lines 57 through 84 use the following features:

• The COALESCE() function calls on lines 57 and 58 deals with the fact that one or the other of its arguments can be null, and if so it picks the other one.
  

• The IF expression on lines 59 through 62 displays the 'Different' eyecatcher to show where the old and new option settings are different.
  

• The COALESCE() function calls on lines 63 and 64 deals with the situation where an option setting is completely missing from either the old or new databases, something that is often the case when they use different versions of SQL Anywhere.
  

• The derived tables on lines 65 through 72 and 74 through 81 simply add the user_name 'PUBLIC' from SYSUSER to the data coming from SYSOPTION; otherwise, you'd have to deal with the number 3 in the user_id column.
  

• The ON clause on lines 82 and 83 tells the FULL OUTER JOIN to match rows on user_name and "option" values, with the FULL and OUTER keywords telling SQL Anywhere to keep all the rows (OUTER) from each table (FULL, as opposed to just LEFT or RIGHT) even when the ON clause fails to find a matching row in the other table.
  

---