Connecting to an IBM AS/400 DB2 Database

Question

I'm trying to connect to a client's IBM AS/400 DB2 Database from an Ubuntu Server using PHP's ODBC Driver. I have the unixODBC installed as well. My odbcinst.ini looks like this:

[IBM DB2 ODBC DRIVER]
Description = ODBC 5.1 Driver for Database
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
FileUsage = 1

And my odbc.ini looks like this:

[IBM DB2 ODBC DRIVER]
Driver = IBM DB2 ODBC DRIVER
Description = ODBC 5.1 Driver DSN

Now, my code to connect is:

$server = '12.345.678.90' //IP
$port = '446' //PORT
$username = 'my_username';
$password = 'my_password';

$connect = odbc_connect("DRIVER = {IBM DB2 ODBC DRIVER};System=$server:$port;Uid=$username;Pwd=$password;", $username, $password);

if(!$connect)
    echo 'Cannot Connect!';
else
    echo 'Connected!';

The error I get is this:

Warning: odbc_connect(): SQL Error: [unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user 'my_username'@'localhost' (using password: YES), SQL state S1000 in SQLConnect

I tried using the PDO ODBC Driver also. This is the error I get:

$connect = new PDO("odbc:DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME=$server;PORT=$port;Uid=$username;Pwd=$password");

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] SQLDriverConnect: 1045 [unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user 'my_username'@'localhost' (using password: YES)' in /var/www/test_file.php Stack trace: #0 /var/www/test_file.php: PDO->__construct('odbc:DRIVER={IB...') #1 {main} thrown in /var/www/test_file.php

Am I doing something wrong here? Do I need to use some other driver, because the username and password are correct, I saw the client log in to the database using the username and password I have. I thought the username and password were wrong because it says Access Denied for user. It doesn't seem to be the case. There might be something else that's wrong.

Thank you for your help. I hope I made the problem very clear. Thanks!

Solution 1

Your odbcinst.ini file is saying to use the MySQL ODBC driver:

Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so

but you need to use the iSeries Access ODBC driver. The reason you're getting an Access Denied for User message is because you're trying to connect to your MySQL database with credentials for the IBM i.

Here are step by step instructions for how to connect to DB2 for i (on the IBM i) on Ubuntu:

Download the free iSeriesAccess-6.1.0-1.2.i386.rpm file from IBM (you'll have to create a free account to get it - and I'm sure there is a more recent version than 6.1.0-1.2)

Convert the RPM file to something Ubuntu understands: sudo alien iSeriesAccess-6.1.0-1.2.i386.rpm

Install the resulting .deb: sudo dpkg -i iseriesaccess_6.1.0-2.2_i386.deb

Copy the installed iSeries libraries to where Ubuntu expects them: sudo cp /opt/ibm/iSeriesAccess/lib/* /usr/lib

Edit the /etc/odbc.ini file to contain:

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

Edit the /etc/odbcinst.ini file to contain:

[iSeries Access ODBC Driver]
Description     = iSeries Access for Linux ODBC Driver
Driver          = /usr/lib/libcwbodbc.so
Setup           = /usr/lib/libcwbodbcs.so
NOTE1           = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2           = the following Driver64/Setup64 keywords will provide that support.
Driver64        = /usr/lib/lib64/libcwbodbc.so
Setup64         = /usr/lib/lib64/libcwbodbcs.so
Threading       = 2
DontDLClose     = 1
UsageCount      = 1

And then to create the connection with PDO:

$pdo = new PDO("odbc:DRIVER={iSeries Access ODBC Driver};SYSTEM=$server;PROTOCOL=TCPIP", $username, $password);

Solution 2

I had to get IBM/DB2 access working with ODBC / PHP on a Ubuntu box recently. You can probably adapt this for most other distros: Here's how I got it working:

[1] Download one of the following, depending on your required DB2 version and architecture

iSeriesAccess-5.4.0-1.6.i386.rpm
iSeriesAccess-5.4.0-1.6.ppc.rpm  
iSeriesAccess-5.4.0-1.6.ppc64.rpm
iSeriesAccess-5.4.0-1.6.x86_64.rpm
iSeriesAccess-6.1.0-1.0.i386.rpm
iSeriesAccess-6.1.0-1.0.ppc.rpm   
iSeriesAccess-6.1.0-1.0.ppc64.rpm
iSeriesAccess-6.1.0-1.0.x86_64.rpm

You can get this from the IBM website, or from here:

http://www.mmnt.net/db/0/0/public.dhe.ibm.com/as400 (According to feedback, this opens NSFW popups. It's not my site, sorry about that - if you have concerns, go with the IBM website).

Then install IBM as400 client Access using something like this:

sudo apt-get install alien libmotif3
cd /path/to/iSeriesAccess-6.1.0-1.0.i386.rpm
sudo alien -dckv iSeriesAccess-6.1.0-1.0.i386.rpm
sudo dpkg -i iseriesaccess_6.1.0-1.0_i386.deb

[2] Install Java (if needed)

sudo apt-get install sun-java6-jre

[3] Install UNIXODBC

sudo apt-get install unixodbc-bin

[4] Install PHP ODBC

sudo apt-get install php5-odbc

[5] Ldconfig Create a file /etc/ld.so.conf.d/iSeriesAccess.conf with this line:

/opt/ibm/iSeriesAccess/lib/

Then run the following:

sudo ldconfig

[6] Register driver

sudo odbcinst -i -d -f /opt/ibm/iSeriesAccess/unixodbcregistration

[7] Test it with some PHP code

<?php
$user = 'SomeUser';
$pass = 'SomePass';
$Conn = odbc_connect("DRIVER={iSeries Access ODBC Driver};DATABASE=DBNAME;SYSTEM=127.0.0.1;HOSTNAME=127.0.0.1;PORT=446;PROTOCOL=TCPIP", $user, $pass);
if($Conn === false){
    die('failed to connect');
}
$sql = "select * from database.table fetch first 10 rows only";

$result=odbc_exec($Conn,$sql);

echo '<pre>';

while(odbc_fetch_array(($result)){
         print_r($result);
    }
}