Monday, January 31, 2011

Avoiding Quotes

Several SQL Anywhere procedures and functions take SQL statements as string arguments; for example,

  • sa_describe_query() returns information about all the columns in a result set,

  • rewrite() shows how the query optimizer is going to manipulate your query, and

  • graphical_plan() returns the execution plan used by the query engine.
If you have a long and complex SQL statement in your code, it can be a challenge to re-code it as a 'quoted string literal' in order to pass it to one of these functions... every embedded single quote must be doubled-up, and quotes that are already embedded inside string literals (and already doubled-up) must be quadrupled.

Here's an example:

SELECT STRING (
'<HTML><HEAD><TITLE>',
'All the demo database''s sales identifiers',
'</TITLE></HEAD><TABLE>',
LIST (
STRING (
'<TR><TD>', SalesOrders.ID,
'</TD><TD>', SalesOrderItems.LineID,
'</TD><TD>', Products.ID,
'</TD></TD>' ),
''
ORDER BY SalesOrders.ID,
SalesOrderItems.LineID,
Products.ID ),
'</TABLE></HTML>' )
FROM SalesOrderItems
INNER JOIN SalesOrders
ON SalesOrders.ID = SalesOrderItems.ID
INNER JOIN Products
ON Products.ID = SalesOrderItems.ProductID

Here's how it has to be coded if you're going to pass the SELECT as a string literal to the rewrite() function:

SELECT rewrite ( '
SELECT STRING (
''<HTML><HEAD><TITLE>'',
''All the demo database''''s sales identifiers'',
''</TITLE></HEAD><TABLE>'',
LIST (
STRING (
''<TR><TD>'', SalesOrders.ID,
''</TD><TD>'', SalesOrderItems.LineID,
''</TD><TD>'', Products.ID,
''</TD></TD>'' ),
''''
ORDER BY SalesOrders.ID,
SalesOrderItems.LineID,
Products.ID ),
''</TABLE></HTML>'' )
FROM SalesOrderItems
INNER JOIN SalesOrders
ON SalesOrders.ID = SalesOrderItems.ID
INNER JOIN Products
ON Products.ID = SalesOrderItems.ProductID
' )

But... there's an easier way: Copy the SELECT as-is (no doubling-up of the quotes required) to a separate text file, and then code xp_read_file() as the argument to rewrite().

Here, the SELECT has been copied to a text file called select.txt:

SELECT rewrite ( xp_read_file ( 'select.txt' ) )

By the way, here's what rewrite() had to say about the SELECT; no need for any joins at all since the SalesOrderItems table has all the columns that are needed:

select
STRING(
'<HTML><HEAD><TITLE>',
'All the demo database''s sales identifiers',
'</TITLE></HEAD><TABLE>',
LIST(
STRING(
'<TR><TD>',SalesOrderItems_1.ID,
'</TD><TD>',SalesOrderItems_1.LineID,
'</TD><TD>',SalesOrderItems_1.ProductID,
'</TD></TD>'),
'' order by
SalesOrderItems_1.ID asc,
SalesOrderItems_1.LineID asc,
SalesOrderItems_1.ProductID asc),
'</TABLE></HTML>')
from SalesOrderItems as SalesOrderItems_1

Saturday, January 29, 2011

The fRiDaY File

This was first published on March 22, 2006:

Friday, January 28, 2011

It Seems Like Only Yesterday

It seems like only yesterday when the Top 10 Cool New Features in SQL Anywhere 10 was published.

But it wasn't quite "yesterday", it was wayyyy back in 2006, and since then we've had the Top 10 Cool New lists for Version 11 (2008) and Version 12 (2010).

So it shouldn't come as any surprise...

but nevertheless it does


...to see the official End of Life Notice for SQL Anywhere Version 10.x effective on January 25, 2012.

What does that mean?


First, it means no more Version 10 EBFs after January 25, 2012.

Welllll, that's the official story. The real story might be somewhat different. Take the End of Life Notice for SQL Anywhere Version 9.0.x for example; it said the "End of Engineering Support" was coming on January 31, 2010.

And it did... sort of... on September 22, 2010 (almost eight months after End of Life) an EBF for 9.0.2 was released:
SQL Anywhere - Express Bug Fix, Update of 9.0.2 to build 3951 - 9.0.2 - 22 Sep 2010 - EBF/Patch

