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.