Thursday, September 23, 2004

Porting from oracle to firebird - howto

First i downloaded the opendbcopy.sourceforge.net and installed it
After that i used the jdbc driver (jaybird) for firebird and the jdbc driver that is with oracle 9 (copied jars in the lib directory)
Then i clicked on the DDL plugin and configured the drivers for source and destination databases and executed it choosing all tables from oracle database .
Then after xml files are generated is needed to run the ant tool to create the sql script for firebird .Here you need to download the latest version of ant tool
In my case i installed it in /opt/ant and after reading the manualexported these variables ANT_HOME=/opt/ant, JAVA_HOME=/opt/j2sdk1.4.2.x

Go to the OpenDBcopy/plugins/opendbcopy.schemageneration and
run >ant hbm2java
and then
>ant create-schema

for me it gave an error to at one table : " composite-id incomplete must match
"(meta*,(key-property|key-many-toone)+)"

so i had to move manually an table then it showed to the prompt BUILD SUCCESSFULL and database schema was created and could be viewed with

$view sql/Firebird_schema_create.sql

After this euphoric moment is time to go with another step :
pumping the data between the two databases (run the "Copy data from a source into a destination datbase" plugin)
So run the generated script into new database, for it gave some errors so i had to tweak some fields generated for example : numeric (19,255) to numeric (10)
(19 is too high for firebird - more than 64 bit precision ! )
Also oracles NUMBER is replaced with equivalent FLOAT from firebird , DATE converted to TIMESTAMP.
Another thing is about keywords like
"value" or "time " - you must quote the table names , and columns if want to use it
A good tip from opendbcopy manual is to disable the fk before data pump so i did .
In plugin area there is "Create Insert scripts for destination" and could be used for the same purpose. For that plugin is good to inspect the sql files generated and quote every timestamp value (it can be done with %s/\.0/\.0'/g in vim for example)
SYSDATE references need to replaced with
CURRENT_TIMESTAMP in the sql code (in fyracle you don't need to replace it - is done by the driver)
Sequences from oracle will be replaced with Generators


No comments: