Using PHP/NGINX and ODBC to connect to IBM i from Linux

We decided to test out the ability to connect to the IBM i using a NGINX Server with php-fpm and ODBC. We already run NGINX with php-fpm for connecting to our IBMi but we use Easycom as the glue that makes it work. Easycom is a chargeable product so while we feel its something that is far more capable than what we will test here, we do understand people may be a bit reluctant to use it. ODBC is free and can be installed on many operating systems so it makes sense for some to use for their connectivity.

We are doing this on Linux, but with a little bit of configuration manipulation it can be set up on any platform. Our test will be very simple in content but it will hopefully show just what needs to be done to get it set up and running. the OS we are using is Debian 9. As I have already said we already have the various elements required to run NGINX/PHP and Easycom and ready configured and running, all we will show here is how we configured a new site ready to run ODBC connectivity to the IBM i.

First of all there is not a lot of information on the web explaining what needs to be done, IBM does provide the drivers as part of the IBM i Access solutions but the documentation did not explain how to go about configuring it etc.

First of all we need to get the ODBC driver from IBM. It is packaged as part of the ACS Linux App Package which can be downloaded from IBM. Here is a link to the starting page https://www-01.ibm.com/support/docview.wss?uid=isg3T1026805 once there you will need to select the link to the Downloads for IBM i Access Client Solutions (you will need to sign in and accept the conditions before being redirected to the actual downloads page.). Select the ACS Linux App Package and download to your PC.

Before you can install the package we suggest you install the unixodbc driver first. That can be installed using the following command on debian. Use the appropriate command for other Linux variants

apt-get install unixodbc

Now we need to unzip the download from IBM and transfer the odbc driver we need for our system. Extract the zip file and go to the x86_64 folder, we needed ibm-iaccess-1.1.0.12-1.0.amd64.deb file for our system, select the one you need and transfer to your Linux System. We copied the file to my home directory so we signed in via ssh (putty) and changed to superuser with the su command. Then we ran the following command to install the package.

dpkg -i ibm-iaccess-1.1.0.12-1.0.amd64.deb

If there are any problems due to missing dependencies you will need to fix them up and then run the following (we had a missing lib).

apt –fix-broken install

Now we have all of the required elements to set up ODBC. There are some important files that need to be updated depending on how you want to run ODBC, in our case we are just going to use hard coded user names and passwords for the test but you can set up various DSN’s to allow many other options. First file we updated was the /etc/odbc.ini file this is where the default DSN is located.

[*LOCAL]
Description = sas5 connecton
Driver = IBM i Access ODBC Driver
System = sas5.shield.local
UserID =
Password =
Naming = 0
DefaultLibraries = CHLIB HA4I73
TrueAutoCommit = 1

The Driver is the important part to get right, if you do not have the same name defined as you have in the available drivers, ODBC will not connect. To find out the correct name look in the file /etc/odbcinst.ini where you should see the drivers available. This is how ours looked.

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

You can see that the top entry defines both drivers (64 and 32 bit) so we used the 32bit as the driver name in our file. You should also check that the module is enabled for the php-fpm instance if you are going to use php to access the drivers, ours was already enabled after the install but only for the 7.3 php-fpm instance? Not sure why the others were not updated during the install (we have various versions of php installed)?

/etc/php/7.3/mods-available/odbc.ini.

As we have added a new module to php-fpm we need to restart the service.

service php7.3-fpm restart

To test the connectivity we used the isql command which is installed with unixodbc. Here is the command we used (userid and password need to be replaced with a userid and password that is available on the IBM i with sufficient authority.

isql -v *LOCAL userid password

You should see something similar to this. type in quit and press enter if you do, if not there is something wrong and that needs to be fixed before going any further.

+---------------------------------------+
 | Connected!                            |
 |                                       |
 | sql-statement                         |
 | help [tablename]                      |
 | quit                                  |
 |                                       |
 +---------------------------------------+
 SQL> 

To test with php and NGINX we created a new server instance in the NGINX configuration. The NGINX implementation on IBM i (if you have used it) uses a single configuration file for the setup, in Debian they use a separate file for each server and symbolic links to allow enabling and disabling sites. We tried this on IBM i and it did not work, so this is how we set up on the Debian system.

First we created a new virtual host called ha4i73-linux.shield.local, we added a DNS entry to our DNS server and had the following content in the file /etc/nginx/sites-available/ha4i73-linux.shield.local.

server {
listen 80;
server_name ha4i73-linux ha4i73-linux.shield.local;
root /var/www/html/ha4i73;
index index.php;
location ~ .php$ {
include snippets/fastcgi-php.conf;
fastcgi_pass unix:/var/run/php/php7.3-fpm.sock;
}
location / {
try_files $uri $uri/ =404;
}
}

The content which will be served is in /var/www/html/ha4i73. we had a number of test pages but the one used for the test was called odbc-test.php.

Note: in the following text the connection string was split to fit the page width. We have changed the userid and password to protect the innocent.

<html>
    <head>
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta charset="UTF-8">
        <link rel="stylesheet" href="css/gen.css" type="text/css">
        <title><?php echo($page_title); ?></title>
    </head>
    <body>
        <?php
        $connection_str = "DRIVER=IBM i Access ODBC Driver;SYSTEM=sas5.shield.local;UID=userid;PWD=password;";
        $conn = odbc_connect($connection_str,'','');
        if(!$conn) {
            echo("Connection failed\n");
            return;
        }
        $sql = "select * from HA4I73.JRNCFG";
        $rs = odbc_exec($conn,$sql);
        if(!$rs) {
            echo("Error in SQL\n");            
        }
        while($row = odbc_fetch_array($rs)) {
            print_r($row);
        }
        odbc_close($conn);
        ?>
    </body>
</html>

The above request simply runs the SQL to pull back the Remote Journals that we have configured in our High Availability product instance we are testing with. There is only a single line so this is what we saw in the web page.

Array ( [RJNAME] => BATCHJRN [RJLIB] => BATCHRMT [RRCVRLIB] => BATCHRMT [LJNAME] => BATCHJRN [LJLIB] => B_JRN_LIB [LRCVRLIB] => B_JRN_LIB [RJMSGQ] => JRNMSGQ [RJMSGQLIB] => B_JRN_LIB [LSTAPY] => BATCH00000BATCHRMT [ERRLOG] => [DQ] => HA4IDQ0000 [CFGSYS] => SAS5 [RSWAP] => Y [RTVJRNEDLY] => 5 [RCVRHLD] => 2 [APYBACKLOG] => 500 [MAXRCVRS] => 0 )

Exactly as we expected. So this shows how you can run web services on a Linux NGINX server with php-fpm to collect data from a remote IBM i server using the Linux ODBC driver supplied by IBM.

The next post will look at how we can use odbc.ini file in a user directory to determine what is passed through the ODBC driver instead of using the fixed $connection_str parameter above.

Chris…