View source for Using perl/php with DB2, unixODBC and AS400

[[Category:Linux]]

= 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:
<pre>
yum install unixODBC unixODBC-devel
</pre>

== Ubuntu/debian ==
<pre>
apt-get install unixODBC
</pre>

== 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:
<pre>
http://www-03.ibm.com/systems/i/software/access/linux/
</pre>

Install it:
<pre>
## 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
</pre>

'''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.
<pre>
## 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
</pre>

== Ubuntu/debian ==
Download software as described above; we need to convert the .rpm into a deb.

Install alien to do that
<pre>
apt-get install alien
</pre>

Convert the package:
<pre>
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
</pre>

= 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
<pre>
[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
</pre>
'''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.
<pre>
/opt/ibm/iSeriesAccess/bin64/cwbping <as400_ip>
</pre>
should return alot of Success if configured correctly.

== Test SQL connection ==
Another unixODBC tool to test the DSN/OBDC connectivity:
<pre>
isql <DSNname> <user> <password>
</pre>

= Perl =
If you want perl to connect via ODBC you need to install the DBD::ODBC module from CPAN.
<pre>
## need perl-DBI if not installed already
yum install perl-DBI gcc
cpan 'DBD::ODBC'
</pre>
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 ==
<pre>
#!/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);
</pre>

== 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:

<pre>
[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]
</pre>

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

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

<pre>
Debug = 65536
</pre>

Somewhere in the global odbc.ini file.

Now you can do SQL lookups again.