Wednesday, March 30, 2011

Proxy {Variables}

There's a new feature in SQL Anywhere 12.0.1 that didn't get documented in the Help:

{Variable} references in remote access CREATE SERVER and CREATE EXISTING TABLE statements which allow you to dynamically change the remote server and remote table names without altering or re-executing the CREATE statements
Whew! That's a mouthful, not exactly suitable as a name for this new feature, so let's call it this:
Proxy {Variables}
While it doesn't have its own Help topic yet, it did get mentioned: Go to the Create directory access servers topic in the Help, then scroll down to Example 2 where you will see these examples of proxy {variables} in the CREATE SERVER ... USING and CREATE TABLE ... AT clauses:
CREATE SERVER dir CLASS 'directory' USING 'root={@directory}\\users;subdirs={@subdirs}';

CREATE EXISTING TABLE dbo.userdir AT '{@server};;;{@curuser}';
Here's another example; this code runs on database ddd1 to display row counts on databases ddd2 and ddd3 via proxy tables:
BEGIN
DECLARE other_engine_name VARCHAR ( 128 );
DECLARE other_database_name VARCHAR ( 128 );
DECLARE other_table_name VARCHAR ( 128 );
DECLARE row_count BIGINT;

CREATE SERVER other_server CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere 11;ENG={other_engine_name};DBN={other_database_name}';

SET other_engine_name = 'ddd2';
SET other_database_name = 'ddd2';
SET other_table_name = 'SYSARTICLE';

CREATE EXISTING TABLE proxy_systable
AT 'other_server..SYS.SYSTABLE';

CREATE EXISTING TABLE proxy_table
AT 'other_server..SYS.{other_table_name}';

FOR f_fetch1 AS c_fetch1 INSENSITIVE CURSOR FOR
SELECT proxy_systable.table_name AS proxy_table_name
FROM proxy_systable
WHERE proxy_systable.table_type = 'VIEW'
AND proxy_systable.creator = 0 -- SYS
ORDER BY proxy_systable.table_name
FOR READ ONLY
DO

SET other_table_name = proxy_table_name;

SELECT COUNT(*)
INTO row_count
FROM proxy_table;

MESSAGE STRING ( other_engine_name, ' ', other_table_name, ' ', row_count ) TO CLIENT;

END FOR;

SET other_engine_name = 'ddd3';
SET other_database_name = 'ddd3';

FOR f_fetch2 AS c_fetch2 INSENSITIVE CURSOR FOR
SELECT proxy_systable.table_name AS proxy_table_name
FROM proxy_systable
WHERE proxy_systable.table_type = 'VIEW'
AND proxy_systable.creator = 0 -- SYS
ORDER BY proxy_systable.table_name
FOR READ ONLY
DO

SET other_table_name = proxy_table_name;

SELECT COUNT(*)
INTO row_count
FROM proxy_table;

MESSAGE STRING ( other_engine_name, ' ', other_table_name, ' ', row_count ) TO CLIENT;

END FOR;

END;
  • The CREATE SERVER statement on lines 7 and 8 contains two dynamic {variable} references to the local DECLARE variables defined on lines 2 and 3. Those variables don't have values yet because CREATE SERVER statements aren't actually executed until necessary; e.g., when a CREATE EXISTING TABLE is executed.

  • The CREATE EXISTING TABLE statement on lines 17 and 18 contains another dynamic {variable} reference to the local variable defined on line 4. At this point the local variable must have a value (see line 12) because CREATE EXISTING TABLE is an executable statement. The other two {variables} must also have values at this point (see lines 10 and 11).

  • The FOR loop on lines 20 through 37 displays the row counts for all the system views on the remote database, in this case ddd2. The SET statement on line 29 dynamically changes the {variable} that controls which remote table is actually used to satisfy the FROM clause on line 33.

  • The SET statements on lines 39 and 40 change the remote database to ddd3, which causes the identical FOR loop on lines 42 through 59 to display the row counts for a completely different set of system views.
Here's the output:
ddd2 ST_GEOMETRY_COLUMNS 0
ddd2 ST_SPATIAL_REFERENCE_SYSTEMS 5
ddd2 ST_UNITS_OF_MEASURE 5
...
ddd2 SYSVIEW 128
ddd2 SYSVIEWS 128
ddd2 SYSWEBSERVICE 0
ddd3 ST_GEOMETRY_COLUMNS 0
ddd3 ST_SPATIAL_REFERENCE_SYSTEMS 5
ddd3 ST_UNITS_OF_MEASURE 5
...
ddd3 SYSVIEW 128
ddd3 SYSVIEWS 128
ddd3 SYSWEBSERVICE 0
Tip: This stuff doesn't actually work in the GA build of 12.0.1, you have to get the first EBF 12.0.1.3298 here.

Monday, March 28, 2011

Chrome Rocks!