But no, you can't expect any EBFs after the End of Life date. What you probably can expect is "One Last EBF" right before End of Life, like the one released less than three weeks before the End of Life deadline for 9.0.2:
SQL Anywhere Studio - Express Bug Fix, Update of 9.0.2 to build 3924 (all components) - 9.0.2 - 07 Jan 2010 - EBF/Patch

Life After Death


The "End of Engineering Support" does not mean the "End of Technical Support"... it doesn't even mean the "End Of Technical Support by Engineering".

You will still be able to get your Version 10 questions answered after January 25, 2012... just like you can still get your Version 5.5 questions answered today.

It just means if you step on a bug in Version 9.0.2 after that date, you probably won't get a fix. A workaround, maybe, but not a fix.

Wednesday, January 26, 2011

Did you know?

Did you know that SQL Anywhere has a DESCRIBE TABLE statement?


CREATE TABLE t (
pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
data1 VARCHAR ( 100 ) NOT NULL DEFAULT '',
data2 DECIMAL ( 11, 2 ) NOT NULL DEFAULT 0.0,
data3 LONG BINARY NOT NULL DEFAULT '' );

DESCRIBE TABLE t;

Column Type Nullable Primary Key
pkey integer 0 1
data1 varchar(100) 0 0
data2 decimal(11,2) 0 0
data3 long binary 0 0

Did you know that it's been there since Version 10?

How about this?


Did you know that SQL Anywhere has an sa_describe_query() procedure that returns all sorts of information about each and every column in a SELECT?

Did you know that sa_describe_query() has also been there since Version 10?

Did you also know that sa_describe_query() can do everything DESCRIBE TABLE can do, plus a lot more?

SELECT * FROM sa_describe_query ( 'SELECT * FROM t', 1 );

column_number name domain_id domain_name domain_name_with_size width scale declared_width user_type_id user_type_name correlation_name base_table_id base_column_id base_owner_name base_table_name base_column_name nulls_allowed is_autoincrement is_key_column is_added_key_column
1 pkey 2 int int 4 0 4 (NULL) (NULL) t 723 1 DBA t pkey false true true false
2 data1 9 varchar varchar(100) 100 0 100 (NULL) (NULL) t 723 2 DBA t data1 false false false false
3 data2 27 decimal decimal(11,2) 11 2 11 (NULL) (NULL) t 723 3 DBA t data2 false false false false
4 data3 12 long binary long binary 2147483647 0 32767 (NULL) (NULL) t 723 4 DBA t data3 false false false false

Here's a big difference that might not be obvious: DESCRIBE TABLE is an ISQL statement so you'll see "Syntax error near 'describe'" if you try to use it inside a stored procedure, whereas sa_describe_query works just fine in both contexts (ISQL and SQL):

CREATE PROCEDURE p ( IN @query LONG VARCHAR )
BEGIN
SELECT name AS column_name,
domain_name_with_size AS data_type
FROM sa_describe_query ( @query, 1 )
ORDER BY column_number;
END;

SELECT * FROM p ( 'SELECT * FROM t' );

column_name data_type
pkey int
data1 varchar(100)
data2 decimal(11,2)
data3 long binary

If the query involves a view, sa_describe_query() tells you where the columns are coming from (if it can):

CREATE VIEW v AS
SELECT data1,
data2 * 10,
pkey
FROM t;

SELECT name AS view_column_name,
domain_name_with_size AS data_type,
STRING ( base_table_name, '.', base_column_name ) AS base_column
FROM sa_describe_query ( 'SELECT * FROM v' )
ORDER BY column_number;

view_column_name data_type base_column
data1 varchar(100) t.data1
expression numeric(13,2) .
pkey int t.pkey

SELECT name AS view_column_name,
domain_name_with_size AS data_type,
STRING ( base_table_name, '.', base_column_name ) AS base_column
FROM sa_describe_query ( 'SELECT * FROM SYSTAB' )
ORDER BY column_number;

