Using perl/php with DB2, unixODBC and AS400

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
rpm -ivh iSeriesAccess-5.4.0-1.6.i386.rpm

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

If it fails regarding dependencies I had to download openmotif and install manually (yum crashed when trying to install the one in repo)

## 64bit
wget ftp://ftp.sunet.se/pub/os/Linux/distributions/centos/5.3/os/x86_64/CentOS/openmotif22-2.2.3-18.x86_64.rpm
## 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.x86_64.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.

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 
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);