There are a number of disadvantages to being an early adopter. One of those is becoming so discouraged with all the bugs and glitches in the new product that you stop using it, even when everything's fixed and the new product becomes Best of Breed.

Even when the new product becomes much better than the siht you're using now.

That was me two years ago, with the Google Chrome browser... I tried it... Crash! Kaboom!... and immediately ran back into the arms of Firefox and IE.

Now, Firefox and IE have driven me in the other direction; here's the story, step-by-step...

  • The other day Firefox 3.6 insisted on downloading some new patches, but

  • Norton Internet Security quarantined a critical component in that download, then

  • Firefox (not unexpectedly) refused to start without that component, making it clear that

  • (who am I to argue?) this is Mozilla's way of telling me to upgrade, and when

  • I downloaded Firefox 4 it seemed to work OK, until

  • Firefox 4 started going Crash! Kaboom! every time I touched Gmail, plus

  • the rest of it was cluttered and confusing and just plain ugly, so for some (hitherto unexplained and clearly irrational) reason

  • I decided to try Internet Explorer 9 and that seemed to go OK, except for the fact

  • the new GUI is so completely hosed up that it can't even display the Minimize / Maximize / Close icons properly, plus

  • the rest of it was cluttered and confusing and just plain ugly, leaving me...
...discouraged.

"What have I got to lose? Let's try Chrome again!"


Wow! What a difference two years makes! Not only is Chrome fast, Fast, FAST!
  • it works with Gmail (surprise, surprise),

  • it doesn't mess up the display, and

  • the GUI is clean, Clean, CLEAN!
Plus, it got Today's Cool New Features like
  • dragging a tab to create a separate window,

  • dragging tabs between windows,

  • an "Inspect element" feature that includes an execution profiler...

  • and a convenient Console for in-your-face displays of JavaScript errors...

My experience with Chrome has been so much better than Firefox and IE that I've already made it my default browser, and I'm going to include "Start Foxhound via Chrome" shortcuts in the next release of Foxhound.

What's wrong with Chrome?


Here are the problems I've had so far, with workarounds:
  • There's no window <TITLE> bar: if the page titles don't all fit on the tab bar, you can use a hover-over text box to see each one in turn.

  • It's hard to find where to set options: click on the wrench icon to see a menu; if the option you want isn't in "Options", you might find it in "Tools".

  • It's easy to get the bookmark bar confused with the tab bar: you could turn off the bookmark bar, but it's really handy so just get used to it.

  • Chrome isn't anywhere near as popular as Firefox or IE: that I don't understand, especially for geeks and nerds; here are the browser stats for this blog:

Friday, March 25, 2011

The fRiDaY File

This was first published on March 27, 2006:

12.0.1.3298 EBF Now Available

The first EBF for SQL Anywhere 12.0.1 on Windows 32 and 64-bit is now available.

Note: This EBF has been uploaded to the website twice recently, same build but different file sizes...
March 22 - 237,964,016 bytes
March 24 - 237,963,968 bytes
...to fix a setup issue; the installed software is the same.

Some other new EBFs are also available here:
HP-UX Itanium 11.0.1.2574 EBF

Sun Solaris SPARC 11.0.1.2572 EBF

Sun Solaris x64 11.0.1.2574 EBF

Windows x86/x64 12.0.0.2644 EBF

Wednesday, March 23, 2011

Product Suggestion: Nested Internal Procedures

SQL Anywhere already has nested BEGIN blocks, complete with variable scope rules, local variables, even local EXCEPTION handling. What it needs is an extension to the BEGIN statement to define nested internal PROCEDURE blocks with CALL, RETURN and parameter passing.

A local variable defined at the front of a BEGIN block is created when the block is entered and destroyed when the block terminates. The scope of a local variable includes all the executable statements in the same BEGIN block, as well as all statements in any nested BEGIN block. - page 262, SQL Anywhere Studio 9 Developer's Guide

Before...

Here's an excerpt from a large stored procedure that mimics "calls" to local sections of code by using Y/N variables and IF statements. All the code is local to one single CREATE PROCEDURE because of the large number of local variables (over 200, not shown) that are shared across the different sections; using a separate CREATE PROCEDURE for each section of code would require thousands of lines of additional code to implement parameter passing.

