Replacing DB2 with Derby

A few days back I had to upgrade my DB2 client connect. Unfortunately, that also meant I could no longer use my local DB2 personal edition since the versions conflict. Thanks Big Blue for insisting on installing everything in the same place!

As I prefer to develop against a private database and not a shared one, I had to look for an alternative. The last few months I had toyed with Apache Derby and noticed that even if Derby’s roots were in Cloudscape, the IBM managed to forge a striking resemblance to big brother DB2. We’re not using any exotic queries or stored procedures or such. So, I decided to give Derby a chance to replace my local DB2.

Initially I had to tweak my DDL a bit to set up all tables & constraints because we apparently used some DB2/OS390 specific short cuts. After that however, everything went smooth for a few days. Until today, when I stumbled on a SQL fragment that required the MICROSECOND function. Alas, that is not defined in Derby (yet?). I then tried to come up with alternatives that would have the same behaviour in Derby as in DB2. I tried various combinations of the MOD, TIME and INTEGER functions, but although I found several functional equivalent statements, none of those worked on both DB2 and Derby.

So what’s person to do then? Well, dig a little deeper and read how to create user-defined functions in the Derby manual!

It’s not exactly rocket science, but maybe these steps can be a help to someone.

First we define a public static function microsecond:

package com.tmme.derby.functions;
public class Time {
       public static final int microsecond (java.sql.Timestamp timeStamp) {
               return timeStamp.getNanos()/1000000;
       }
}

Then we put this in a jar tmmeFunctions.jar which we add to the Derby classpath by modifying %DERBY_HOME%\bin\common.bat (on windows). Using eclipse, making a jar is easiest by means of the export to archive file function in the File menu. Don’t forget to restart Derby or the functions won’t be available.

In a DB2 command processor console (or one the many Eclipse database plugins such as DBExplorer) we can define the function like this:

CREATE FUNCTION MICROSECOND(TIMSTMP TIMESTAMP) RETURNS INTEGER
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME
'com.tmme.derby.functions.Time.microsecond';

This will define the function in the currently active schema. In a regular Derby set-up this is determined by the id of the account used to connect to the server.

To test if everything works, we can execute:

VALUES MICROSECOND(CURRENT_TIMESTAMP);

With this done, I nearly reached my goal of replacing DB2 by apache. Another difference between the two however is the SECOND function. In DB2, this produces an integer result. In Derby it’s fractional. Hence the reason why Derby doesn’t have a MICROSECOND function. To overcome this last issue, I defined a function called SECOND in the same way as the MICROSECOND function above. By making sure it was defined in the same schema as the application is using, the user-defined function is overriding the built-in one.