Some additional DB2 notes

In my original article I wrote, that I had some problem with the to_char() function while moving the Oracle database structure to DB2. The problem wasn’t so much about, that to_char() isn’t supported on DB2 (it is, it’s basically the same thing as varchar_formar()), but that the Migration Toolkit had generated a 3 argument version of to_char(), the 3rd argument being some I18N parameter. Apparently, what the MTK deduced from the originating database didn’t fit with DB2 9.7. Anyway, this is probably still a MTK issue.

Working with DB2 for the last weeks has been mostly a pleasant experience. First of all there comes a GUI (db2cc aka the DB2 command center, which is basically a front end to a couple of graphical tools) with the database, which simplifies most administration tasks and which also provides a SQL editor to help with the development of your SQL queries. Therefore you don’t necessarily need any 3rd party software, to make your life easier. Second, if you look up the explanation of a particular SQL error (db2 ? SQL?????N), you will receive helpful suggestions, what might have caused the problem. This was frequently good enough for me to keep going without further reading in any manuals.

However I also have an issues with db2ce, the SQL command editor. When I try to save my developed SQL query to a file only the root (/) directory is offered as the destination location. I was unable to select a different location with the file selection box. If I tried to create a directory within the file selection dialog, I received a SQL22204N error and the dump/db2dasdiag.log file contained the following:

2009-08-25-16.19.31.198185+120 I5362G279          LEVEL: Error
PID     : 27995                TID : 3041135504
FUNCTION: DB2 Tools, DB2 administration server, db2dasFMdirectoryCreate, probe:30
CALLED  : DB2 Common, OSSe, ossDirectoryCreate
RETCODE : ECF=0x90000001 Access denied

Unfortunately there is no indication of the file path, which might cause the error.

The other issue concerns the user administration. I’m trying to give an additional system user access to some tables from the main schema. I’m received a large Java stack trace once I clicked in the respective dialog box to select the tables from the particular schema and I can’t proceed. Something like this:

    ....
    at javax.swing.JComboBox.setEnabled(JComboBox.java:1381)
    at com.ibm.db2.tools.common.AssistCombo.setEnabled(Unknown Source)
    at db2_udb.UserGroupAddObjectDialog.itemStateChanged(Unknown Source)
    at com.ibm.db2.tools.common.AssistCombo.notifyItemListeners(Unknown Source)
    at com.ibm.db2.tools.common.AssistCombo.itemStateChanged(Unknown Source)
    at javax.swing.JComboBox.fireItemStateChanged(JComboBox.java:1214)
    at javax.swing.JComboBox.selectedItemChanged(JComboBox.java:1271)
    at javax.swing.JComboBox.contentsChanged(JComboBox.java:1318)
    at com.ibm.db2.tools.common.AssistCombo.contentsChanged(Unknown Source)
    at javax.swing.AbstractListModel.fireContentsChanged(AbstractListModel.java:111)
    at common.JComboBoxModel.setSelectedItem(Unknown Source)
    at javax.swing.JComboBox.setSelectedItem(JComboBox.java:564)
    at com.ibm.db2.tools.common.AssistCombo.setSelectedItem(Unknown Source)
    at javax.swing.JComboBox.setSelectedIndex(JComboBox.java:612)
    at com.ibm.db2.tools.common.AssistCombo.setSelectedIndex(Unknown Source)
    at common.ComboBox.setSelectedIndex(Unknown Source)
    at javax.swing.plaf.basic.BasicComboPopup$Handler.mouseReleased(BasicComboPopup.jav
    ....

When I’m terminating the db2cc Java even segfaults:

Unhandled exception
Type=Segmentation error vmState=0x00000000
J9Generic_Signal_Number=00000004 Signal_Number=0000000b Error_Value=00000000 Signal_Code=00000001
Handler1=B7D150E5 Handler2=B7DB7A0D InaccessibleAddress=A9824680
EDI=A8B013DC ESI=00000000 EAX=A9824680 EBX=A946787C
ECX=00000000 EDX=A9824680
EIP=AA9149C8 ES=0000007B DS=0000007B ESP=A94676CC
EFlags=00010282 CS=00000073 SS=0000007B EBP=A94676DC
Module=/home/db2inst1/sqllib/lib32/db2jddba
Module_base_address=AA8BE000 Symbol=_ZN13jdbcAEProfileD1Ev
Symbol_address=AA91497C
Target=2_40_20081105_025433_lHdSMr (Linux 2.6.27-gentoo-r8)
CPU=x86 (4 logical CPUs) (0xbe195000 RAM)
----------- Stack Backtrace -----------

I guess I need to check the DB2 forums for some hints.

Originally I installed DB2 on a AMD64 server to get familiar with the software. The actual destination server however was a 32bit Intel box. There is a PDF in the DB2 9.7 documentation set, which describes the various available options. After some fiddling I choose the combination of db2look and db2move to transfer the data.

On the originating host I executed:

db2look -d httpd -z db2inst1 -dp -e -o struc.sql
db2move HTTPD EXPORT -sn DB2\*

Then the data was transferred to the destination host, where I executed the commands

db2 -tvf struc.sql 
db2move httpd import -io insert

to restore the database. The “-io insert” is important here, otherwise you would have received errors in connection with tables, which reference foreign keys.

Otherwise it’s still smooth sailing with DB2.

Steffens Big 18

Steffens Big 18

Der erste große Meilenstein ist erreicht. Sohni Steffen feiert heute seinen 18. Geburtstag.

All the very best to you.

Pünktlich zu diesem Datum war am Freitag der offizielle Führerschein in der Post. Und dann wurde mir heute Abend ganz anders, als die drei Kinder ohne uns Eltern im Auto unterwegs waren, um noch etwas zu holen, was die jüngste Tochter vergessen hatte.

Irgendwie fühle ich mich da doch alt.

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.