Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy includes SPF record at
 spf.trusted-forwarder.org)
Message-ID: <41379AB1.7010706@debrunners.com>
Date: Thu, 02 Sep 2004 15:12:01 -0700
From: Daniel John Debrunner <djd@debrunners.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.4.1) Gecko/20031008
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: db2db data migration tool experience
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


I tried using db2db, an open source JDBC data migration tool with Derby.

http://db2db.sourceforge.net/

(note the name stands for 'db to db', nothing to do with IBM's DB2)

db2db claims it can migrate tables from one database to another using
JDBC and allow configurable conversion of datatypes, e.g. BOOLEAN in
source table to SMALLINT in the target table.

For Cloudscape and/or Derby you must have the embedded JDBC drivers on
the classpath when starting up db2db and set the drivers up as 'Load
from Classpath'.

I succeeded in getting both Cloudscape 5.1 and Derby embedded
connections setup at the same time and could do some simple database
browsing and execution of queries. One issue with the tool is that it
seems if you add schema objects such as tables or schemas after it has
loaded the initial information, they are not reflected in any subsequent
database browsing. You must restart to see the new items.

For the table transfer I tried Cloudscape 5.1 to Derby and I hit several
issues:

- - schema names do not seem to be carried forward automatically, i.e. if
the source tables are in schema X then there seems to be no way to
automatically create schema X in the target database. Even when I
created schema X, transferred tables seemed to be put in the current
schema of the target connection.

- - Forign keys seemed to cause problems, possibly due to the ordering
requirements, I just disabled their transfer in the preferences of db2db.

- - While I did get tables and their primary keys created in the target
database from the source database, there never seemed any attempt to
copy data over.

This was from a quick attempt, the tool actually looks good, but I just
can't get it to do a simple trandfer. If anyone else looks at db2db
could they post their view?

Another other JDBC data migration tools out there?
Dan.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBN5qwIv0S4qsbfuQRAu5wAKDkrKVPghbLv0WQVf4o/69uBlRU9ACfejyx
Jdup151a0eB28/dfWKubwOo=
=wbyF
-----END PGP SIGNATURE-----