view_column_name data_type base_column
table_id unsigned int ISYSTAB.table_id
dbspace_id smallint ISYSTAB.dbspace_id
count unsigned bigint ISYSTAB.count
creator unsigned int ISYSTAB.creator
table_page_count int ISYSTAB.table_page_count
ext_page_count int ISYSTAB.ext_page_count
commit_action int ISYSTAB.commit_action
share_type int ISYSTAB.share_type
object_id unsigned bigint ISYSTAB.object_id
last_modified_at timestamp ISYSTAB.last_modified_at
table_name char(128) ISYSTAB.table_name
table_type tinyint ISYSTAB.table_type
replicate char(1) ISYSTAB.replicate
server_type tinyint ISYSTAB.server_type
tab_page_list long varbit ISYSTAB.tab_page_list
ext_page_list long varbit ISYSTAB.ext_page_list
pct_free unsigned int ISYSTAB.pct_free
clustered_index_id unsigned int ISYSTAB.clustered_index_id
encrypted char(1) ISYSTAB.encrypted
last_modified_tsn unsigned bigint ISYSTAB.last_modified_tsn
file_id smallint ISYSTAB.dbspace_id
table_type_str char(13) .

Monday, January 24, 2011

Techwave 2011

Sybase Techwave 2011 is going to be co-located with the Las Vegas edition of SAP TechEd 2011 at the Venetian hotel (where I hear the rib steak is excellent) on Monday September 12 through Friday September 16 2011.

Friday, January 21, 2011

The fRiDaY File

This was first published on March 21, 2006:

IvanAnywhere, meet TeleDork

Well, I don't care if it looks like Vlad The Impaler's vision of a Teletubbie...


... I Want One!


Wednesday, January 19, 2011

SQL Anywhere Debugger Beta

For more information, click here...

Monday, January 17, 2011

More Toad

No SQL Anywhere support in Toad yet, but AFAIK it's still coming.

In the meantime, there's a Toad for Sybase Webcast tomorrow...



and an Open Toad Beta running now...



...yes, I know, bad pun, BAD pun :)

Friday, January 14, 2011

The fRiDaY File

This was first published on February 27, 1996:



Yesterday a colleague suggested that Wally sounds just like me, but I don't see it myself.

p_drop_other_connections Revisited

You may have seen a little stored procedure in the newsgroups or out on the web called "p_drop_other_connections" which issues DROP CONNECTION commands for every connection except the one that called the procedure. It's useful when you want to grab complete control of a running SQL Anywhere server and do something important, like ALTER TABLE or CREATE INDEX on the Table From Hell that everyone's using:

/* How to get full control over a SQL Anywhere server...

(1) CALL sa_server_option ( 'ConnsDisabled', 'ON' );

At this point, any attempt to open a new connection will
get "Connections to database have been disabled".
However, current connections will still work until...

(2) CALL p_drop_other_connections();

(3) Do whatever you need to do.

(4) CALL sa_server_option ( 'ConnsDisabled', 'OFF' );

*/
Well, it turns out that the original p_drop_other_connections doesn't work at all on connections to other databases running on the same server: the DROP CONNECTION command gets SQLCode -121 Permission denied.

Here's a new version of p_drop_other_connections that does work, by sending DROP CONNECTION commands to the other databases where they will work without getting permission denied:

CREATE PROCEDURE p_drop_other_connections ()
BEGIN

/* How to get full control over a SQL Anywhere server...

(1) CALL sa_server_option ( 'ConnsDisabled', 'ON' );

At this point, any attempt to open a new connection will
get "Connections to database have been disabled".
However, current connections will still work until...

(2) CALL p_drop_other_connections();

(3) Do whatever you need to do.

(4) CALL sa_server_option ( 'ConnsDisabled', 'OFF' );

*/

DECLARE @this_database_number INTEGER;
DECLARE @this_connection_number INTEGER;
DECLARE @database_name VARCHAR ( 128 );
DECLARE @sql LONG VARCHAR;
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
DECLARE @connection_count INTEGER;
DECLARE @dropped_count INTEGER;

SET @this_database_number = CONNECTION_PROPERTY ( 'DBNumber' );
SET @this_connection_number = CONNECTION_PROPERTY ( 'Number' );
SET @connection_count = 0;
SET @dropped_count = 0;

SET @database_name = ''; -- until filled in with an actual value

-- Create a remote server without the DBN filled in.

BEGIN
DROP SERVER other_database;
EXCEPTION WHEN OTHERS THEN -- ignore any exception
END;

CREATE SERVER other_database CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere 11;DBN=unknown';

-- Loop through each connection on the server.

FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT sa_conn_info.DBNumber AS @database_number,
sa_conn_info.Number AS @connection_number
FROM sa_conn_info()
ORDER BY sa_conn_info.DBNumber,
sa_conn_info.Number
FOR READ ONLY
DO

SET @connection_count = @connection_count + 1;

CASE

WHEN @database_number = @this_database_number
AND @connection_number = @this_connection_number THEN

-- Don't drop this connection.

WHEN @database_number = @this_database_number THEN

-- Drop local connection.

BEGIN
SET @sql = STRING (
'DROP CONNECTION ',
@connection_number );
MESSAGE @sql TO CLIENT;
EXECUTE IMMEDIATE @sql;
SET @dropped_count = @dropped_count + 1;
EXCEPTION WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING ( 'EXCEPTION: ',
@sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;
END;

ELSE

IF DB_PROPERTY ( 'Name', @database_number ) <> @database_name THEN

-- Change the DBN for the remote server.

SET @database_name = DB_PROPERTY ( 'Name', @database_number );

BEGIN
SET @sql = 'ALTER SERVER other_database CONNECTION CLOSE';
MESSAGE @sql TO CLIENT;
EXECUTE IMMEDIATE @sql;
SET @sql = STRING (
'ALTER SERVER other_database USING ''DRIVER=SQL Anywhere 11;DBN=',
@database_name,
'''' );
MESSAGE @sql TO CLIENT;
EXECUTE IMMEDIATE @sql;
EXCEPTION WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING ( 'EXCEPTION: ',
@sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;
END;

END IF;

-- Drop the remote connection.

BEGIN
SET @sql = STRING (
'FORWARD TO other_database ''DROP CONNECTION ',
@connection_number,
'''' );
MESSAGE @sql TO CLIENT;
EXECUTE IMMEDIATE @sql;
SET @dropped_count = @dropped_count + 1;
EXCEPTION WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING ( 'EXCEPTION: ',
@sqlcode, ', ', @sqlstate, ', ', @errormsg ) TO CLIENT;
END;

END CASE;

END FOR;

BEGIN
DROP SERVER other_database;
EXCEPTION WHEN OTHERS THEN -- ignore any exception
END;

MESSAGE STRING ( @dropped_count, ' of ', @connection_count, ' connections dropped.' ) TO CLIENT;

END;
Here's what a test looks like:
CALL p_drop_other_connections();

ALTER SERVER other_database CONNECTION CLOSE
ALTER SERVER other_database USING 'DRIVER=SQL Anywhere 11;DBN=ddd11b'
FORWARD TO other_database 'DROP CONNECTION 26'
FORWARD TO other_database 'DROP CONNECTION 27'
DROP CONNECTION 30
ALTER SERVER other_database CONNECTION CLOSE
ALTER SERVER other_database USING 'DRIVER=SQL Anywhere 11;DBN=ddd11c'
FORWARD TO other_database 'DROP CONNECTION 28'
FORWARD TO other_database 'DROP CONNECTION 29'
5 of 6 connections dropped.
Here's the before-and-after...



Here are some notes on the code...

The CREATE SERVER statement at line 44 establishes the partial definition for a remote server that will be completed via ALTER SERVER statements later on. The CREATE SERVER statement doesn't actually DO anything, like connect to a remote server, but that's not important here... what's important is that CREATE SERVER doesn't have to be semantically perfect when it's executed.

The FOR statement at line 49 calls the sa_conn_info() procedure to get information about every single connection to every single database on the server.

The CASE statement starting at line 60 makes a three-way choice:
  • This connection? Don't do anything.

  • A different connection to this database? Issue a simple DROP CONNECTION.

  • A different database? Send a DROP CONNECTION to that database via FORWARD TO.
The BEGIN END blocks starting at lines 71, 93 and 114 are there to capture any exceptions and carry on with the next connection.

The EXECUTE IMMEDIATE statement at line 76 drops the local connection. If it gets an exception, the SET @dropped_count = @dropped_count + 1 at line 77 doesn't get executed; instead, the MESSAGE statement at line 81 displays the exception in the ISQL Messages tab.

The ALTER SERVER on lines 94 through 96 forces the remote server to disconnect from the previous database. This is necessary for the change made by the second ALTER SERVER on lines 97 through 102 to have any effect; otherwise, the old connection would remain in effect even though the USING clause has changed.

The FORWARD TO statement on lines 115 through 120 send the DROP CONNECTION statement over to the other database, where it's legal.

Wednesday, January 12, 2011

Syntax error on line 5475, column 0: Illegal character "\n" (10)

Back in my callow youth, when I learning to program, the bane of my existence was compilers that diagnosed syntax errors thusly:

ERROR IN ABOVE PROGRAM
Yes, parsers always SHOUTED in those days, no lower case on the printers... which wouldn't have helped anyway since keypunches were all upper case as well.

Now, everything's so much better, no more keypunches, and parsers don't shout any more.

But sometimes, the error messages are just as unhelpful:



So what does that mean? Especially when there is no line 5475, the code ends at 5474.

It means you've made a really simple mistake, like a missing quote, and you are being punished by the parser.

Here's one way to find the problem: Open the offending SQL file in dbisql, and run it so you get the same error. Then scroll to the bottom of the file; you will probably see that the syntax coloring is wrong: what should be black and blue is red, and what should be red is black and blue.

Keep scrolling upwards until the coloring is correct again, and at that point you should see where to put the missing quote:
IF @update_diagnostics = '
   THEN ''
   ELSE '\x0d\x0a'
   ENDIF,


Or where to take out the extra quote.

Or where to fix the
'stupid backquote`
and make it a
'proper quote'.

Monday, January 10, 2011

READ and PARAMETERS

The ISQL READ and PARAMETERS statements are wonderful things when you need to apply string substitutions to SQL code when it's being compiled or executed.

Here's how to use them:

  1. Assign a name to each different string substitution value you want applied to a SQL file.

  2. Code each name in a PARAMETERS statement at the top of the SQL file, like this:
    PARAMETERS column_name, column_value, primary_key_value;

  3. Code the parameter name in {curly braces} down in the body of the SQL file wherever you want the actual value to be substituted; for example:
    UPDATE t SET t.{column_name} = '{column_value}' WHERE t.pkey = {primary_key_value};

  4. Code the actual values in [square braces], left to right in the same order as the PARAMETERS statement, in a READ statement on the ISQL command line, like this:
    "%SQLANY12%\bin32\dbisql.com"^
    -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
    READ ENCODING Cp1252 p1.sql [data2] [Hello, world!] [3]

  5. Run the ISQL command line to have the actual values substituted like this:
    UPDATE t SET t.data5 = 'Hello, world!' WHERE t.pkey = 3;
Here's a demonstration; first, the table definition:
CREATE TABLE t (
pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
data1 VARCHAR ( 100 ) NOT NULL DEFAULT '',
data2 VARCHAR ( 100 ) NOT NULL DEFAULT '',
data3 VARCHAR ( 100 ) NOT NULL DEFAULT '' );

INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
COMMIT;

SELECT * FROM t ORDER BY pkey;

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','',''
4,'','',''
Here's a procedure in a file called p1.sql:
PARAMETERS column_name, column_value, primary_key_value;

CREATE PROCEDURE p1()
BEGIN
UPDATE t SET t.{column_name} = '{column_value}' WHERE t.pkey = {primary_key_value};
COMMIT;
END;
Here's the dbisql command used to compile p1.sql:
"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 p1.sql [data2] [Hello, world!] [3]
Here's a test of the procedure p1:
CALL p1();
SELECT * FROM t ORDER BY pkey;

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','Hello, world!',''
4,'','',''



Now, supposing you have lots and lots of SQL files, many or all of them needing PARAMETERS substitution, and you want run one ISQL command line to compile them all. Here's how:
  1. Create a master SQL file containing one READ statement for each of the other files.

  2. Code a master PARAMETERS statement at the top of the master SQL file to define the complete set of substitution values across all the other files.

  3. Use nested [{square and curly braces}] to pass the actual values from the READ statement on the master ISQL command line, down through the READ statements inside the master SQL file, and on to the other SQL files.
Here's a sample master.sql file:
PARAMETERS column_name, column_value, primary_key_value, global_database_id;
READ ENCODING Cp1252 p1.sql [{column_name}] [{column_value}] [{primary_key_value}];
READ ENCODING Cp1252 p2.sql [{global_database_id}] [{column_name}] [{primary_key_value}];
Here's the master ISQL command line:
"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 master.sql [data2] [Hello, world!] [3] [123]
Here's the new p2.sql file:
PARAMETERS global_database_id, column_name, primary_key_value;

CREATE PROCEDURE p2()
BEGIN
SET OPTION PUBLIC.global_database_id = '{global_database_id}';
UPDATE t SET t.{column_name} = UPPER ( t.{column_name} } WHERE t.pkey = {primary_key_value};
COMMIT;
END;
Here's a test of p1 and p2:
CALL p1();
CALL p2();
SELECT DB_PROPERTY ( 'GlobalDBId' );
SELECT * FROM t ORDER BY pkey;

DB_PROPERTY('GlobalDBId')
'123'

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','HELLO, WORLD!',''
4,'','',''



Tip: If you're using SQL Anywhere 10 or later, and you see an error like this,
Could not execute statement.
Invalid setting for option 'global_database_id'
SQLCODE=-201, ODBC 3 State="42000"
File: "test1.sql" on line 3, column 40
SET OPTION PUBLIC.Global_database_id = '{value}'
try wrapping the offending statement in a BEGIN END block to force ISQL to handle the string substitution properly.

For example, change your SQL file from this (which works OK in SQL Anywhere 9 but throws SQLCODE = -201 in versions 10, 11 and 12)...
PARAMETERS value;

SET OPTION PUBLIC.Global_database_id = '{value}';

MESSAGE STRING ( 'GlobalDBId = ', DB_PROPERTY ( 'GlobalDBId' ) ) TO CLIENT;
to this...
PARAMETERS value;

BEGIN
SET OPTION PUBLIC.Global_database_id = '{value}';

MESSAGE STRING ( 'GlobalDBId = ', DB_PROPERTY ( 'GlobalDBId' ) ) TO CLIENT;
END;
which works in all four versions of SQL Anywhere to display this...
GlobalDBId = 123
Execution time: 0 seconds

Friday, January 7, 2011

The fRiDaY File

This was first published on March 20, 2006:

STFW

Here's a wall-mounted electrical timer that controls some outdoor lights. When it's working those lights turn on at dusk and off at dawn:



When it's not working (which is right after anyone messes with it) the lights turn on and off at the wrong times.

The definition of "anyone" includes "me"... after hours of messing with it, I realized that I had to RTFM.

Trouble was, no manual. No visible model number either, not even a manufacturer's name. A friend suggested "take the photo to Home Depot and buy another timer... that way you'll get a manual."

But that was then...


...and this is 2011.

It ain't RTFM any more, the new rule is STFW.

So I took the word "timer" plus the only words visible on the timer itself (off clear on) and did a Google search.

I did this even though I was sure that "off" and "on" would be useless noise as far as Google was concerned...
timer off clear on

GADZOOKS!




The first result was exactly what I was looking for!

STFW Rules!




What does this have to do with SQL Anywhere?

Nothing, except that not all answers to SQL Anywhere questions are found in the SQL Anywhere manuals. Sometimes they are found elsewhere.

That's what STFW means: Look elsewhere. Look everywhere.

For example: How do I stop dbmlsync from a command line?



That search didn't work so well a few days ago, but this generalized version did: How do I kill a Windows task from a command line?



That answer ("it's called taskkill, and it's from Microsoft") led to this search: taskkill site:microsoft.com



Question: When Google becomes self-aware, will anyone notice?

Wednesday, January 5, 2011

The Incredible Shrinking Database: A Quiz

How well do you know how SQL Anywhere works, when it comes to using disk space? Here's a quiz to test your knowledge...

But first, the setup...


The following table shows 4 stages in the history of a SQL Anywhere 12.0.0.2601 database.
  • The "File Size" column shows how big the main database file was at each stage, based the FileSize database property.

  • The "Free Space In File" column show how much free space existed in the database file, based on FreePages database property.

  • The "Disk Space Usage For All Tables" column shows how disk space was allocated for table data, blob extensions and indexes. These numbers were calculated by Foxhound based on data from the SYSTAB and SYSPHYSIDX tables.

  • The "Table From Hell" column shows how disk space was allocated for The One Big Table in this database. These numbers were also calculated by Foxhound based on SYSTAB and SYSPHYSIDX.

  • Stage 1 is the starting point, with over 3 million rows of data pretty much filling up all 475 megabytes of disk space in the main database file.

  • Stage 2 was reached after a series of DELETE statements got rid of almost all of the rows in the Table From Hell, followed by a COMMIT and a CHECKPOINT.

  • Stage 3 was reached after the SQL Anywhere server was stopped and restarted.

  • Stage 4 was reached after the database was rebuilt via dbunload, dbinit and dbisql.

And now, the Quiz...

  1. Why did the File Size more than double from 475M to 1.1G between stages 1 and 2?

  2. Why did the Table From Hell's disk space usage only drop from 440M to 368M between stages 1 and 2 even though 97% of the rows were deleted?
    Hint: Don't pay too much attention to the "bytes per row" numbers. They are based on the disk space allocated to the table, not the actual data stored in each row. In other words, no, the rows didn't suddenly change in size, nor did the DELETEs just get rid of short rows.

  3. Why did the Free Space In File only grow to 122M in stage 2 even though so much data was deleted?

  4. Why did the File Size drop so dramatically between stages 2 and 3?

  5. Why did the Free Space In File more than double in size between stages 2 and 3?

  6. Why did the disk space allocated to the Table From Hell drop at all between stages 2 and 3?

  7. Why is the file still roughly the same size in stage 3 that it was in stage 1, even though the Table From Hell only takes up half as much disk space?

  8. Why did the disk space allocated to the Table From Hell drop so dramatically between stages 3 and 4?

  9. Why did the File Size drop so dramatically between stages 3 and 4?

  10. Why is the Free Space In File so small in stage 4?
Ten questions, ten points each. When I find out what all the answers are, I'll post them.

"WHAT!?!? What do you mean, you don't know all the answers?"


That's right.

And I'll give out 10 Bonus Points if you can point out which questions I'm not sure of.

Hint: I'm pretty sure of numbers 9 and 10.

...and I'm pretty sure the only people who will score 100 work in Engineering. Or 110 :)

Monday, January 3, 2011

Be Very Afraid Of Floating Point Numbers

What does this statement in the Help mean, exactly?

Values held as DOUBLE are accurate to 15 significant digits, but may be subject to rounding error beyond the fifteenth digit.
In the classroom or laboratory, it probably means this:
Values held as DOUBLE are accurate to 15 significant digits, but may be subject to rounding error beyond the fifteenth digit.
In the real world, where people write programs to do real things with real data, it means this:
Values held as floating point cause all sorts of problems and should be avoided at all costs. When you can't avoid them, convert them. When you can't convert them, don't trust them: Test them, check them, and fix them when you find errors in the data. Do it in your code, don't wait for your users to notice drifting data six months down the road.
Here's an example that doesn't involve any arithmetic, so we can't blame the algorithms:
  • An application written using Microsoft Access has a large number of columns with the "Data Type" defined as "Number" and the "Field Size" set to "Double" instead of "Long Integer" or "Decimal" or something safe and manageable.

  • One of these columns is the primary key for a table with over 3 million rows.

  • The SQL Anywhere 12 migration wizard is used to convert the Access database into a SQL Anywhere database, and it dutifully defines the primary key as DOUBLE... after all, what else can it do? That's what Access says it is.

  • After migrating, most of the primary key values look like integers; they have values like 11, 21 and 3208547315.

  • However, over a million rows have values that look like this: 1020661200.9999999, 1020661224.0000001, 3108547316.0000005 and 3108547313.9999995.
Yes, those are all way out past the fifteenth digit.

I don't care where the problem comes from, Access, SQL Anywhere or the migration process. This kind of crap always happens when you mess with floating point, and in this case I just want it expunged, excised, gone, done with.

I'm not going to take a chance with a situation like this...

SELECT ( SELECT COUNT(*) FROM t WHERE pkey - TRUNCNUM ( pkey, 0 ) = 0 ) AS "Integers",
( SELECT COUNT(*) FROM t WHERE pkey - TRUNCNUM ( pkey, 0 ) <> 0 ) AS "Not Integers";

Integers Not Integers
1,917,541 1,252,134

I'm going to bite the bullet, do this...

UPDATE t
SET pkey = ROUND ( pkey, 0 )
WHERE pkey - TRUNCNUM ( pkey, 0 ) <> 0;

1252134 row(s) updated
Execution time: 272.574 seconds



Then I'm going to UNLOAD that table, change the data type from DOUBLE to BIGINT, and reload.

Or do an ALTER TABLE, that might be fun.

Either way, no more DOUBLE.

No more fear.