Prerequisites: You’ve got to have PHP5 installed and running; this isn’t part of the tutorial.

Sure, it’s a highly specific title… but I’ve spent days pulling my hair out (and my hair is thinning, so I really can’t spare any) attempting to get Ubuntu to talk to a DB2 Database running on IBM iSeries.

Now, the IBM iSeries is not my machine… I don’t, nor do I want to, know anything about it. I only care about the PHP environment I’m working with.

So here’s how I got it done with a relatively vanilla installation of Ubuntu 12.04 LTS.

Step 1 – Install PHP ODBC and Alien

This is the first of the many (seriously, there aren’t THAT many steps) steps to get it running. Get into BASH and then run

$ sudo apt-get install php5-odbc alien

We’re installing alien because IBM provide a “Linux compatible” version of the iSeries Access ODBC Driver, but it’s only available as a Red Hat/CentOS package… [facepalm]

Step 2 – Download the RPM (whaaaat?)

Yes, an RPM…

Go create yourself an IBM account then visit https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=ial. I used IBM i Access for Linux V7R1, and then the “For Intel-based Linux workstations”… if you choose differently, you’re on your own.

If you’ve made it this far, congratulate yourself on having an attention span longer than a 1 year old child. It’s really nothing to be too satisfied about, so punch yourself in the nose and get back to it!

Step 3 – Convert and install the RPM

You will quite probably get an error that odbcinst is unable to be run, so prior to installing the RPM, go ahead and execute

$ sudo apt-get install odbcinst

Good job! (joking) Now, the highly complex task of converting an RPM to a .deb file and installing it… wait for it!

$ sudo alien -i -c iSeriesAccess-7.x.x-1.0.i386.rpm

NOTE: You need to change out the version numbers with whatever the RPM is called that you downloaded.

We also need to create symbolic links from the default Ubuntu lib directory to point to the (potentially 64-bit) provided by the IBM installation.

sudo ln -s /opt/ibm/iSeriesAccess/lib64/libcwb* /usr/lib

Huzzah! We’re nearly there.

Step 4 – Create the DSN

So, this last step kept me entertained (read: suicidal) for a couple of days. Because I come from a background of “conventional” PHP development (using MySQL/Postgres for the RDBMS) I kept thinking I should just be able to set up my connection with the following style of string

$conn = new PDO('odbc:Driver={iSeries Access ODBC Driver};System=db.domain.com;Database=myDb;Dbq=SOMELIB;Naming=1;', 'username', 'password');

Unfortunately, I got this error

[unixODBC][Driver Manager]Data source name not found, and no default driver specified

Theoretically, this should be possible. But I gave up trying and concluded that attempting to set up a System DSN was probably better. This involved setting up an odbc.ini file for the entire system. This box was our dev machine, and used only for development… connecting to the DEV database… so it makes sense, all users running on this box should be able to use ODBC with a DSN of myDBdev and instantly connect to it.

I set up the /etc/odbc.ini file thusly

[myDBdev]
Description = iSeries Access ODBC Driver DSN for iSeries
Driver = iSeries Access ODBC Driver
System = db.domain.com
UserID = myDbUser
Password = myDbPassword
Naming = 1
DefaultLibraries = SOMELIB
Database = myDb

This should fix all problems and we should be able to down a golden ale in celebration… fire off the php script. In my case I had a test script set up with the contents

// odbc.php
try {
    $conn = new PDO('odbc:myDBdev'); // Note: The name is the same as what's in our square brackets in ODBC.ini
    $stmt = $conn->prepare("SELECT * FROM myTable WHERE id = :id");
    $stmt->execute(array('id' => 123));
    while ($row = $stmt->fetch()) {
        print_r($row);
        echo '
';
    }
} catch (PDOException $e) {
    echo $e->getMessage();
}

Then I simply called the script from the command line with

$ php odbc.php

Then… then… then…

Array(
[ID] => 123,
[0] => 123,
[CURDSC] => My description,
[1] => My description,
[SOMEVAL] => Some value,
[2] => Some value
)

I could have hugged a cactus, I was so happy! All I have to do now is set up the same ODBC connections with different DSN’s (test_db, stage_db, production_db) and all of my deployment systems will be amazeballs.

You’re welcome.