Friday, July 25, 2008

Top 10 In 11

Here's why there haven't been any postings here for a few days: I've been working on Top 10 Cool New Features In SQL Anywhere 11... over 30 distinct features covered, 6500 words, plus a day job to deal with.

From the overview...

SQL Anywhere 11 has a surprisingly large number of new features, especially when you consider the huge number of features introduced only two years ago in SQL Anywhere 10. This article presents a Top 10 list of new features in SQL Anywhere 11 that developers are likely to find most interesting. Some of the features that didn't make the Top 10 list are described as well, including features that might be important to management but aren't necessarily cool as far as developers are concerned. Also included is a short list of features that are more weird than wonderful.
Want to read more? Come to Techwave.

Saturday, July 19, 2008

Foreign Keys Are Your Friends

Imagine a database where only some of the natural parent-child relationships are actually represented as foreign key relationships. Some are, some aren't... and now you have to write queries to join these tables.

Say, in MobiLink download_cursor scripts to implement synchronized partitioning among multiple databases, where the partitioning is controlled by data in some other table, not the one being downloaded.

How do you get from one table to another when there's no foreign key path between them? What columns do use in the INNER JOIN ... ON clauses?

Well, it's not easy. You might be tempted to use a NATURAL JOIN to automatically generate ON clauses based on columns with the same name in both tables. In the real world, however, NATURAL JOIN sucks... sometimes columns that should be used have different names, and other times columns that shouldn't be used have the same names.

And so you pick the wrong columns, and get the wrong rows. Or you pick too many columns and get too few rows. Or too few columns, and get too many rows.

With explicit foreign key relationships, a developer stands a very good chance of writing correct, reliable queries involving complex joins by simply inspecting the database schema. Foreign keys are a way for the database designer to communicate with the developer, providing direction: "this is how you get from here to there."

And that's one of the reasons that I say to developers, "foreign keys are your friends". Even if you disregard the safety of referential integrity, even if you don't consider the benefits that foreign key indexes might bring to performance, foreign keys are STILL good things.

Even for us ordinary folk.

Friday, July 18, 2008

SQL Anywhere 11 Developer Edition!

The SQL Anywhere 11 Developer Edition is now available for these platforms:

Windows 2000/XP/2003/Vista/2008

Windows Mobile (Pocket PC/Windows CE)

Linux x86 and x86_64
You can download it here.

DCX Rocks!

DCX stands for "DocCommentXchange", which is the windy name for a cool new feature in SQL Anywhere 11: Hypertext links from the Help to a website that lets you read other people's comments about individual topics... and enter your own.

Here's how it works: You open up a topic in the compiled HTML Help for SQL Anywhere 11, and you click on the DocCommentXchange link at the bottom:



That opens up the same Help topic in a separate browser window, this time in a page stored at dcx.sybase.com:



Down at the bottom you'll see a blue bar that reads "Click this bar to open comment area."

I won't bore you with the details of the login process, but it's not hard, and I'm a REALLY HARSH CRITIC of crappy web dialogs... let's just say that DCX is a refreshing change from business as usual!

Here's what an actual comment looks like in the "comment area":



And yes, dcx.sybase.com uses a SQL Anywhere database.

Sunday, July 13, 2008

Migrating Dirty Databases

Sybase Central has never been my first tool of choice when it comes to working with SQL Anywhere databases. I usually create script files and run them via Interactive SQL, the main reasons being these:

  • It's easy to rerun script files, not so easy to remember what buttons were pressed in a GUI.

  • It's easy to document changes by adding comments to the script files, not so easy to keep an audit trail of actions performed in Sybase Central.

  • It's easy to automate huge database "builds" by running dbisql.exe in batch mode, not so easy to automate a Sybase Central session.

  • It's easy to customize dbisql batches with the PARAMETERS statement.
But I'm trying to learn the joys of Sybase Central, I really am. And it's pretty slick, especially for some tasks like migrating a database from Oracle to Sybase Central. I really really don't like writing scripts to do that, they're really nasty, and the Sybase Central wizard is so much easier... even if you have to run it over and over again.

This posting is about a tiny little problem you might run into when migrating a database, especially an Oracle database, and a tiny little fix to make the problem go away.

Here's the problem: Oracle database designers sometimes define a column using the essentially unlimited-precision NUMBER datatype instead of something with an explicit precision, like NUMBER ( 7 ). When such a database is migrated to SQL Anywhere NUMBER columns become DOUBLE, whereas NUMBER ( 7 ) becomes NUMERIC ( 7 ).

So what? Well, in Oracle it's OK to set up a NUMBER primary key in a parent table, and have it correspond with a NUMBER ( 7 ) foreign key in a child table. When you try to migrate that, it goes Splat! ...in SQL Anywhere you can't use a DOUBLE primary key with a NUMERIC ( 7 ) foreign key:



If you have two or more problems like that, you'll only find one at a time; the migration wizard stops dead in its tracks on the first error.

The reason it stops is that the underlying stored procedures that perform the migration don't handle any exceptions, they just pass them back to the wizard. In the case of a foreign key error, the exception is raised by the statement "execute immediate stmt" near the bottom of the procedure called dbo.sa_migrate_create_fks:



Here's the fix: Wrap the execute immediate with a BEGIN block containing an EXCEPTION clause that traps each error, writes diagnostic messages to the SQL Anywhere console log, and continues on with the next foreign key:



Here's the code for copy and paste; you can use the same code in all four migration procedures to trap and diagnose all kinds of errors:
  • dbo.sa_migrate_create_fks

  • dbo.sa_migrate_create_base_tables

  • dbo.sa_migrate_create_proxy_tables

  • dbo.sa_migrate_data
BEGIN
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
execute immediate stmt;
EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING (
'DIAG ---------------------------',
'--------------------------------' )
TO CONSOLE;
MESSAGE STRING (
'DIAG ', CURRENT TIMESTAMP,
' SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg )
TO CONSOLE;
MESSAGE STRING (
'DIAG Statement: "', stmt, '"' )
TO CONSOLE;
END;

Now, when you have two or more migration problems, you'll see all of them at once in the console log:



Finding all the errors in one run is very important if you want to fix the problems on the SQL Anywhere side; e.g., if changes aren't allowed on the Oracle side, or you don't have time to wait for an overworked Oracle DBA to make changes one at a time.

Caution: When you add these EXCEPTION handlers, the Migrate Database wizard will run to completion WITHOUT displaying ANY error messages. The ONLY place you'll see the messages is in the engine console window.

Friday, July 11, 2008

SQL Anywhere 11 Goes GA!



The Developers Edition will be posted in a couple of days, but in the meantime Panorama for Linux and Windows is now shipping here...

Thursday, July 10, 2008

This is Glenn Paulley



This is what he's talking about: Techwave

Monday, July 7, 2008

Number One Reason To Attend Techwave

Here is my Number One Reason To Attend Techwave: Meet Anil Goel and Glenn Paulley.

And now, if you care at all about SQL Anywhere, especially SQL Anywhere query performance, it can be your Number One reason too:

"I'd also like to extend an invitation to meet with me (or Anil Goel) one-on-one; Techwave is my best opportunity to meet with customers and partners to discuss anything about SQL Anywhere.

See you in Vegas."


- Glenn Paulley makes an offer you can't refuse

Friday, July 4, 2008

Where's Borat?

Here is StatCounter.com's "Recent Visitor Map" for this blog:



Togo's there, but still nobody from Kazakhstan.

Wednesday, July 2, 2008

Dear FirstName

Don't you just feel the love in this personalized email?

I know I do :)...