PHP/Linux to AS/400-db2

Question

I am trying to get php on Linux Centos server access as/400 (iSeries) db2 database.

I am using this IBM guide as much as possible, (though we could not get GUI configuration utility working.)

http://www-03.ibm.com/systems/i/soft...ide/index.html

I downloaded and successfully installed iSeriesAccess drivers and pre-requisites.

rpm -i iSeriesAccess-5.4.0-1.6.i386.rpm

I have configured these files to define drivers/DNS:

/etc/odbc.ini and /etc/odbcinst.ini

[iSeries Access ODBC Driver]
Description          = iSeries Access for Linux ODBC Driver
Driver               = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Setup                = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
Driver64             = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64              = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading            = 2
DontDLClose          = 1
UsageCount           = 1 

file /etc/odbc.ini was empty so I added this configuration:

[AS400]
Description     = iSeries Access ODBC Driver
Driver          = iSeries Access ODBC Driver
System          = 172.999.999.999             (from netstat option 1)
UserID          = my_user
Password        = my_pass
Naming          = 0
DefaultLibraries  = QGPL
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           = 1
DSN             = AS400 

I assume these are working because I can run

isql -v AS400

and I connect successfully to db2 database can perform queries from Linux box.

However I have been unable to get make and ODBC connection in PHP on the Linux box. Is there another way to test DSN from php? or get more detailed error information?

$server="172.999.999.999";    
    // tried with both system name and "AS400", the dsn name
$user="my_user"; 
$pass="my_pass";

$conn=odbc_connect($server,$user,$pass);
if ($conn == false) {
  echo "Not able to connect to database...
";
}

result:

**Not able to connect to database...** 

phpinfo() shows that php was compiled with unixODBC and unixODBC is enabled.

any help is appreciated!

Answer 1

Try double checking your odbcinst.ini and odbc.ini config files. Do you have the correct database name/default library set in odbc.ini? I had success following these instructions:

http://werk.feub.net/2010/11/ingredients-php-db2-and-unixodbc/

One difference is that I found a version of openmotif that included libXm.so.3.

http://rpm.pbone.net/index.php3/stat/3/limit/2/srodzaj/1/dl/40/search/libXm.so.3/field[]/1/field[]/2

Restart apache after installing php-odbc.

/etc/odbc.ini

[ISERIES]
Description = iSeries Access ODBC Driver DSN for iSeries
Driver = iSeries Access ODBC Driver
System = 192.168.1.1
UserID = MYUSER
Password = MYPASSWORD
Naming = 0
DefaultLibraries = QGPL
Database =
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

Sample PHP:

<?php
if (!$db = odbc_connect ( "ISERIES", "MYUSER", "MYPASSWORD") )
    echo 'error!';

$result = odbc_exec($db, "SELECT * FROM MYUSER.TABLENAME");
while (odbc_fetch_row($result)) {
    echo odbc_result($result, "ID")."\n";
}

odbc_close($db)
?>

Answer 2

You need to use ODBC Source Name in odbc_connect() function, not the server IP. In your example AS400 is the name between brackets at the top of odbc.ini file.

Answer 3

I had a similar issue. I finally found this post: https://adminramblings.wordpress.com/2015/02/27/odbc-from-linux-to-iseries-as400-for-php/ that helped me install the correct drivers, configure them and connect to the database. I'll include the information here just in case:

---

ODBC from Linux to iseries (AS400) for php

Posted: February 27, 2015 | Author: Stephen Dart | Filed under: Uncategorized |Leave a comment

Using a linux (Ubuntu) box to connect to a IBM iSeries (AS400) to use php queries and reporting

IBM ODBC driver (login required) http://www-03.ibm.com/systems/power/software/i/access/linux/guide.html

Install apache with php and odbc modules.

sudo apt-get install libapache2-mod-php5 apache2 php5-odbc

Install unixodbc

sudo apt-get install unixodbc

Copy the downloaded iseries odbc driver to the server and install

sudo dpkg -i ibm-iaccess-1.1.0.2-1.0.amd64.deb

symbolic link the libraries to the /usr/lib folder for use for the system

sudo ln -s /opt/ibm/iSeriesAccess/lib64/libcwb* /usr/lib

We can now create the odbc setup using the driver. Find where the SYSTEM odbcinst.ini and odbc.ini files are:

odbcinst -j

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources

Edit the files, the odbcinst.ini file should have the IBM iseries driver setup already within it as part of the driver install, but the default file is missing the header [ODBC Drivers] which I found caused problems, so can be added to the top.

/etc/odbcinst.ini

[ODBC Drivers]
IBM i Access ODBC Driver
Description = IBM i Access for Linux ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
Driver64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading = 0
DontDLClose = 1
UsageCount = 2

[IBM i Access ODBC Driver 64-bit]
Description = IBM i Access for Linux 64-bit ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading = 0
DontDLClose = 1
UsageCount = 2

Now create the odbc.ini for your specific system, its important to create a [ODBC Data Sources] and the [DSN]

Example odbc.ini

[ODBC Data Sources]
DEV = DEV

[DEV]
Description = iSeries Access ODBC Driver
Driver = IBM i Access ODBC Driver
System = FQDN or IP
UserID = USER
Password = PASSWORD
Naming = 1
DefaultLibraries = QGPL
Database =
ConnectionType = 2
CommitMode = 2
ExtendedDynamic = 1
DefaultPkgLibrary =
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 1
ForceTranslation = 1
Trace = 0

The bracketed name in the odbc is the DSN, in this case [DEV] Driver in odbc.in needs to match the name specified in odbcinst.ini

System is the network name in fqdn or IP.

UserID and Password are as required to login to the iseries.

DefaultLibraries, Database, and DefaultPkgLibrary can be specified as required or left blank and specified higher in the php, I only use the DefaultLibraries.

Test the connection using the commandline isql and the DSN.

isql -v DEV
[unixODBC][Driver Manager]Can't open lib '/opt/ibm/iSeriesAccess/lib64/libcwbodbc.so': file not found

Oooh ERROR, thats not good… but I have found a fix !

There is a discrepancy between the iseries driver and the unixodbc libraries for the libodbcinst which can result in the above meaningless error when using the driver.

The file is within the location but there is a problem with the actual library and the error message is not very clear.

To see the true error we need to use ldd to see the linked dependancies of libcwdodbc.so

ldd /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
linux-vdso.so.1 => (0x00007fff86dfe000)
libodbcinst.so.2 => not found
libcwbcore.so => /usr/lib/x86_64-linux-gnu/libcwbcore.so (0x00007f7f68545000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f7f68240000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f7f67f3a000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f7f67d24000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f7f6795d000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f7f6773f000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f7f6753b000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f7f67332000)
/lib64/ld-linux-x86-64.so.2 (0x00007f7f68b98000)

Until there is a fix packaged version of unixodbc within the ubuntu packages (current version 2.2.14p2-5ubuntu5) then we can symbolic link so.1 to so.2

sudo ln -s /usr/lib/x86_64-linux-gnu/libodbcinst.so.1 /usr/lib/x86_64-linux-gnu/libodbcinst.so.2

IBM article regarding this: http://www.ibm.com/developerworks/ibmi/library/i-ibmi-access-client-solutions-linux/

Running ldd now sees the required libraries.

ldd /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
linux-vdso.so.1 => (0x00007fff315fe000)
libodbcinst.so.2 => /usr/lib/x86_64-linux-gnu/libodbcinst.so.2 (0x00007fcef32ed000)
libcwbcore.so => /usr/lib/x86_64-linux-gnu/libcwbcore.so (0x00007fcef2f7a000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fcef2c75000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fcef296f000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fcef2759000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fcef2392000)
libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007fcef2188000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fcef1f6a000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fcef1d65000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007fcef1b5d000)
/lib64/ld-linux-x86-64.so.2 (0x00007fcef37df000)

Now we can rerun isql and test.

isql -v DEV
+---------------------------------------+
| Connected!                           |
|                                      |
| sql-statement                        |
| help [tablename]                     |
| quit                                 |
|                                      |
+---------------------------------------+

SQL

Woot, connected!

Ok now you have a working ODBC connection to your iseries, so you can use it via the DSN and your applications.

The next stage was to use php to link to this odbc.

Php page connecting to DSN [DEV] using a select statement and putting the results into a table:

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
</style>
</head>
<body>

<?php
try{
$as400conn = new PDO(‘odbc:DEV’); 
// Note: The name is the same as what’s in our square brackets in ODBC.ini
$as400conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$library = “as400 library”;
$file = “as400 file”;
$i = 0;
$fields[$i++] = “file field 1″;
$fields[$i++] = “file field 2″;
$fields[$i++] = “file field 3″;
$AryLength = count($fields);
// Create SQL Have to include first field for comma separate outside of the while loop. field,field
$sql = “SELECT ” . $fields[0] ;
for($x = 1; $x < $AryLength; $x++) {
$sql = $sql . “,” . $fields[$x] ;
}
$sql = $sql.” FROM ” . $library . “/” . $file ;
echo $sql;
echo “<br>”;
$result = $as400conn->query($sql);

// Print Table Header //
echo “<table><tr>”;
for($x = 0; $x < $AryLength; $x++) {
echo “<th> $fields[$x] </th>”;
}
echo “</tr>”;

// Output Data of each row
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo “<tr> “;
for($x = 0; $x < $AryLength; $x++) {
echo “<td>” . $row[$fields[$x]] . “</td>”;
}
echo “</tr>”;
}
echo “</table>”;
$as400conn = null;

//end of try
}
catch (PDOException $e) {
echo $e->getMessage();
}
?>
</body>
</html>

I've installed on both 32 bit and 64 bit versions of Ubuntu using this tutorial. You just have to tweak a few of the folder names if you want to install the 32 bit version. Hope this helps someone else.

Answer 4

indeed job log had this entry:

Mar 9 14:04:52 mtl setroubleshoot: SELinux is preventing the http daemon from connecting to network port 8471

I turned off SELinux and problem solved.

Thanks for the lead!