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
Ubuntu/debian
apt-get install unixODBC
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
Ubuntu/debian
Download software as described above; we need to convert the .rpm into a deb.
Install alien to do that
apt-get install alien
Convert the package:
sudo alien -d iSeriesAccess-5.4.0-1.6.i386.rpm --scripts ## Then install sudo dpkg -i iseriesaccess_5.4.0-2.6_i386.deb
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 a lot 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 perl/php UTF-8 plus non-ascii chars
This is no longer needed! Kept for historic reasons
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.