Using perl/php with DB2, unixODBC and AS400

Note

Be aware that if you want to use unixODBC with php/perl, you're bound to 32-bit and the fix outlined in the bottom. It's basically because of how ODBC and perl/php handles connections and data.

Install prereq software (Centos 5.3)

We need a bit of software installed to get up and running. Grab:

yum install unixODBC unixODBC-devel

iSeries Access for linux

This package contains the basics for connecting to an AS/400 via an 5250 emulator + unixODBC drivers and sql utils.

We need this to create/use our unixODBC. This is under IBM license, but otherwise a free download. You do need to register on their site tho. Grab from here:

http://www-03.ibm.com/systems/i/software/access/linux/

Install it:

## 32 bit
yum --nogpgcheck install iSeriesAccess-5.4.0-1.6.i386.rpm

## 64 bit (only ODBC - no terminal - only supported on 32bit)
yum --nogpgcheck install iSeriesAccess-5.4.0-1.6.x86_64.rpm

32bit only If it fails regarding dependencies I had to download openmotif and install manually (yum crashed when trying to install the one in repo). This is because of the terminal emulator which is absent in the 64bit version.

## 32bit
wget ftp://ftp.sunet.se/pub/os/Linux/distributions/centos/5.3/os/x386/CentOS/openmotif22-2.2.3-18.i386.rpm

## Install
yum install openmotif22-2.2.3-18.i386.rpm

ODBC Setup

Setup ODBC to reflect your DB-settings on the AS/400 run #odbcinst -j to see where your install files are located

This is my setup

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

Note

• [AS400] is the DSN (without the []) used when making ODBC connections

DefaultLibraries = QIWS is a test db which AS400 has. Use this to test connections/queries. Change back to QGPL when ready for live work.

Test connection

IBM has made an utility available which can test the connection to the AS400-server.

/opt/ibm/iSeriesAccess/bin64/cwbping <as400_ip>

should return alot of Success if configured correctly.

Test SQL connection

Another unixODBC tool to test the DSN/OBDC connectivity:

isql <DSNname> <user> <password>

Perl

If you want perl to connect via ODBC you need to install the DBD::ODBC module from CPAN.

## need perl-DBI if not installed already
yum install perl-DBI gcc
cpan 'DBD::ODBC'

This has unixODBC-devel as requirement, so if it fails, make sure you have that installed as well. Then try again.

Sample perl code to connect and retrive some data

#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect('dbi:ODBC:AS400', '<username>', '<password>');

&getTestData();
sub getTestData
{
        my $sSQL = "SELECT COUNT(*) FROM QIWS.QCUSTCDT";
        my $iCount = $dbh->selectrow_array($sSQL);
        print "Count :: $iCount\n";
}
$dbh->disconnect if ($dbh);

Patches for UTF-8 plus non-ascii chars

If you happen to use php/perl, UTF8, DBD::ODBC and any tools using these, there is a 'bug' with the ibm odbc interface. You'll most likely have alot of these:

[unixODBC][IBM][iSeries Access ODBC Driver]CWB0111 - A buffer passed to a system call is too small to hold return data (SQL-22018) [state was 22018 now 22018]

Read more here :: http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14210307

In essence you need to download the patch at the very bottom of the file and replace it with the one you in the system. Then change odbc slightly as well. Add a

Debug = 65536

Somewhere in the global odbc.ini file.

Now you can do SQL lookups again.