How to connect Node.js to IBM DB2 database - Linux
Tags: ibm, db2, odbc, linux
Published: 2020-02-25 18:26:52

Operation system: Centos OS

# TOC
  * [Install ODBC driver on Linux](#section-driver)
  * [Install unixODBC package to access databases] (#section-package)
  * [Configure odbcinst.ini](#section-odbcinst)
  * [Configure odbc.ini](#section-odbc)
  * [Test the database connection](#section-test-db)


# Install ODBC driver on Linux <a name="section-driver"></a>

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](https://www.ibm.com/support/pages/node/633843) page and select [Downloads for IBM i Access Client Solutions](https://www-01.ibm.com/marketing/iwm/platform/mrs/assets?source=swg-ia). 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<a name="section-package"></a>

### 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 <a name="section-odbcinst"></a>
> 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
```bash
[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 <a name="section-odbc"></a>


> 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
```bash
[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 <a name="section-test-db"></a>

## 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"

------------


```javascript
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
```