Operation system: Centos OS

TOC

• Install ODBC driver on Linux
• Install unixODBC package to access databases
• Configure odbcinst.ini
• Configure odbc.ini
• Test the database connection

Install ODBC driver on Linux

Install instructions taken from here: https://github.com/IBM/ibmi-oss-examples/blob/master/odbc/odbc.md

To get the driver, visit the IBM i Access - Client Solutions page and select Downloads for IBM i Access Client Solutions. After logging in and redirected to the IBM I Access Client Solutions Download page, select the Download using http tab then scroll down and download the ACS Linux App Pkg.

In this package, there is a README that will help explain how to install the driver with either with RPMs or DEBs, depending on your Linux distribution. Just know that when you install the driver, it should pull in all of the packages you need to create an ODBC connection to Db2 for i from your Linux system.

Install unixODBC package to access databases

To get ODBC working we need to install the unixODBC package

The unixODBC package can be used to access databases through the ODBC
drivers.

Install unixODBC-devel rpm package:

# yum install unixODBC-devel

To verify whether unixODBC is installed, execute the following commands:

# which odbcinst
# which isql

configure odbcinst.ini

Verify the driver data stored in odbcinst.ini

file location: /etc/odbcinst.ini
The location of this file can also be found by running

# odbcinst -j

The odbcinst.ini file maps all of the files needed for the driver to operate properly. The odbcinst.ini usually looks like this. If your copy is missing information about IBM go ahead and add it

odbcinst.ini

[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[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

Configure odbc.ini

create ODBC.ini

File location for the SYSTEM DATA SOURCES:
*/etc/odbc.ini *

• The name of the DSN should be placed within the [] brackets

• The name of the driver specified in the Driver keyword must match the name of a driver defined in the odbcinst.ini.
  

vi /etc/odbc.ini

Copy and paste details below
press the "Esc" key, then type ":wq"

ODBC.ini

[usrProd]
Description            = IBM i Access for Linux ODBC data source
Driver                 = IBM i Access ODBC Driver
System                 = 192.168.1.225
Naming                 = 0
DefaultLibraries       = usrsadbtst
TrueAutoCommit         = 1
UserID                 = username1
Password               = password1

Test the database connection

Create node application

mkdir -p /var/www/node-apps/obdc_test
cd /var/www/node-apps/obdc_test
npm init -y

Install node ODBC package

npm install odbc


vi /var/www/node-apps/obdc_test/app.js

copy and paste details below
press the "Esc" key, then type ":wq"

const odbc = require("odbc");

const cn = "DSN=usrProd;UID=username1;PWD=password1";

odbc.connect(cn, (error, connection) => {
  connection.query(
    "SELECT * FROM QIWS.QCUSTCDT FETCH FIRST 6 ROWS ONLY",
    (error, result) => {
      if (error) {
        throw error;
      }
      console.log(result);
    }
  );
});

Run node application:

node /var/www/node-apps/obdc_test/app.js