In this article I will be looking at database access using Node.js on our favourite platform the IBM i.

A bit of background info first.

One of the first applications we wrote in Node.js had to access an audit log that was stored as a traditional table, physical file.  It had to both read and write to this table.

Options for using a method to access this data from an open source language were quite limited at this time.

We decided to use a node package called idb-pconnector which was developed by IBM. Thank-you IBM it worked well.

If you need any further information about idb-pconnector it can be found at https://www.npmjs.com/package/idb-pconnector

This package worked fine for our first fully blown node.js application, but we were young in those days and had hair too!

The problem we had going forward was this application had to be solely developed on the IBM i as the IBM module would only install and run on the IBM i.

And this is where ODBC comes in.

If we swap out where we use the original idb-pconnector, for an ODBC connector, we could then develop locally on our PCs and it would be compatible with the IBM i. 

All we would need to do would be change the connection from one machine to the other. Ah, that will make life easier!

Installation of ODBC Driver on PC

Before we go any further, we need to install the ODBC driver for my PC (Windows 10).  This is part of the IBM Access for Client Solutions (ACS) package.

A bit of background about the ACS product.  The IBM ACS package is split into two parts, the main, base package is filled with all the goodies we use day to day, 5250 emulation, Run SQL scripts etc.  The other part, which we need is Operating System specific. 

I need the Windows Application Package, of which the ODBC driver is included.  This can be downloaded from IBMs main ACS page found at https://www.ibm.com/support/pages/ibm-i-access-client-solutions

Once downloaded, run the install program, which hopefully completes without any errors.

To test it has installed all the ODBC components, use windows search feature to find ODBC administration.

Under the driver tab, you should see three IBM drivers.  The one I have highlighted is the recommended driver that IBM states you should use.  The others are for historical purposes and should not be used.

Create a DSN (Data Source Name)

We now need to create a data source name that points to our IBM i.  This can be seen in the figure below where Galatea is the name of our i server.

That completes the ODBC configuration on my PC.

Testing Local PC Access

To test the install of this driver, I wrote a quick node program that displays our employee table.

It doesn’t do much, just connects using the ACS ODBC driver we saw above and runs a very simple SQL SELECT statement, which outputs the rows in a debug window.

Let me explain the coding below.

1. Use the ODBC package from NPM
2. Use the DEBUG package from NPM to help explain this script
3. Make a connection object stating the name of the data source
4. A quick debug, no need to explain here!
5. Make an ODBC connection using the connection object from step 3
6. Check for any connection problems
7. Build our SQL SELECT statement and execute it
8. Check for any SQL errors
9. Output the results from the SQL statement in step 7

And the results are in!

Installation of ODBC on IBM i

This step is performed in two stages.

Firstly we have to install the ODBC driver for the IBM i.  This is provided as part of the ACS downloads at the same location we used for the windows ODBC package, but this time we need to select the ACS PASE Application Package.  This was called IBMiAccess_v1r1_PASE_AP.zip

Unpack this file to a directory on the IFS, open a shell session into that directory, then we run the installer, in this case we use YUM.

This can be seen in the figure below.

All installed.

If you wish to do any development with the ODBC drivers, you also need the development package.  This can be installed via yum also.

yum install unixODBC-devel

Now we have everything installed, let me show you how to use it.

Configuring ODBC on IBM i

Just like we did for windows, we have to configure ODBC.

To see where the system stores the ODBC configuration files, run the odbcinst -j command.

Using the Nano file editor, I will now create a user DSN in my home directory, called .odbc.ini

nano .odbc.ini

Using ODBC on IBM i

Now we are ready to test.

As part of the ODBC installation, IBM has provided a useful utility to test our connections.  This is called isql

We just run the command with the name of the DSN we created earlier (Galatea) and it will provide a SQL window.  Here we can enter a SQL statement to test our setup.

isql galatea

All looks good to me, here we can see all the rows of the table QIWS/QCUSTCT

Input QUIT to exit the isql utility.

Conclusion

Hopefully, this article has given you a quick insight into how we can easily access our DB2 data on IBM i using the ODBC connectivity method.  It really has made life easier here as an IBM i ISV.  It is highly recommended.

If you have any questions, either on this article, or anything else open source, use the comments below, or send me a message on twitter @AndyYouens

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience. 

IBM Champion 2021