Friday, August 17, 2007

How to upgrade a Derby/Java DB database

With the recent release of Derby 10.3, it's relevant to ask - what happens to the database you created with an older version when you upgrade Derby or Java DB?

Well, Derby is focused on ease of administration and embedded use, and this includes seriously painless upgrade.

There are two types of upgrade: soft upgrade and full upgrade.

With soft upgrade, you can use a new driver to work with a database created under a previous version, but older drivers will still be able to work with the database too -- the database itself is not upgraded to the newer version/format.

This is convenient if you want to just try things out, or if you have a mixed environment where some users are still using older versions of the driver.

The drawback of this approach is that if there are new features in the newer version that rely on a new database format or new system metadata, etc., then you can't take advantage of those new features. For example, Derby 10.3 has added features for security, SQL, and administration that won't work with an older database.

That is where full upgrade comes in.

You don't need to do anything crazy like export all your data, create a new database, and recreate your tables and load your data. All you have to do is set the "upgrade" property to "true" when you connect to the database.

There are two ways to do this. You can set it as a property directly in the URL, e.g
or you can add it as a property when you get the connection, e.g.

String dbURL = "jdbc:derby:travel";
Properties connProps = new Properties();
connProps.put("upgrade", "true");
Connection conn = DriverManager.getConnection(dbURL, connProps);
One important caveat: if you have created a database with a non-GA version of Java DB you can not upgrade it. This is because there is no commitment to support upgrade from what is basically an experimental database. All this mean is, please don't deploy a production solution using Java DB with a beta version of Java DB.


John H. said...

Nice... It would be great to have something like this on the Derby wiki - the current UpgradingTen page is getting kindof stale...

Anonymous said...

Thanks for your contribution to the wealth of knowledge so readily accessible on the web! Gotta love the time savings offered by technology :)