How to migrate a Derby database?

[re...@gowdygroup.net]

It would be nice to have a way to convert a database to a form that is 
independent of the software used to support the database. For example, 
when using phpMyAdmin on a MySQL database there is an option referred to 
as "Export".  This option causes the creation of a text file that 
contains all of the SQL statements required to recreate the database at 
least in MySQL or Maria DB.  In theory, such a file should also be 
useful for migrating the same database to other kinds of relational 
databases that support SQL.

At least in the case of MySQL/MariaDB this capability provides an easy 
way to transfer databases from one server to another which may be 
desirable when making transition to a new release.  If nothing else it 
preserves the ability to restore to current release.  Given how many 
versions of Derby presently exist it seems like this should be 
especially important.

There is a present need to do such a migration simply to upgrade to a 
new version of Derby.  In the case where this migration is from a very 
old version of Derby the idea of a version independent file format would 
seem to be very desirable.  Is that possible with Derby?  If so, some 
reference to appropriate technique would be appreciated.  If NOT, is 
there a prescribed method for doing such that is safe and effective?

Ajax ...

Re: How to migrate a Derby database?

[Rick Hillegas <ri...@gmail.com>]

Hi Ajax,

Here are a couple points to consider:

0) Derby supports two kinds of upgrade: soft and hard. Soft-upgraded 
databases can be downgraded to previous releases of Derby (but not to a 
release earlier than the original version of the database). 
Hard-upgraded databases can not be downgraded. Please see the section on 
upgrades in the Derby Developer's Guide: 
http://db.apache.org/derby/docs/10.15/devguide/cdevupgrades.html

1) Consider using operating system commands to simply copy your old 
database to a new location and then hard-upgrade the new copy in place. 
You will need to quiesce the source database first, that is, gracefully 
shut it down. The Derby database format has not changed since the code 
was open-sourced in the early noughties. It is a platform-agnostic 
format, so you can simply tar up the whole directory tree holding the 
old database and then un-tar it in your target installation--even if the 
target machine has a different architecture. Hard-upgrade is something 
which we test whenever we vet a new Derby release. The test involves 
creating a database from each of the releases which we have produced 
over the past fifteen years and then hard-upgrading those databases to 
the latest version, that is, to the release which we are vetting. Very 
few bugs have been logged against hard-upgrade.

2) If you want to make this as difficult as MySQL, you can, of course, 
dump the schema-creating DDL from the old database and then replay it in 
a fresh database. Use the dblook tool for this purpose. It is somewhat 
analogous to the MySQL Export tool mentioned below. Please see its 
documentation in the Tools Guide: 
http://db.apache.org/derby/docs/10.15/tools/ctoolsdblook.html Once you 
have a schema shell, you will need to populate the tables with data from 
the original database. There are two ways to do this:

i) Use the SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure to dump data 
from the original database into flat files and then use the 
SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure to load those files into 
the new target database. Please see the sections on these procedures in 
the Reference Manual: 
http://db.apache.org/derby/docs/10.15/ref/rrefexportproc.html and 
http://db.apache.org/derby/docs/10.15/ref/rrefimportproc.html

ii) You can avoid indirecting through flat files and, instead, transfer 
your data quickly and directly by using the foreignViews tool documented 
in the Tools Guide. Please see 
http://db.apache.org/derby/docs/10.15/tools/rtoolsoptforeignviews.html 
In case, you're interested, this tool can also be used to quickly 
migrate data out of non-Derby databases.

I would rank these approaches in ascending complexity and time needed, 
as follows:

   1
   2ii
   2i

I would avoid over-thinking this problem. Derby does not suffer from the 
kinds of version-specific format incompatibilities which plague other 
databases. Option 1 is your best choice.

Please feel free to ask more questions.

Hope this helps,
-Rick


On 6/28/20 10:17 AM, receiver@gowdygroup.net wrote:
> It would be nice to have a way to convert a database to a form that is 
> independent of the software used to support the database. For example, 
> when using phpMyAdmin on a MySQL database there is an option referred 
> to as "Export".  This option causes the creation of a text file that 
> contains all of the SQL statements required to recreate the database 
> at least in MySQL or Maria DB.  In theory, such a file should also be 
> useful for migrating the same database to other kinds of relational 
> databases that support SQL.
>
> At least in the case of MySQL/MariaDB this capability provides an easy 
> way to transfer databases from one server to another which may be 
> desirable when making transition to a new release.  If nothing else it 
> preserves the ability to restore to current release.  Given how many 
> versions of Derby presently exist it seems like this should be 
> especially important.
>
> There is a present need to do such a migration simply to upgrade to a 
> new version of Derby.  In the case where this migration is from a very 
> old version of Derby the idea of a version independent file format 
> would seem to be very desirable.  Is that possible with Derby?  If so, 
> some reference to appropriate technique would be appreciated.  If NOT, 
> is there a prescribed method for doing such that is safe and effective?
>
> Ajax ...
>