Data Migration from Cloudscape/Derby to mySQL

Jamesm

Any suggestions on how to quickly export data from IBM's Cloudscape/Derby onto mySQL? The "ij" command line tool doesn't seem to be available (missing derbytools.jar file) in the AwareIM default installation.

AwareIM offers the option of exporting data but only on a "one by one" business object level, and even then we lose the relationships between the objects, since the objectID export doesn't import unless the object already exists (in other words the export is only useful if the file is imported onto the same database).

What would be the recommendation for exporting and importing all business objects and their relationships in a single step? Should we export the whole BASDB database schema? What tools should we use?

Thanks,
James

aware_support

We will be adding a utility to perform migration from Derby to MySQL in the nearest future.

In the meantime if you are familiar with Java you can write this utility yourself (with some help from us if required). The utility should be quite simple - it should copy the entire database schema of the BASDB database into exactly the same schema in MySQL (the MySQL schema will be automatically created once you publish your confuguration in MySQL).

Unfortunately this is all we can suggest at this stage. Let us know if you need help with this utility.

Jamesm

I managed to run ij by copying the missing derbytools.jar file to the lib directory. I created a ij.bat file in the bin directory that runs ij>

java -Dderby.system.home=DATA -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij

then...

ij version 10.1 (C) Copyright IBM Corp. 1997, 2004.
ij> connect 'jdbc:derby:BasDB';
ij> select * from domains;
ERROR 42X05: Table 'DOMAINS' does not exist.
ij> select * from SYSTABLES;
ERROR 42X05: Table 'SYSTABLES' does not exist.
ij> show connections;
CONNECTION0* - jdbc:derby:BasDB

= current connection

Are there any other configurations or scripts that must be executed before having access to the schema? Even the data dictionary table "systables" isn't visible !!!
I'm doing everything according to the IBM Cloudscape/Derby documentation but something must be missing. Has anyone there used ij to export tables? There's a system procedure that should accomplish this task, so all we need is to develop a script, after having configured the Derby environment to work and respond properly.

Procedure to export a table from the ij tool>
call syscs_util.syscs_export_table(null,'<table_name>','<export_file>',null,null,null,0);

aware_support

James,

You only need to export the tables specific to your business space. These tables have business space prefix followed by underscore, for example:

WMI_Order

The following script will show all the tables existing in a database in a file a.txt (change the paths to match your file locations)

java -Dderby.system.home="d:/eclipse21/eclipse/workspace/DATA" -classpath "d:/derby10/lib/derbytools.jar;d:/derby10/lib/derby.jar;" org.apache.derby.tools.dblook -d jdbc:derby:BASDB >a.txt

The following script will start ij:

java -Dderby.system.home="d:/eclipse21/eclipse/workspace/DATA" -Dij.protocol=jdbc:derby: -Dij.database=BASDB -classpath "d:/derby10/lib/derbytools.jar;d:/derby10/lib/derby.jar;" org.apache.derby.tools.ij

Jamesm

Thanks for the advise. The dblook script works well. I can see the list of tables. The "domains" table wasn't visible because I didn't include the app prefix, as app.domains.

But the system procedure to export still isn't working. See below>

C:\AwareTEST>java -Dderby.system.home="c:/awaretest/DATA" -Dij.protocol=jdbc:derby: -Dij.database=BASDB -classpath "c:/awaretest/tomcat/shared/lib/derbytools.jar;c:/awaretest/tomcat/shared/lib/derby.jar;" org.apache.derby.tools.ij
ij version 10.1
CONNECTION0* - jdbc:derby:BASDB

= current connection

ij> call syscs_util.syscs_export_table(null,'app.domains','domains.txt',null,null,null,0);
ERROR 42Y03: 'SYSCS_UTIL.SYSCS_EXPORT_TABLE' is not recognized as a function or procedure.

Any suggestions? The "select * from systables" still isn't working either.
Probably some system dictionary script to install these facilities?
Thanks

aware_support

I've never tried to launch a stored procedure from ij. Maybe it doesn't work.

The only thing I can suggest is to try launching it programmatically from Java.

Sergej

Mysql is already running on server but not integrated with awareim yet.

So will it be possible to correctly import bsv application file that was made in awareim with derby into awareim with mysql in configuration tool?

jacklcs

awareim support.. It is more cool if u can come out the utility to mirgate to sql server too....

Thanks....

ninap

hi, not to state the obvious but this alternative is also possible if you donot mind heavy lifting

a) export your bsv
b) export each BO data set (remember to maintain relations)
c) change your dbase setting to mysql or reinstall
d) create new bsv, import your original bsv
e) login as admin and begin importing each bo's export

if import fail, then re-export each bo and find variances using excel.

not sexy but a pragmatic means of moving from derby to mysql.

txs, nina 🙂

tford

Sergej's question:

So will it be possible to correctly import bsv application file that was made in awareim with derby into awareim with mysql in configuration tool?

This post describes moving a BSV, but not the data:

RentProperty

Hey There, Fellow Aware IM'ers!

In 2005 Aware Support said they'll be releasing utility to migrate from Derby to MySQL in the nearest future:

Posted: Mon Sep 19, 2005 2:14 am Post subject:
We will be adding a utility to perform migration from Derby to MySQL in the nearest future.

In the meantime if you are familiar with Java you can write this utility yourself (with some help from us if required). The utility should be quite simple - it should copy the entire database schema of the BASDB database into exactly the same schema in MySQL (the MySQL schema will be automatically created once you publish your confuguration in MySQL).

Unfortunately this is all we can suggest at this stage. Let us know if you need help with this utility.

That Was 8 Years ago so I'm assuming that tool is already available?? 😉 Does anyone know if that is the case, or if not maybe make a good suggestion?
We've got a VERY complicated Business space with Tons of Objects and lots of relationships so Import Export is not really an option.

We need to move to MySQL a.s.a.p.

Any feedback would be Greatly appreciated!!

Thanks a million!

aware_support

The utility hasn't been written. You need to use a tool for Derby such as Razor SQL and export the following tables:

1) All tables that start with your business space name
2) BAS_IDGEN table

Then you need to:
1) Import and PUBLISH your BSV in the MySQL system
2) Import the tables from your Razor SQL backup into the tables with the same name in MySQL