Setting up DB2 for PHP on CentOS

Using a remote DB2 8.1 database with php 5.1.6 and Apache 2.2 on CentOS 5.4 (64 bit)

To download the IBM drivers for DB2, go to the IBM page and click on DB2 Client. If you don't have an account, you can request a free one. After logging in, download the IBM Data Server Client for linux. I also installed Express-C. I think this gave me some of the scripts I needed, though I'm not exactly sure. It was pretty self explanatory, download, extract, run the install script.

These instructions assume you've installed apache (httpd) via yum

# yum install php-pear
# mkdir /opt/ibm/db2/
# cp ./ibm_data_server /opt/ibm/db2/
# cd /opt/ibm/db2
# tar -xzf ibm_data_server
# cd dsdriver
# sh ./installDSDriver
# pecl download ibm_db2
# pecl install ./ibm_db2

when it asks for the installation dir, tell it: /opt/ibm/db2/dsdriver

Now we set up php to use the driver.

# vi /etc/php.ini

Find the section of Dynamic Extensions and add

extension=ibm_db2.so

To make sure it worked, run:

# php -i | grep -i db2

and you should get some results like:

ibm_db2
IBM DB2, Cloudscape and Apache Derby support => enabled
Binary data mode (ibm_db2.binmode) => DB2_BINARY
DB2 instance name (ibm_db2.instance_name) =>
PWD => /opt/ibm/db2/dsdriver
OLDPWD => /opt/ibm/db2/dsdriver/bin

.... and so on

So this is where I got stuck for a long time. At this point, db2_ commands will work from php, but it won't be able to find any database. All the documentation I read said things like "if you've created a db2 instance, point to it in your php.ini file" and stuff like that. It took me much longer to find how to actually create the instance.

Add the user db2inst1 (this is the default name php looks for an instance, but it can really be whatever.) I also put the user in a group I made (called db2grp1), but I don't think you need to.

# useradd db2inst1
# ./opt/ibm/db2/V8.1/instance/db2icrt db2inst1

This creates the instance, which basically adds sqllib and bin dirs to the home directory, and changes the .bashrc. I also don't know where this script came from, maybe from Express-C.

You can see all instances using:

# /home/db2inst1/sqllib/bin/db2ilist

If you want, you can go ahead and add this to the php.ini right now. I couldn't find anywhere that said where it had to be, so I just stuck it at the end before the end tag.

ibm_db2.instance_name=db2inst1

This will update the results when you do a php -i|grep -i db2 as well.

Now we have an instance, php knows where it is, but the instance doesn't know where the database is. If you're on a local database, it's easy, but I'm not so I have to configure the local instance to go find the remote db.
su to the instance user.

# su db2inst1
$ db2
=> catalog tcpip node remoteinst remote hostname.or.ip server 50000
=> catalog database testdb as remotedb at node remoteinst authentication server
=> terminate

This created the alias to the server and to the database. Everything in italics should be your information. To test this has worked type

=> connect to testdb user username using password

Your connection should succeed and tell you database information:

Database Connection Information

Database server        = DB2/6000 8.1.6
SQL authorization ID   = USERNAME
Local database alias   = TESTDB

If it fails, it should tell you why.

SQL30082N  Attempt to establish connection failed with security reason "24"
("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001

Now we're almost done (finally). We just need to put the alias info into the db2dsdriver.cfg where php looks for it. To do this, there's another fancy IBM script, yay! This one should be in /opt/ibm/db2/dsdriver/bin, assuming you followed my instructions up above.

# cd /opt/ibm/db2/dsdriver/bin
# ./db2dsdcfgfill -i db2inst1 -o /home/db2inst1/sqllib/cfg

That should fill in the db2dsdriver.cfg file. It took mine a few tries to succeed, but I don't think i did anything differently, so I'm not sure why it was failing. If it keeps failing, try moving the script to /home/db2inst1/sqllib. Maybe I did that to make it work.

One last thing before it'll work (and yes, I forgot this at first)

# httpd -k restart

That should be it. In php using the connection string, for the database, use the alias you created with the catalog commands.

<?php
$conn = db2_connect('testdb', 'user', 'password');
if (!$conn)  {
 echo "Connection failed.". db2_conn_errormsg();
}?>

Resources:
php.net
IBM Library
db2ude
sqlrelay
and 100 more google search results

Anomaly:
Since the db2dsdriver can only be updated (in my experiences) using

db2dscfgfill -i db2inst1 -o /home/db2inst1/sqllib/cfg

that means the db2dsdriver located in /opt/ibm/db2/dsdriver/cfg will need to be updated manually. Even though php knows the instance is db2inst1, it still seems to look in the opt location for the driver. When I updated the driver, I just copied and overwrote the one in opt, then restarted the httpd server and all was fine. Before I overwrote the old one, it wasn't finding the alias.