Faggruppe PHP

DB2 is one of IBM’s database server systems, it comes in many flavours, and runs on many platforms including Linux and Windows, but traditionally on IBM’s own mainframes. DB2 has a long history, so if you’re in the business chances are you’ll need to work with it one day.

We bumped into it when we had to connect to said database across both platforms and network. Here’s an account of our experience with it.

The problem
A web application in a LAMP environment (Ubuntu Hardy, Apache 2, MySQL5 and PHP 5.2), where login should be done using a centralised database – the already mentioned IBM DB2, on IBM System i.  As with DB2, System i has a long history, so don’t be surprised if it’s referred to by previous names such as AS/400, or eServer iSeries.

The solution
Our first attempt involved installing DB2 express on the same server as the web application, by using the ibm_db2 driver we got a connection that worked, but we were denied access with the following message:

An attempt to connect to a host failed due to a missing DB2 Connect product or invalid license. SQLSTATE=42968

DB2 Connect is apparently part of some DB2 products, but not DB2 express. A long call to IBM eventually established that we probably needed to pay for this approach to work.

Second attempt

Luckily there is another way to connect to DB2 – using ODBC. Which means we need an ODBC driver for DB2. IBM calls System I Access a connectivity product, and its web page goes on to say: “The entire focus of System i Access is to deliver the strengths and capabilities of System i to the desktop through easy-to-use screens and wizards”. In short, it’s a bundle that includes a 5250 emulator, an EDRS driver, and what we need: an ODBC driver.

•  Get ODBC: We need an ODBC manager and PHP support for it. Download and install it by apt getting unixodbc, unixodbc-bin, php5-odbc and libstdc++5.
•  Get System i Access: We also need an ODBC driver for DB2, available for dowload free of charge as an RPM package from http://www-03.ibm.com/systems/i/software/access/linux/index.html. Remember to choose the correct version for your system (32-bit or 64-bit). To install it you can for instance run the following:

 $ sudo rpm -ivh –nodeps iSeriesAccess-5.4.0-1.6.i386.rpm

It will be set up under the catalogue /opt/ibm/

The ODBC manager needs to know about the odbc driver and what to say to it. This is configured in two files: odbc.ini and odbcinst.ini. If they haven’t already been created by installing System i Access, they should be created in the /etc folder.

odbc.ini should look something like this:

[DB2CONN]
Description             = iSeries Access ODBC Driver
Driver                  = iSeries Access ODBC Driver
Driver64                = iSeries Access ODBC Driver 64-bit
System                  = 127.0.0.1
UserID                  = ELVIS
Password                = PRICILLA
Naming                  = 0
DefaultLibraries        = HAWAII,QGPL,qiws
Database                = GRACELAND
ConnectionType          = 0
CommitMode              = 2
ExtendedDynamic         = 1
DefaultPkgLibrary       = QGPL
DefaultPackage          = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression    = 1
MaxFieldLength          = 32
BlockFetch              = 1
BlockSizeKB             = 128
ExtendedColInfo         = 0
LibraryView             = 0
AllowUnsupportedChar    = 0

Each connection has its own ini section. The following parameters are the ones you are most likely to change:

• Driver: has to match a section in odbcinst.ini, which we’ll get to shortly.
• System: The ip adress to where the db2 is
• UserID
• Password
• DefaultLibraries: QGPL and qiws are standard, but most likely you need extra libraries
• Database: The name of the database you want to connect to

odbcinst.ini should look something like this:

[iSeries Access ODBC Driver]
Description     = iSeries Access for Linux ODBC Driver
Driver          = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup           = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
NOTE1           = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2           = the following Driver64/Setup64 keywords will provide that support.
Driver64        = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64         = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading       = 2
DontDLClose     = 1
UsageCount      = 1

[iSeries Access ODBC Driver 64-bit]
Description     = iSeries Access for Linux 64-bit ODBC Driver
Driver          = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup           = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading       = 2
DontDLClose     = 1
UsageCount      = 2

Notice there’s no mention of port anywhere. You cannot configure this, the port is 8471 so make sure it’s open in both ends. Also note that the Driver and Driver64 parameters in odbc.ini matches the two sections in odbcinst.ini

Using the connection
The setup should be up and running now, so to use it in PHP we need to create a connection. A simplified connection can look something like this:

function getConnection()
{
$dsn             = sfConfig::get('db2_conn_dsn', 'DB2CONN');
$user            = sfConfig::get('db2_conn_usr', 'ELVIS');
$pwd             = sfConfig::get('db2_conn_pwd', 'PRICILLA');

$connection = odbc_connect($dsn, $user, $pwd) or die ("Can not connect to $dsn.");

return $connection;
}

An example of a simple login query that uses a stored procedure from the hawaii library:

function checkUserNameAndPassword($username, $password)
{

$conn            = getConnection();
$user            = false;
if ($conn)
{
$sql       = utf8_decode("{CALL HAWAII.LOGIN('$username', '$password')}");
$callstore = odbc_exec($conn, $sql) or die ("Can not perform query");
$user    = odbc_fetch_array($callstore, 1);

odbc_close($conn);
}
return $user;
}

For debugging purposes, a useful way to output the result from an odbc_exec can be found in one of the comments at: http://no.php.net/odbc_exec

Here’s a simplified example of how to insert data using a prepared statement:

function insertUser($user)
{

$conn = getConnection();

if($conn)
{
$sql  = utf8_decode("INSERT INTO HAWAII.ADDUSER (FNAME, LNAME, ADR1, CITY)");
$sql .= utf8_decode(" VALUES (?,?,?,?)");
$res  = odbc_prepare($conn, $sql) or die ("Can not prepare query");
$parameters = array(
utf8_decode($user['firstname']),
utf8_decode($user['lastname']),
utf8_decode($user['address']),
utf8_decode($user['city']));
if(!odbc_execute($res, $parameters)) die("Can not perform query");
odbc_close($conn);
}

return true;
}

You can also insert without using the prepare statement (by using odbc_exec), but we had trouble with character encoding. The example above worked for us.

Good luck!