I had a similar problem. I finally found this post: https://adminramblings.wordpress.com/2015/02/27/odbc-from-linux-to-iseries-as400-for-php/ , which helped me install the correct drivers, configure them and connect to the database. I will include the information here just in case:
ODBC from Linux to iseries (AS400) for php
Posted February 27, 2015 | Posted by: Stephen Dart | Filed under: Uncategorized | leave a comment
Using linux (Ubuntu) to connect to IBM iSeries (AS400) to use php queries and reports
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 oderies oderies driver to the server and install
sudo dpkg -i ibm-iaccess-1.1.0.2-1.0.amd64.deb
symbolic link of libraries to the / usr / lib folder for use on the system
sudo ln -s /opt/ibm/iSeriesAccess/lib64/libcwb* /usr/lib
Now we can create an odbc configuration using the driver. Locate the SYSTEM files odbcinst.ini and odbc.ini:
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 installation already inside it as part of the driver installation, but the default file does not contain the [ODBC drivers] heading, which I found caused by problems, so it can be added to the beginning.
/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 odbc.ini for your specific system, it is important to create [ODBC Data Sources] and [DSN]
Odbc.ini example
[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 name in parentheses in odbc is the DSN, in which case the driver [DEV] in odbc.in must match the name specified in odbcinst.ini
System is the network name in fqdn or IP.
A user password and password are required to enter the server.
DefaultLibraries, Database and DefaultPkgLibrary can be specified as needed or left empty and specified above in php, I use the libraries DefaultLibraries.
Verify the connection using the isql command line and DSN.
isql -v DEV [unixODBC][Driver Manager]Can't open lib '/opt/ibm/iSeriesAccess/lib64/libcwbodbc.so': file not found
Oooh ERROR, this is not good ... but I found a fix!
There is a mismatch between the iseries driver and the unixodbc libraries for libodbcinst, which can lead to the above pointless 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 view the related dependencies 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)
As long as a fixed version of unixodbc is not installed in ubuntu packages (current version 2.2.14p2-5ubuntu5), then we can symbolically refer 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
An 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 restart isql and test.
isql -v DEV +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+
SQL
Woot related!
Now you have a working ODBC connection with your server, so you can use it through DSN and your applications.
The next step was to use php to reference this odbc.
Php page connecting DSN [DEV] using the select statement and putting the results in 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 installed both 32-bit and 64-bit versions of Ubuntu using this tutorial. You just need to configure several folder names if you want to install the 32-bit version. Hope this helps someone else.