My hatred for DB2 grows. I thought it was bad setting up the DB2 server - it’s even worse getting the ODBC adapter working. It took me a good 2 days of fiddling to figure this out. Fricken ridiculous.
All the ODBC documentation on the web focuses on talking to DB2 on a local machine. I needed ODBC to talk to DB2 on a remote machine.
But first, some quick background before we start. unixODBC has two configuration files, odbcinst.ini for database drivers and odbc.ini for database sources. Drivers are mechanisms for talking to database, and sources are database definitions.
Most (all?) unixODBC sources have all their config under their own section in /etc/odbc.ini. DB2 likes to be different and store its own config in a separate file called db2cli.ini. The file is used by DB2 utilities that use the db2cli. (a bit of background on the db2cli can be found here)
Anyway, I managed to set this up with DB2 8 under Fedora Core 6 but I assume it works under RHEL 5, and could easily be transposed to other distros.
First step - install unixODBC:
yum install unixODBC
Now you’ve got to install a copy of DB2.
IBM provide a bunch of different editions of DB2. Don’t fall into the trap of using the “IBM DB2 Driver for ODBC and CLI” - I wasn’t able to get it talking to a remote DB2 server. You want to grab the “DB2 Runtime Client”. Infuriatingly IBM have removed all links to older versions of DB2 on their website, but you can find downloads for DB2 8 here, and DB2 9 here.
wget ftp://ftp.software.ibm.com/ps/products/db2/fixes2/english-us/db2linux2632/client/runtime/FP15_MI00189_RTCL.tar
tar xvf FP15_MI00189_RTCL.tar
rtcl/db2_install -p DB2.RTCL
Why doesn’t IBM compress their DB2 releases? Gzip’s been around for 15 years - get your act together! I downloaded the archive to a Linode in the states and bzip’d it before downloading it here and saved 70mb.
The runtime client doesn’t set up users, so you’ll have to create a DB2 instance and user yourself.
mkdir -p /home/db2
adduser -m -d /home/db2/db2inst db2inst
passwd db2inst
/opt/IBM/db2/V8.1/instance/db2icrt db2inst
Then you need to set up db2inst’s db2cli.ini, which contains all the useful information on how to connect to the remote DB2 instance.
vim /home/db2/db2inst/sqllib/cfg/db2cli.ini
Here’s an example config (thanks IBM for not providing anything as vaguely useful):
[foo]
Database = FOO
Protocol = TCPIP
Hostname = 192.168.10.77
ServiceName = 50000
For some bizarre reason, ServiceName is the port number.
So now DB2’s CLI is set up, it’s time to do the ODBC manager. Setup the DB2 driver in
/etc/odbcinst.ini.
[DB2]
Description = IBM DB2 Adapter
Driver = /opt/IBM/db2/V8.1/lib/libdb2.so
FileUsage = 1
DontDLClose = 1
And finally, the ODBC source in /etc/odbc.ini.
[foo]
Description = example database connection
Driver = DB2
This is quite unlike normal ODBC source definitions in unixODBC. Because of IBM’s insistence on using db2cli.ini, you have to put all the relevant settings there, essentially turning odbc.ini into a wrapper for db2cli.ini. Apparently the db2cli allows you to put the settings just in odbc.ini and they’ll be passed through when the DB2 driver is called, but I could not get this to work.
Also worth noting is that the db2cli doesn’t allow DSN’s (the name of the ODBC source inside the []’s) longer than 8 characters. What the fuck. Are we living in the 80’s?
Regardless, to test the setup:
export DB2INSTANCE="db2inst"
isql -v foo db2inst password
Useful manuals for DB2 can be found here (the Call Level Interface Guide and Reference Vol.1 & 2 are particularly handy).
I hope someone else finds this useful. Hopefully this is my last post about DB2 for quite some time.