CREATE PROCEDURE rroad_monitor_options (
...
BEGIN
DECLARE @do_task_load_criteria_into_@variables VARCHAR ( 1 );
DECLARE @do_task_copy_inputable_@variables_into_@old_variables VARCHAR ( 1 );
DECLARE @do_task_load_factory_settings_into_inputable_@variables VARCHAR ( 1 );
DECLARE @do_task_cancel_alerts_as_required VARCHAR ( 1 );
DECLARE @do_task_replace_entire_target_row_with_@variables VARCHAR ( 1 );
...
SET @do_task_load_criteria_into_@variables = 'N';
SET @do_task_copy_inputable_@variables_into_@old_variables = 'N';
SET @do_task_load_factory_settings_into_inputable_@variables = 'N';
SET @do_task_cancel_alerts_as_required = 'N';
SET @do_task_replace_entire_target_row_with_@variables = 'N';
...
WHEN @submit_form = 'Show Criteria' THEN
SET @do_task_load_criteria_into_@variables = 'Y';
SET @do_task_copy_inputable_@variables_into_@old_variables = 'Y';

WHEN @submit_form = 'Switch Display' THEN
SET @do_task_load_criteria_into_@variables = 'Y';
SET @do_task_copy_inputable_@variables_into_@old_variables = 'Y';

WHEN @submit_form = 'Restore Factory Settings' THEN
SET @do_task_load_criteria_into_@variables = 'Y';
SET @do_task_copy_inputable_@variables_into_@old_variables = 'Y';
SET @do_task_load_factory_settings_into_inputable_@variables = 'Y';
SET @do_task_cancel_alerts_as_required = 'Y';
SET @do_task_replace_entire_target_row_with_@variables = 'Y';
...

IF @do_task_load_criteria_into_@variables = 'Y' THEN
...
END IF;

IF @do_task_copy_inputable_@variables_into_@old_variables = 'Y' THEN
...
END IF;

IF @do_task_load_factory_settings_into_inputable_@variables = 'Y' THEN
...
END IF;

IF @do_task_cancel_alerts_as_required = 'Y' THEN
...
END IF;

IF @do_task_replace_entire_target_row_with_@variables = 'Y' THEN
...
END IF;
...

...and After

Here's what that excerpt might look like with the crude SET and IF statements changed to CALL and INTERNAL PROCEDURE statements:

CREATE PROCEDURE rroad_monitor_options (
...
BEGIN
...
WHEN @submit_form = 'Show Criteria' THEN
CALL load_criteria_into_@variables();
CALL copy_inputable_@variables_into_@old_variables();

WHEN @submit_form = 'Switch Display' THEN
CALL load_criteria_into_@variables();
CALL copy_inputable_@variables_into_@old_variables();

WHEN @submit_form = 'Restore Factory Settings' THEN
CALL load_criteria_into_@variables();
CALL copy_inputable_@variables_into_@old_variables();
CALL load_factory_settings_into_inputable_@variables();
CALL cancel_alerts_as_required();
CALL replace_entire_target_row_with_@variables();
...

INTERNAL PROCEDURE load_criteria_into_@variables()
BEGIN
...
END;

INTERNAL PROCEDURE copy_inputable_@variables_into_@old_variables()
BEGIN
...
END;

INTERNAL PROCEDURE load_factory_settings_into_inputable_@variables()
BEGIN
...
END;

INTERNAL PROCEDURE cancel_alerts_as_required()
BEGIN
...
END;

INTERNAL PROCEDURE replace_entire_target_row_with_@variables()
BEGIN
...
END;
...

Since the main point behind an internal procedure is to take advantage of local variable scope rules (inheritance), I could live without parameter passing altogether... but here's what the full BNF might look like:
<internal_procedure>        ::= INTERNAL PROCEDURE <procedure_name>
"(" [ <parameter_list> ] ")"
[ RESULT "(" <result_set_column_list> ")" ]
<begin_block>

<parameter_list> ::= <parameter_specification> { "," <parameter_specification> }

<parameter_specification> ::= [ <parameter_disposition> ]
<parameter_name>
<data_type>
[ <parameter_default> ]

<parameter_disposition> ::= IN
| OUT
| INOUT -- default

<parameter_default> ::= DEFAULT <expression>

<result_set_column_list> ::= <result_set_column> { "," <result_set_column> }

<result_set_column> ::= <identifier> <data_type>

Monday, March 21, 2011

In Praise Of Recursion

I'd like to say the code posted last week solved the problem it was intended to solve, but alas, that was not the case. The job of resetting the Alerts criteria for a target database is a lot more complex than replacing a row in a table; it includes

  • checking to see if each setting has actually changed for each different alert (all 29 of them),

  • and if it has,

  • and if that alert is currently in effect,

  • then that alert must be cancelled,

  • and that in turn might require an "All Clear" email to be sent

  • as well as rows to be inserted in other tables.
None of that was accomplished by the cute little FOR INSERT SELECT * loop posted earlier.

The good news is, all the code to perform those tasks for a single target database already existed inside the stored procedure being modified... all it needed was

A DIFFERENT Cute Little FOR Loop


Here's the actual solution; a lot of code's been removed for clarity (about 6500 lines worth) but nothing else has been changed:

CREATE PROCEDURE rroad_monitor_options (
IN @submit_form VARCHAR ( 100 ),
IN @sampling_id UNSIGNED INTEGER,
IN @display_html_page VARCHAR ( 1 ) )
RESULT ( html_output LONG VARCHAR )
BEGIN

...

IF @sampling_id = 0 THEN

CASE @submit_form

...

WHEN 'Force Default Settings On All Targets' THEN

-- Perform 'Restore Default Settings' tasks on each target row.

FOR f_alerts_criteria AS c_alerts_criteria INSENSITIVE CURSOR FOR
SELECT rroad_alerts_criteria.sampling_id AS @target_sampling_id
FROM rroad_alerts_criteria
WHERE rroad_alerts_criteria.criteria_set_type = 'Target Specific'
ORDER BY rroad_alerts_criteria.sampling_id
FOR READ ONLY
DO

-- Recursively call this procedure.

CALL rroad_monitor_options (
'Restore Default Settings',
@target_sampling_id,
'N' ); -- @display_html_page

END FOR;

...

END CASE;

ELSE -- @sampling_id > 0

CASE @submit_form

...

WHEN 'Restore Default Settings' THEN

-- existing code to reset the alerts criteria for a single target database

...
  • The CREATE PROCEDURE on lines 1 through 5 is normally called from an HTML service. The first parameter @submit_form tells the procedure what button was pressed; in this case, the existing 'Restore Default Settings' button versus the new 'Force Default Settings On All Targets'.

  • The second parameter @sampling_id is 0 for the row containing default values, and 1, 2, 3 ... for rows representing actual target databases being monitored by Foxhound.

  • The third parameter @display_html_page is 'Y' when the procedure is called from the HTML service and 'N' when it is being called elsewhere.

  • The WHEN on lines 16 through 35 handles the 'Force Default Settings On All Targets' button press. The FOR loop steps through each target database with each primary key being placed in @target_sampling_id.

  • The recursive CALL on lines 30 through 33 is the heart of this solution. The three parameter values are set up to pretend the user has pressed the 'Restore Default Settings' on a single target database, with @display_html_page set to 'N' because nothing is being sent back to the browser.

  • The WHEN starting on line 47 marks the begining of the existing code which handles a 'Restore Default Settings' button press. This is the code invoked by the earlier recursive CALL.
To tell the truth, I can't remember the last time I coded a recursive call in the real world, if I ever have... and it probably won't happen again any time soon.

Which is too bad, because when the technique works, it works really well. And SQL Anywhere fully supports recursion and all the stacking and unstacking it requires, no fuss, no muss.

Question: What about performance?

Answer: 100 targets in less than two seconds, so nobody's complaining.

Friday, March 18, 2011

The fRiDaY File

This was first published on March 26, 2006:

New 12.0.1 and 11.0.1 Releases

These new SQL Anywhere releases were posted on March 15:

12.0.1 Upgrade for IBM AIX, Mac OS, Sun Solaris SPARC

11.0.1.2569 EBF for Windows x86, x64

Wednesday, March 16, 2011

In Praise Of SELECT *

Foxhound has several examples of "The Table From Hell". Some of them are performance nightmares with millions of rows and gigabytes of data being inserted and deleted on a daily basis.

Others are relational nightmares with kludgy designs that seemed like a good idea at the time but have evolved to become perpetual PITAs.

The "alerts criteria" table is an example of the latter, with over 200 columns when there should be about 20. Here's an excerpt:


CREATE TABLE rroad_alerts_criteria (
criteria_set_type VARCHAR ( 100 ) NOT NULL CHECK ( @criteria_set_type IN (
'Factory Settings',
'Saved Defaults',
'Target Specific' ) ),
sampling_id UNSIGNED INTEGER NOT NULL, -- sampling_id = 0 for 'Factory Settings'
-- sampling_id = 0 for 'Saved Defaults'
-- sampling_id > 0 for 'Target Specific'
send_alert_emails VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
mail_recipient LONG VARCHAR NOT NULL,
use_html_in_emails VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
email_url_host_port LONG VARCHAR NOT NULL,
display_alerts VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
previous_sample_set_number_processed UNSIGNED BIGINT NOT NULL,
previous_gathered_sample_finished_at TIMESTAMP NOT NULL,
current_sample_finished_at TIMESTAMP NOT NULL,

-- Alert #1. Foxhound has been unable to gather samples for [a001p1] or longer.

alert1_enabled VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
alert1_is_in_effect VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
alert1_in_effect_at TIMESTAMP NOT NULL,
alert1_all_clear_at TIMESTAMP NOT NULL,
alert1_threshold_lost_sample_accumulated_msec BIGINT NOT NULL CHECK ( @column >= 10000 ), -- a001p1
alert1_actual_lost_sample_accumulated_msec BIGINT NOT NULL,

-- Alert #2. The heartbeat time has been 1s or longer for 10 or more recent samples.

alert2_enabled VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
alert2_is_in_effect VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
alert2_in_effect_at TIMESTAMP NOT NULL,
alert2_all_clear_at TIMESTAMP NOT NULL,
alert2_threshold_canarian_msec BIGINT NOT NULL, -- a002p1
alert2_threshold_canarian_msec_sample_count BIGINT NOT NULL CHECK ( @column >= 1 ), -- a002p2
alert2_actual_canarian_msec_threshold_reached_sample_counter BIGINT NOT NULL,

...

-- Alert #29. There have been [1] or more cache panics per second for [10] or more recent samples.

alert29_enabled VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
alert29_is_in_effect VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
alert29_in_effect_at TIMESTAMP NOT NULL,
alert29_all_clear_at TIMESTAMP NOT NULL,
alert29_threshold_cache_panics BIGINT NOT NULL CHECK ( @column >= 1 ), -- a029p1
alert29_threshold_cache_panics_sample_count BIGINT NOT NULL CHECK ( @column >= 1 ), -- a029p2
alert29_actual_cache_panics_threshold_reached_sample_counter BIGINT NOT NULL,

PRIMARY KEY ( criteria_set_type, sampling_id ) );

With a table like that, any day you can code SELECT * instead of a SELECT-endless-list is a good day, and that's what I was hoping for when faced with this maintenance chore:
  • Add a button "Force Default Settings On All Targets" to do the following:

    • Retrieve the "default settings" row with primary key [criteria_set_type; sampling_id] = ['Saved Defaults'; 0].

    • Use that row to update all the rows with primary key [criteria_set_type; sampling_id] = ['Target Specific'; 1, 2, 3, ...].

One brute-force solution might be to SELECT FROM the 'Saved Defaults' row into two hundred local variables, then loop through an UPDATE with two hundred SETs.

A slightly-less-brutish solution might be to move the FROM clause into the UPDATE; this avoids the local variables, but still has the two hundred SETs.

But wait, there's a better way...

Go Brute-Free With SELECT *



SELECT *
INTO LOCAL TEMPORARY TABLE temp_alerts_criteria
FROM rroad_alerts_criteria
WHERE rroad_alerts_criteria.criteria_set_type = 'Saved Defaults'
AND rroad_alerts_criteria.sampling_id = 0;

FOR f_alerts_criteria AS c_alerts_criteria INSENSITIVE CURSOR FOR
SELECT rroad_alerts_criteria.sampling_id AS @target_sampling_id
FROM rroad_alerts_criteria
WHERE rroad_alerts_criteria.criteria_set_type = 'Target Specific'
ORDER BY rroad_alerts_criteria.sampling_id
FOR READ ONLY
DO

-- Set the temporary table primary key to match the 'Target Specific' row.

UPDATE temp_alerts_criteria
SET temp_alerts_criteria.criteria_set_type = 'Target Specific',
temp_alerts_criteria.sampling_id = @target_sampling_id;

-- Copy all the temporary table columns into the 'Target Specific' row.

INSERT rroad_alerts_criteria
ON EXISTING UPDATE
SELECT *
FROM temp_alerts_criteria;

END FOR;

Here's how it works:
  • The SELECT on lines 1 through 5 creates a single-row temporary table holding the 'Saved Defaults' row. This table has exactly the same columns as the permanent rroad_alerts criteria table, and except for the primary key values, the single row holds exactly the same data as each and every 'Target Specific' row is going to hold in the end.

  • The FOR statement on lines 7 through 13 retrieves the sampling_id column for each 'Target Specific' row.

  • The UPDATE on lines 17 through 19 changes the temporary table primary key to match the current 'Target Specific' row.

  • The INSERT ON EXISTING UPDATE statement on lines 23 through 26 replaces the 'Target Specific' with the data from the 'Saved Defaults' row.
The two uses of SELECT * don't just save initial coding effort, they make this block of code immune to most schema changes involving the rroad_alerts_criteria table, like adding or removing data columns.

Of course, none of this would be possible without SQL Anywhere's wonderful INTO LOCAL TEMPORARY TABLE and ON EXISTING UPDATE clauses.

Monday, March 14, 2011

SQLAnywhere-Forum.Sybase.com

The new SQL Anywhere question and answer website is now up and running, for real, no more beta...

SQLAnywhere-Forum.Sybase.com

Friday, March 11, 2011

The fRiDaY File

This was first published on March 25, 2006:

Great Moments in History: Fun With NULLs

One of the reasons lots of people (like me) don't like SQL NULLs is because so many people (like me) have trouble with them.

Some folks, however, have more trouble than others. Like the original creators of Oracle who confused NULL with the empty string.

Well, why else would Oracle automatically convert '' to NULL?

Not just convert it, but define it as being the same as NULL. In other words, in Oracle the predicate '' IS NULL is true:



Oracle's not the only one. Sun's JDBC-to-ODBC bridge was unable to distinguish an SQL NULL string from empty string...



Not a bug

It must be a feature because...
This is not a bug. Some databases consider an empty string to be synonymous with NULL.
Never mind what ANSI says, what does ANSI know?
Misunderstanding of how Null works is the cause of a great number of errors in SQL code, both in ISO standard SQL statements and in the specific SQL dialects supported by real-world database management systems. These mistakes are usually the result of confusion between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as ''). Null is defined by the ISO SQL standard as different from both an empty string and the numerical value 0, however. While Null indicates the absence of any value, the empty string and numerical zero both represent actual values.
Maybe it's a good thing Oracle bought Sun, gather all the stupids bugs in one place so they're easier to avoid.

Wednesday, March 9, 2011

Celebrating LIST() and STRING()

Here's a situation that pops up from time to time when working with MobiLink: The consolidated and remote databases are ready to go and all the MobiLink scripts have been coded, and it's time to start testing... but there's no data in any of the tables.

What's the quickest way to insert a single row into each table just to get started running synch tests?

To be more specific, how can you execute an INSERT statement that looks like this template, for each and every table in the database, so every column has at least one value?


INSERT owner.table VALUES (
value -- column description
, value -- column description
, value -- column description
);

Here's a SQL script that will do just that, using the following features of SQL Anywhere:
  • the LIST() aggregate function is called twice, once to build each INSERT statement, and again to build each VALUES entry within an INSERT,

  • the STRING() function is also used twice, to convert expressions into strings and concatenate them into longer strings, once for each INSERT and once for each VALUES entry,

  • the EXECUTE IMMEDIATE statement is used to apply all the inserts to the database, and

  • the WAIT_FOR_COMMIT option is used so it doesn't matter what order the inserts are executed in (referential integrity isn't checked until after all the inserts are finished).
