Ingredients : PHP, DB2 and unixODBC

In this post, we are going to do a recipe mixing those few ingredients: PHP, DB2 and unixODBC.

One of the project in my new job in Rome is to develop an application in PHP that works with an AS/400 DB2 database. I’ve been working with PHP and databases such as MySQL for quite some years now, instead, it has been my first encounter with DB2, so the challenge was interesting.

I did some search before even starting anything, and it seems there is mainly 2 ways of using DB2 with PHP : 1. install a IBM DB2 client on the computer where PHP is and build the ibm_db2 extension. 2. use DB2 with ODBC and the unixODBC drivers. This article explains how to make unixODBC to work with an AS/400 DB2 database on a Linux (CentOS) machine to access it with PHP.

Installation

The process isn’t really straighforward, but here is the working solution i have on 2 CentOS servers. We’ll install unixODBC in the CentOS repositories and the RPM of iSeriesAccess for linux (centos 5.5) from IBM website. But like this, the normal RPM installation will return missing dependencies, partly because Openmotif is missing, so, let’s install it now as well :

# yum install unixODBC openmotif

iSeriesAccess needs libXm.so.3 and Openmotif provided libXm.so.4, we can create a symbolic link like this :

# ln -s /usr/lib/libXm.so.4 /usr/lib/libXm.so.3

Now, iSeriesAccess can be downloaded and installed (without the dependencies checking) :

# wget http://www-03.ibm.com/systems/i/software/access/linux/index.html
# rpm --nodeps -ivh iSeriesAccess-6.1.0-1.2.i386.rpm

Configuration

unixODBC consists of 2 configuration files under /etc/, odbcinst.ini that should be okay by default after the installation of iSeriesAccess, and odbc.ini that will hold the DB2 instance. Very little documentation can be found about this, i finally found something good on this site. The following is an exemple of my odbc.ini working with a MY_DB_NAME library :

[MY_DB_NAME]
Description = iSeries Access ODBC Driver DSN for iSeries
Driver = iSeries Access ODBC Driver
System = 192.168.1.1
UserID = my_user_id
Password = my_password
Naming = 0
DefaultLibraries = MY_DB_NAME
Database = MY_DB_NAME
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

The options have been left to their default values, except obviously for System, UserID, Password, DefaultLibraries and Database.

ODBC and PHP

Prior to dialogue to the DB2 database with PHP, the php-odbc module needs to be added :

# yum install php-odbc

That’s all, now it should work. Here is a quick sample code to try it out (provide a real SQL query ^.^) :

# DB2 instantiation
if (!$db = odbc_connect ( "MY_DB_NAME", "my_user_id", "my_password") )
	echo 'error!';

# Querying
$result = odbc_exec($db, "SELECT * FROM the_table ");
while (odbc_fetch_row($result)) {
	echo odbc_result($result, "the_field"))."\n";
}

// closing db properly
odbc_close($db);

7 thoughts on “Ingredients : PHP, DB2 and unixODBC”

  1. I found that on my linux64 I was getting the error:
    Can’t open lib ‘/opt/ibm/iSeriesAccess/lib64/libcwbodbc.so’ : file not found
    Found that I had to add /opt/ibm/iSeriesAccess/lib64/ to the LD_LIBRARY_PATH

  2. Unfortunately you missed the RPM details to get iSeriesAccess rpm, perhaps because it is a restricted program requiring some kind of license. At this point this was another waste of time!

    1. This is quite an old post. I don’t know how it is now, but everything was available – and free – at the time of the writing of this post

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *