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.

6 comments:

Bill said...

New features on your blog are COOL!

The topic and age of related blogs (Biff's Rows and Columns, etc) makes me read more articles since I don't spend time going to Glenn's blog every couple of days only to see he is still on vacation.

The new Topics index at the bottom is the BEST! I have often been digging around for a tutorial I know you have written in the past.

Thanks for your efforts to make us better developers!

Bill

Breck Carter said...

I've been cutting back on the use of keywords, and I was actually going to go back and trim them out of old entries.

I think you're saying I should continue to assign lots of relevant topic keywords, rather than just expecting folks will use Google search to find stuff... right?

Bill said...

In "my" case: I keep up with your blog. So I know you have an XML tutorial (OpenXML() Rocks). When I wanted to find it, I expanded previous months and then did a Find to get to the article. I knew you have some good material on the shortcomings of MySql, so when I needed to explain that to someone for the 50th time...

I don't know about others, but your new topic index at the bottom is the best new feature I have seen.

If the work to maintain it is a factor, I can certainly still find what I am looking for and I value your content much more than busy work to maintain the index!

Breck Carter said...

It's actually easier to just dump terms into the index when I post something, than it is to think about minimizing the number of terms and "gather" posts into "major topics"... which is what I was going to do, and which you have saved me from :)

Anonymous said...

Does this technique work for oracle and SA running on unix?
Seems like the oracle remote server class might have an issue..

Breck Carter said...

Anonymous said... Does this technique work for oracle and SA running on unix? Seems like the oracle remote server class might have an issue...

What issue? ODBC not working? I've heard that can be a real problem on some flavors of n*x. If you can't fix that (why not?) then one brute force technique might be to run SA on Windows (where ODBC always works) and migrate the Oracle database there, then just do a file copy over to the n*x box. SQL Anywhere database files are binary-compatible across all platforms.

FWIW the V11 Help says this about the iAnywhere Oracle ODBC driver: "If you use Oracle with MobiLink or OMNI, you must install an Oracle client on the same computer as this Oracle driver. The Oracle driver can be configured using the ODBC Administrator, the .odbc.ini file (in Unix), or the dbdsn utility." - "OMNI" means "remote server / proxy tables"

... in other words, it should work for SA on n*x. The part about Oracle running on n*x is *definitely* not an issue, that's where half the world's Oracle servers run.