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, but there are extra details for 7.1 and 9.1

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.

Description     = DB2 Driver
Driver      	= /usr/IBMdb2/V6.1/lib/
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.

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/

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

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
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/

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

Version 9.1

As is described in this document IBM now have a different lib for 32 and 64 bit operation. So for 32 bit use
or 64 bit
Its also worth remembering that unixODBC allows you to have both 32 and 64 bit drivers installed in the same setup, and the driver manager will pick the right one from a shared set of ini filed

Version 10.1 Fix Pack 1 (IBM Data Server Driver for ODBC and CLI, 64-bit)

(Thanks Björn)

Getting the driver

Download page:

Preferrably choose the latest version from the "IBM Data Server Client Packages, Fix Packs by version", e.g. "Version 10.1 Fix Pack 1".

Then choose "IBM Data Server Driver for ODBC and CLI (64-bit)", "View all".

Then choose your platform under "Platform", e.g. "AIX 64-bit, pSeries" and check the appropriate checkbox, e.g. "fix pack: DSClients-aix64-odbc_cli-,
IBM Data Server Driver for ODBC and CLI (AIX 64 bit) V10.1 Fix Pack 1". Click "Continue" and login with your IBM ID (or sign up for a new one).

Download the file, e.g. "v10.1fp1_aix64_odbc_cli.tar.gz".

Untar to a suitable location, e.g. /usr/local/src/DB2. This will create a "db2_cli_odbc_driver" subdirectory, with another subdirectory "clidriver" in it.

Create debug/diagnostic directories, as those are missing:

cd /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver
mkdir db2 db2dump
chmod 1777 db2 db2dump

64-bit unixODBC on IBM AIX

...with gcc, set:

export CC=gcc
export CFLAGS="-maix64 -q64 -DBUILD_REAL_64_BIT_MODE"
export OBJECT_MODE=64

...although -q64 -DBUILD_REAL_64_BIT_MODE do not appear to influence anything (any longer).

Then configure with (e.g.):

./configure --enable-drivers=no --enable-gui=no --prefix=/usr/local

Ask libtool not to hardcode library paths into libraries before installing:

perl -i.bak -pe 's/^hardcode_into_libs=yes$/hardcode_into_libs=no/' libtool
perl -i.bak -pe 's/^hardcode_into_libs=yes$/hardcode_into_libs=no/' libltdl/libtool

Further enforce this by modifying and libltdl/config/ using unixODBC-2.3.1-libtool.patch (attached with this e-mail).

Then compile and install using GNU make:

/opt/freeware/bin/make install

Make sure to extract the archive members of the libraries and to create .so links to them:

cd /usr/local/lib
ar -x -X any libodbc.a
ar -x -X any libodbccr.a
ar -x -X any libodbcinst.a
ln -s
ln -s
ln -s

General unixODBC settings on IBM AIX

Your /etc/environment (or similar evironment variable-setting profile) should probably set the ODBCINI environment variable to point out odbc.ini:

# System-wide ODBC INI file for unixODBC

(You will need to logout and login again for the changes to take effect, of course.)

Configuring the driver

For AIX, odbcinst.ini should contain: [db2_odbc_driver] Description = IBM DB2 ODBC driver (64-bit) Driver = /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/lib/db2o.o FileUsage = 1 DontDLClose = 1

The db2o.o driver is the one to use for IBM AIX. (There is no need to extract anything from libdb2.a or similar, as in some previous versions.)

Then create a /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/cfg/db2cli.ini file containing one stanza for each data source. The result could look something like this:


(This connects to a DB2 server on TCP port 451234 using the supplied credentials.)

The stanzas should of course have identical names to those of the data sources in odbc.ini.

Configuring the data source

odbc.ini should contain a data source specifying a DMEnvAttr attribute:

Description     = IBM DB2 ODBC data source
Driver          = db2_odbc_driver
Trace           = No
DMEnvAttr       = SQL_ATTR_UNIXODBC_ENVATTR={DB2_CLI_DRIVER_INSTALL_PATH=/usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver}

This points out the installation path of the driver.


If things don't work and you want some further clues:


Trace           = Yes each DB2 data source in odbc.ini.

Add the following to odbcinst.ini:


Add the following to /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/cfg/db2cli.ini:


Perform an isql connection attempt and trace it via the truss command:

cd /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/db2dump
rm -f /tmp/sql.log
rm -f a.trc db2diag.log db2trace.log
truss -a -e -f -w -l -r -x -o a.trc isql -v db2_odbc_source

Then have a look at /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/db2dump/db2diag.log, /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/db2dump/db2trace.log, /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/db2dump/a.trc (from truss, e.g. look out for ENOENTs somewhere at the end) and /tmp/sql.log (from unixODBC).

Hopefully this will give you some clues as to why things don't work, if they don't work.

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

Nick Gorham