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.
[DB2] Description = DB2 Driver Driver = /usr/IBMdb2/V6.1/lib/libdb2.so FileUsage = 1 DontDLClose = 1The 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 = DB2Then 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 ibmdb2That should be all there is to it.
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=1To quote "... the difference was UNBELIEVABLE !!!". Thanks Eric.
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
Driver=/opt/ibm/db2/V9.1/lib32/libdb2.soor 64 bit
Driver=/opt/IBM/db2/V9.7/lib64/libdb2o.soIts 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
Driver=/opt/ibm/db2/V9.1/lib32/libdb2.so Driver64=/opt/IBM/db2/V9.7/lib64/libdb2o.so
(Thanks Björn)
Getting the driver
Download page: http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg27016878
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-10.1.0.1-FP001,
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
...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 ltmain.sh and libltdl/config/ltmain.sh using unixODBC-2.3.1-libtool.patch (attached with this e-mail).
Then compile and install using GNU make:
/opt/freeware/bin/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 libodbc.so.2 libodbc.so ln -s libodbccr.so.2 libodbccr.so ln -s libodbcinst.so.2 libodbcinst.so
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 ODBCINI=/usr/local/etc/odbc.ini
(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:
[db2_odbc_source] Database=mydatabasename Protocol=TCPIP Hostname=my.database.server.com ServiceName=451234 uid=myusername pwd=SECRET
(This connects to a DB2 server on my.database.server.com 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:
[db2_odbc_source]
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.
Debugging
If things don't work and you want some further clues:
Add:
Trace = Yes
...to each DB2 data source in odbc.ini.
Add the following to odbcinst.ini:
[ODBC] Trace=Yes TraceFile=/tmp/sql.log Pooling=No
Add the following to /usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/cfg/db2cli.ini:
[COMMON] DiagPath=/usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/db2dump DiagLevel=4 Trace=1 TraceComm=1 TraceErrImmediate=1 TraceFlush=1 TraceFlushOnError=1 TraceTimeStamp=1 TraceFileName=/usr/local/src/DB2/db2_cli_odbc_driver/odbc_cli/clidriver/db2dump/db2trace.log TracePIDTID=1
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 slibclean 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