Using IBM DB2 with unixODBC

I have had a number of requests from people wanting to connect to IBM's DB2 from Linux via unixODBC. This short document details what I know about this at the moment. If anyone can contribute more imformation then please contact me at the address at the end of the document.

This document originally refered to the 6.1 version of DB2, since then I have tried the 7.1 build and with a couple of changes, these are detailed at the end of this document.

First, install the DB2 Client access package from IBM it will install the IBM CLI driver, this works with unixODBC as a ODBC driver.

Using the install in the Client Access package, setup the client access to give you a instance name (ie db2inst1), then setup your odbcinst.ini entry like this.

[DB2]
Description     = DB2 Driver
Driver      	= /usr/IBMdb2/V6.1/lib/libdb2.so
FileUsage       = 1
DontDLClose     = 1

The DontDLClose is required to avoid a problem in the driver when dynamically loaded (I suspect its registering a exit function which isn't being called).

Then setup your odbc.ini like this.

[sample]
Description     = Test to DB2
Driver      	= DB2

Then when it comes to connecting, you MUST have the environment variable DB2INSTANCE set to a vaild db2 instance, so for instance to connect with isql

export DB2INSTANCE=db2inst1
isql -v sample db2inst1 ibmdb2

That should be all there is to it.

Version 7.1

Since originally creating this document, IBM have released a new version of DB2 7.1. I have tried the 7.1 build and with a couple of changes, the same method seems to work fine. The only difference being the name of the package, where the document used to refer to the DB2 Client access package, the CLI driver is now to be found in the DB2 Personal Developer's Edition V7.1.

If using the 7.1 build the path to the library is now

Driver      = /usr/IBMdb2/V7.1/lib/libdb2.so

I have also found that you need to add "/usr/IBMdb2/V7.1/lib" to /etc/ld.so.conf.

It has been reported that this installation doesn't work in some situations. the attempt to open the driver fails, and cannot find a symbol sqltevents in libdpdcf.a. I hav'nt had this problem, so I wonder if there is a difference between platform, or builds of the DB2 instance. If anyone has any more info on this let me know.

Another useful hint. Somewho has been using PHP+DB2 on AIX via unixODBC to a BIG IBM server (4 processors, bags of core etc), and finding it was slow. It turns out there is a bug in IBM's TCP/IP stack. after looking at this and setting this

$ su - db2inst1
$ db2set DB2TCPCONNMGRS=1

To quote "... the difference was UNBELIEVABLE !!!". Thanks Eric.

Version 9.1

And yet more...

The current installation should look like this

Driver      = /opt/ibm/db2/V9.1/lib32/libdb2.so

IBM supply a script that sets DB2INSTANCE and LIBPATH. Here's what IBM suggests you put in your .bashrc if the DB2 instance you want to talk to is in /home/db2inst1:

if [ -f /home/db2inst1/sqllib/db2profile ]; then
       . /home/db2inst1/sqllib/db2profile
fi

If this is of any help to someone, good, any problems let me know. Thanks to Harold Lee for heliping me keep this doc somewhere near up to date.

Nick Gorham