DB2 Express-C 9.7: very, very cool

I’m using an old Oracle 8i database on a Linux host. I’m running it for the occasional web development. I think I first installed it on SuSE 7.3 or 8.0 system. I think Linux 2.4 was still young at the time and Glibc was probably 2.1 or 2.2. I’ve been moving the DB installation through the various Linux version until today where it still runs just fine on Gentoo Linux with a 2.6.27 kernel and Glibc 2.9. The only problem is, that I need to restart the DB periodically because the initial connection time becomes worse over time.

Anyway since the DB is so old, I’ve finally decided to move the contents to a new database. I could have used MySQL (I’m already using it for other applications), since I don’t really have any particular requirements for the database. However in the end I went for IBM DB2 Express-C 9.7. The database is completely free (of course without support), but is limited to 2 CPU cores and 2Gb of memory. The size of the database(s) is unlimited as compared to the Oracle XE version, which is limited to 4Gb (not that that wouldn’t be enough for me) and only uses 1Gb of memory and one core. I’ve read some good stuff on the Web (here and here) about DB2 and since I’m doing SAP installation with DB2 (and also with MaxDB and Oracle) as a job, I though it would be a good learning vehicle, it I would use DB2 myself for the little development I’m occasional doing. The last thing, that convinced me to use DB2 is the Migration Toolkit, which immensely simplified the move of the data from the source to the target database.

Download the platform archive and the language pack. The installation then is completely painless. Simply execute db2setup. I suggest to use the “Custom” installation option. This directly allows you to select to install the development libraries required for Perl, Python, PHP or Ruby and to install the text-search option. It took a bit to get the (D)atabase (A)dministration (S)erver running. This forum entry was the solution: the DNS entry for the host was missing. I fixed it by adding the hostname as an alias after localhost in the /etc/hosts file.

Next I played with the MTK to transfer the Oracle data to DB2. Biggest obstacle here was, that there was no indication, that the final script for migrating the Oracle data to DB2 didn’t run. The MTK explicitly uses /usr/bin/ksh to invoke the script. The was no indication in the the console window, that the shell couldn’t be started. However, on my Gentoo system, although the pdksh was installed, it didn’t allow the invocation through /usr/bin/ksh. Once I created the a symbolic link, the data was succesfully copied from Oracle to DB2. You have to examine the log files, to see if all database objects from the source database could be created in DB2.

In my particular case I had 3 issues with the conversion. The DB2 script, which creates the database objects, would try to recreate a particular view, which contained a couple definitions like “TO_CHAR(STATUS_SEIT_DATUM, 'yyyy-mm-dd' AS datum“. I had to edit the DB2 script and change these fields to “VARCHAR_FORMAT(STATUS_SEIT_DATUM, 'yyyy-mm-dd' AS datum“. I think, this is an issue, where the MTK could do better. Another issue concerned CLOB columns. The conversion process would recreate an Oracle CLOB column as “CLOB(2G) NOT LOGGED“, which is basically correct, but if you want to move the data later with the db2move command, you are limited to CLOB(1G), because the db2move command has a limitation in this respect. Another issue, I had to consider was due to the move from an ISO-8859-1 character set to Unicode. A column, which is defined as varchar(72) might get wider as 72 character, because typical German umlaut characters (öäüÖÄÜ) take up 2 charcaters. The last issue I experienced was with foreign key constrains. The conversion process drops any foreign key constraints and recreates them after successfull data loading. However my particular data had degenerated over time, so that the recreation of the foreign contraints failed in the final step. Otherwise is was all pretty smooth sailing. BTW, there is an IBM RedBook “Oracle to DB2 Conversion Guide for Linux, UNIX, and Windows“, which details the Oracle-DB2 migration process.

Next step was to install the Perl DBD::DB2 database driver by executing

DB2_HOME=/opt/db2/V9.7 perl Makefile.PL
LD_RUN_PATH=/opt/db2/V9.7/lib64 make install

The LD_RUN_PATH compiles the load path of the DB2 dynamic libraries directly into the Perl module, so that you won’t have to fiddle with the LD_LIBRARY_PATH variable. To successfully use the DBD::DB2 Perl DBI driver in connection with the Apache::DBI module and mod_perl I needed to set two environment variables in /etc/apache2/modules.d/apache2-mod_perl-startup.pl:

$ENV{DB2DIR} = "/opt/db2/V9.7";
$ENV{DB2INSTANCE} = "db2inst1";

There is only one final issue. On my Gentoo system at home running the 2.6.30 kernel, I always need to issue the db2start command twice. After the first invocation I’m seeing an error message in the db2diag.log file.

2009-08-02-20.47.49.327780+120 I4733E357           LEVEL: Severe
PID     : 31906                TID  : 140015648098048PROC : db2star2
INSTANCE: db2inst1             NODE : 000
FUNCTION: DB2 UDB, base sys utilities, DB2StartMain, probe:580
MESSAGE : ECF=0x900003DE=-1879047202=ECF_TRCAPI_MUTEX_LOCK_ERROR
          Trace facility mutex unexpected lock error

The second invocation of db2start then succeeds.

Otherwise you can consider me an DB2 fan. You will definitely notice, that DB2 is a very mature product.

3 thoughts on “DB2 Express-C 9.7: very, very cool

  1. Hey nice post. I went in the other direction as you. Had a DB2 install on Windows to do some DB work. Then I got rid of it and moved to Oracle. My customer has a big Oracle DB, and I thought I had better concentrate more on that.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>