If you're like me, aggregate functions (especially those OLAP WINDOW things) are mysterious to behold... but LIST() is worth the effort to understand because it's so flexible and powerful:

BEGIN
DECLARE @insert_statements LONG VARCHAR;

SELECT LIST (
STRING (
'INSERT ', USER_NAME ( SYSTABLE.creator ), '.', SYSTABLE.table_name, ' VALUES ( \x0D\x0A',
' ',
( SELECT LIST (
STRING (
CASE SYSDOMAIN.domain_name
WHEN 'bigint' THEN '99 -- '
WHEN 'binary' THEN '99 -- '
WHEN 'bit' THEN '1 -- '
WHEN 'char' THEN '''x'' -- '
WHEN 'date' THEN 'CURRENT DATE -- '
WHEN 'decimal' THEN '99 -- '
WHEN 'double' THEN '99 -- '
WHEN 'float' THEN '99 -- '
WHEN 'integer' THEN '99 -- '
WHEN 'long binary' THEN '''x'' -- '
WHEN 'long nvarchar' THEN '''x'' -- '
WHEN 'long varbit' THEN '1 -- '
WHEN 'long varchar' THEN '''x'' -- '
WHEN 'nchar' THEN '''x'' -- '
WHEN 'numeric' THEN '99 -- '
WHEN 'nvarchar' THEN '''x'' -- '
WHEN 'smallint' THEN '99 -- '
WHEN 'st_geometry' THEN 'NULL -- '
WHEN 'time' THEN 'CURRENT TIME -- '
WHEN 'timestamp' THEN 'CURRENT TIMESTAMP -- '
WHEN 'timestamp with time zone' THEN 'CURRENT UTC TIMSTAMP -- '
WHEN 'tinyint' THEN '99 -- '
WHEN 'uniqueidentifier' THEN 'NEWID() -- '
WHEN 'unsigned bigint' THEN '99 -- '
WHEN 'unsigned int' THEN '99 -- '
WHEN 'unsigned smallint' THEN '99 -- '
WHEN 'varbinary' THEN '''x'' -- '
WHEN 'varbit' THEN '1 -- '
WHEN 'varchar' THEN '''x'' -- '
WHEN 'xml' THEN ''''' -- '
END CASE,
SYSCOLUMN.column_name,
' ',
SYSDOMAIN.domain_name,
CASE SYSDOMAIN.domain_name
WHEN 'bigint' THEN ''
WHEN 'binary' THEN ''
WHEN 'bit' THEN ''
WHEN 'char' THEN STRING ( '( ', SYSCOLUMN.width, ')' )
WHEN 'date' THEN ''
WHEN 'decimal' THEN STRING ( '( ', SYSCOLUMN.width, ', ',
SYSCOLUMN.scale, ')' )
WHEN 'double' THEN ''
WHEN 'float' THEN ''
WHEN 'integer' THEN ''
WHEN 'long binary' THEN ''
WHEN 'long nvarchar' THEN ''
WHEN 'long varbit' THEN ''
WHEN 'long varchar' THEN ''
WHEN 'nchar' THEN STRING ( '( ', SYSCOLUMN.width, ')' )
WHEN 'numeric' THEN STRING ( '( ', SYSCOLUMN.width, ', ',
SYSCOLUMN.scale, ')' )
WHEN 'nvarchar' THEN STRING ( '( ', SYSCOLUMN.width, ')' )
WHEN 'smallint' THEN ''
WHEN 'st_geometry' THEN ''
WHEN 'time' THEN ''
WHEN 'timestamp' THEN ''
WHEN 'timestamp with time zone' THEN ''
WHEN 'tinyint' THEN ''
WHEN 'uniqueidentifier' THEN ''
WHEN 'unsigned bigint' THEN ''
WHEN 'unsigned int' THEN ''
WHEN 'unsigned smallint' THEN ''
WHEN 'varbinary' THEN STRING ( '( ', SYSCOLUMN.width, ')' )
WHEN 'varbit' THEN STRING ( '( ', SYSCOLUMN.width, ')' )
WHEN 'varchar' THEN STRING ( '( ', SYSCOLUMN.width, ')' )
WHEN 'xml' THEN ''
END CASE ),
'\x0D\x0A , '
ORDER BY SYSCOLUMN.column_id )
FROM SYSCOLUMN
INNER JOIN SYSDOMAIN
ON SYSDOMAIN.domain_id = SYSCOLUMN.domain_id
WHERE SYSCOLUMN.table_id = SYSTABLE.table_id ),
'\x0D\x0A); ' ),
'\x0D\x0A\x0D\x0A'
ORDER BY SYSTABLE.table_name )
INTO @insert_statements
FROM SYSTABLE
WHERE USER_NAME ( SYSTABLE.creator ) = 'GROUPO'
AND SYSTABLE.table_type = 'BASE';

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';

EXECUTE IMMEDIATE @insert_statements;

COMMIT;

END;
  • The outer SELECT statement on lines 4 through 91 retrieves each base table belonging to GROUPO in the SQL Anywhere 12 demo database, using data from the SYSTABLE catalog view.

  • The outer LIST() function call on lines 4 through 87 gathers up all the SYSTABLE rows into a single string, and that string is placed in a variable on line 88.

  • The STRING() function call on lines 5 through 85 builds the first parameter passed to the outer LIST() function: the generated INSERT.

  • The two carriage return - line feed pairs on line 86 form the second parameter for the outer LIST() function: the separator between each INSERT in the list.

  • The ORDER BY clause on line 87 instructs the outer LIST() to sort the generated INSERT statements in table name order.

  • The inner SELECT on lines 8 through 84 retrieves each column for the table in the outer SELECT, using data from the SYSCOLUMN and SYSDOMAIN catalog views.

  • The inner LIST() function call on lines 8 through 80 gather all the SYSCOLUMN rows into a single string.

  • The STRING() function call on lines 9 through 78 is the first parameter passed to the inner LIST(), the separator is specified on line 79 and the list's ORDER BY clause is on line 80.

  • The CASE expression on lines 9 through 41 determines what literal to use for this column in the VALUES list.

  • The expressions on lines 42 through 78 build a --comment that's very handy if the output has to be customized.
Here's what the first few generated INSERT statements look like in the string:

INSERT GROUPO.Contacts VALUES (
99 -- ID integer
, 'x' -- Surname char( 20)
, 'x' -- GivenName char( 20)
, 'x' -- Title varchar( 34)
, 'x' -- Street char( 30)
, 'x' -- City char( 20)
, 'x' -- State char( 16)
, 'x' -- Country char( 16)
, 'x' -- PostalCode char( 10)
, 'x' -- Phone char( 13)
, 'x' -- Fax char( 13)
, 99 -- CustomerID integer
);

INSERT GROUPO.Customers VALUES (
99 -- ID integer
, 'x' -- Surname char( 20)
, 'x' -- GivenName char( 20)
, 'x' -- Street char( 30)
, 'x' -- City char( 20)
, 'x' -- State char( 16)
, 'x' -- Country char( 16)
, 'x' -- PostalCode char( 10)
, 'x' -- Phone char( 13)
, 'x' -- CompanyName char( 32)
);

INSERT GROUPO.Departments VALUES (
99 -- DepartmentID integer
, 'x' -- DepartmentName char( 40)
, 99 -- DepartmentHeadID integer
);

This method doesn't just work for the demo database, it works for a lot of real-world databases... but not all.

When it doesn't work perfectly, however, it's a good start: Change the code to save the generated inserts in a file instead of executing them directly, and then edit the file to fix whatever's wrong.

Monday, March 7, 2011

A Return To The True Path

If you're trying to explain the Hallmarks of SQL Anywhere but you're pressed for time, here's what you can say instead:

"Watcom does things the way they should be done."
Nowhere in SQL Anywhere does that statement hold true more often than in the choice of default values for various options and settings. For example,
  • string comparisons are case insensitive by default,

  • the database page size is 4K by default,

  • a transaction log is created by default, and

  • string comparisons include trailing blanks by default (in spite of what ANSI says)
and that's just the dbinit utility; there are literally hundreds of other intelligent defaults throughout SQL Anywhere. If all you know is SQL Anywhere that might not seem like such a big thing, but if you're familiar with, say, Oracle or DB2 you know all about dumb defaults.

The fact that dumb default values are so rare in SQL Anywhere is the reason that it's Big News when a dumb default gets fixed. Here's the latest...

Database Checksums Are Now The Default!

When database page checksum validation was first introduced in SQL Anywhere Version 10, the default was "off". That means if you were creating a new database, and you hadn't heard about this new feature, you probably didn't specify dbinit -s and as a consequence your database pages were created without checksums.

And as a consequence of that you didn't receive early notification of database file corruption.

Eventually, all dumb defaults get fixed. That was the case with 4K page sizes (yes, the default was once 2K) and now, with Version 12, dbinit -s is the default which means everyone gets checksums by default.

Friday, March 4, 2011

The fRiDaY File

...How LinkedIn Helps The Downtrodden:



Assertion Relief

The way SQL Anywhere handles assertions is in direct conflict with the goal of providing a self-managing database (see Automatic Restart).

Further evidence of this conflict may be found in the facts that this feature request has received 8 positive votes plus one positive comment in the past year, but no replies:


Recover from single-row assertion errors

Sometimes the server raises an assertion error for a problem with a single row. Here is a really obscure example, but there are other more common ones:
   *** ERROR *** Assertion failed: 200610 (11.0.1.2276)

Attempting to normalize a non-continued row (0x12345:0x0)

When this happens it is very difficult if not impossible to rescue the database. In this case any attempt to SELECT that row causes the server to stop, and because there is no indication what row is involved, and because there are millions of rows in that table, the database had to be restored from a backup.

Here's an analogy: A airliner is halfway across the Pacific when the light in one of the lavatories burns out (the assertion); this in turn causes all the engines to shut down and the plane to crash in the middle of the ocean.

In the real world, one of the flight attendants puts an "out of order" sign on the lavatory and makes a note for the aircraft maintenance crew to "replace bulb".

In the SQL Anywhere world, the server should throw an exception to the affected client, log the problem for later attention... AND CARRY ON!

...no need to spread baggage and body parts over miles of ocean!
Comment...

I think this one deserves a stir-up. The whole business of assertions needs some examination. If we are going down the self-managing database route and (let's say) an index gets corrupted, isn't there an argument that the engine should just get on and fix it. Certainly flash some red lights and let people know, but do the fix anyway. After all, what's the dba going to do - drop and recreate the index, and then keep an eye on things. Obviously harder / not possible if it's real data corruption.


Analogies Prove Nothing

A good analogy can be used to explain an important point or position, but even the best analogy breaks down when closely examined. The airliner analogy is no exception; here's a problem report that indicates the trouble with assertions is worse than the analogy indicates: a single assertion can bring a whole fleet of aircraft crashing down:

"We have an SQLAnywhere infrastructure setup where we have one database service that runs multiple database instances. This has been working well for quite some time..."

"However, yesterday we experienced an "Assertion Failed" error that took down the whole database service, but we could not find which database had caused this problem. Our troubleshooting process eventually led us to having to split each database into its own separate service which then got our customers up and running in a stable way and when the crash happened again we were able to pinpoint the single database with the problem."
This response indicates that some relief is on the way... if crashing only one aircraft instead of the whole fleet can be called relief:

"...we plan to make changes to allow corrupted databases to be stopped without bringing down the server. Printing database information to related assertion failures should be part of that."

Good news

News that some action, any action on assertions is being taken is good news in itself. It sure beats the old excuse that assertions are necessary to protect data integrity.

More, however, is necessary... much more.

Wednesday, March 2, 2011

Oops, sorry!

It's been over a year since the last "Database Deadpool" article was posted here: We Don't Need No Steenking Backup!

Maybe the situation's getting better, or maybe not... not when it's Google:

Google Gmail Snafu Lesson: Backup, Backup, Backup

Gmail is hard at work restoring service to about 40,000 Gmail users after a software bug deleted their e-mail messages, folders, labels and filters.

...

However, it's not just Gmail and other Webmail services that are the problem; we're increasingly using cloud-based tools for work and communication such as Twitter, Facebook, Google Docs, Microsoft Office Live, Tumblr, Wordpress, Blogger, Posterous, Flickr, Picasa, and on and on.

But that doesn't mean you should forego a solid back-up plan for all your online data. If the worst ever does happen, and a free Web service dumps your stuff permanently, the only response you can reasonably expect from these companies is: "oops, sorry."