Support for ODBC was added in Profound.js version 5.3.0.

Overview

The Profound.js odbc database driver provides database connectivity through the Open Database Connectivity (ODBC) standard. It can be used with any database Profound.js supports, but it is primarily designed and tested for connecting to Db2 on IBM i. For the other supported databases, use the database-specific drivers instead.

Why Use the odbc Driver

Compared with the Profound.js IBMi driver, the odbc driver offers:

Better performance - Both for connections to a remote IBM i and for connections from IBM i to the local database;
Multiple systems - Connections to more than one IBM i system are supported. The IBMi driver only supports connecting to a single IBM i system;
Nothing to install on the target - The IBMi driver requires a Profound.js installation on the target IBM i system; the odbc driver does not.

The Profound.js odbc driver - Built into Profound.js starting with version 5.3.0. It relies on the two components below for connectivity to the target system;
The open source odbc package - Installed separately with npm. It provides the Node.js interface for using ODBC drivers;
The ODBC driver for the target system - Performs the actual communication with the target database. ODBC access to IBM i is provided by the IBM i Access ODBC Driver, supplied by IBM.

Installing the odbc Package With npm

Follow the package’s own installation instructions, including the prerequisites for your operating system: https://www.npmjs.com/package/odbc.

The odbc package is a native add-on for Node.js, which means it must be compiled from C++ into a native executable on the source system. npm manages the build, but the following tools must be present:

Python;
Visual Studio C++ Compiler (Windows);
GNU C++ compiler and GNU Make (other platforms).

If the package fails to install, confirm these tools are installed and configured. To install the build tools on IBM i:

yum install make-gnu python2 gcc-cplusplus

To install the build tools on Ubuntu Linux:

sudo apt install build-essential

Installing the IBM i Access ODBC Driver

The ODBC driver is provided as part of the IBM i Access Client Solutions Application Package. The Application Package is a separate installation from the main ACS installation that includes the TN5250 emulator. Versions are available for Windows, Linux, Mac, and IBM i.

Go to the IBM i ACS home page: https://www.ibm.com/support/pages/ibm-i-access-client-solutions. An IBM account is required to access the downloads;
Select Downloads for IBM i Access Client Solutions and accept the license agreement;
Use the ACS App Pkg link for your source operating system. For IBM i, use ACS PASE App Pkg. Installation instructions are included in each download package.

The IBM i version of the Application Package installs through the open source package manager yum. For instructions on setting up yum on IBM i, see https://www.ibm.com/support/pages/node/706903.

For Application Package installers for 32-bit Windows or for languages other than English, visit the IBM ESS website.

Configuration

Connections are configured with the databaseConnections property in the Profound.js configuration file. The example below defines a single pooled connection to an IBM i named myIBMi, authenticates with an encrypted credentials file, and sets a fixed pool of ten connections.

config.js - ODBC connection to Db2 for i

"databaseConnections": [
  {
    // Any unique name; used to select this connection from application code.
    "name": "myIBMi",
    "driver": "odbc",
    // Required for the odbc driver. "db2i" targets Db2 for i.
    "type": "db2i",
    // Encrypted user/password created by store_credentials.js. Requires encryptionKey.
    "credentialsFile": `${__dirname}/credentials`,
    "driverOptions": {
      // NAM=1, UNICODESQL=1 and TSFT=1 are required by Profound.js.
      // DBQ sets the library list (leading comma = no default schema).
      // CMT=0 disables commitment control.
      "connectionString": "DRIVER=IBM i Access ODBC Driver;SYSTEM=myIBMi;NAM=1;CMT=0;UNICODESQL=1;TSFT=1;DBQ=,MYLIB,MYLIB2,MYLIB3",
      // Open 10 connections at startup and never grow or shrink the pool.
      "initialSize": 10,
      "maxSize": 10,
      "shrink": false
    }
  }
]

A databaseConnections entry supports the following properties:

name (required) - A name for the connection. It can be anything you choose, but it must be unique within databaseConnections;
driver (required) - Set to "odbc";
type (required for odbc) - The target database type. One of "db2i", "mysql", "mssql", or "oracledb". This driver is primarily intended for Db2 for IBM i; for the other databases the database-specific drivers are recommended;
driverOptions (required) - An object containing options for the odbc package’s Pool constructor. See the npm page for supported options. The Profound.js odbc driver only supports pooled connections, and one pool is created for each databaseConnections entry;
credentialsFile (optional) - Path to a file containing an encrypted user id and password created by the store_credentials.js utility supplied with Profound.js. This is an alternative to putting the user and password in clear text on the connection string. Using it requires an encryptionKey to be configured, otherwise the instance will not start;
default (optional) - Set to boolean true to make this the default connection. Only one entry can be flagged as the default.

Connection String Options

ODBC driver options – such as which system to connect to – are configured with the connectionString property of driverOptions. Option names and values are separated by an equal sign (=), and each name/value pair is separated by a semicolon (;). Option names must be specified in uppercase.

For the options supported by the IBM i Access ODBC driver, see the IBM documentation.

In the configuration above, the library list is set to MYLIB MYLIB2 MYLIB3 with no default schema using the DBQ option, and commitment control is disabled with the CMT option. The following settings are required by Profound.js and should be set on all connections:

Option	Required Value	Purpose

Option	Required Value	Purpose
`NAM`	`1`	Use system (library) naming rather than SQL naming.
`UNICODESQL`	`1`	Send SQL statements to the server as Unicode.
`TSFT`	`1`	Use the ODBC timestamp format.

Creating a Credentials File

The example configuration above uses an encrypted credentials file. To create one, run the following command from your Profound.js installation directory and enter the desired user id and password when prompted:

node store_credentials.js

The credentials file is decrypted when the Profound.js server starts, and the user id and password are appended to the end of the connectionString like this:

;UID={decrypted_userid};PWD={decrypted_password};

When using this option, specify the connectionString without the UID and PWD options.

An encryptionKey must be configured before credentialsFile can be used.

Using ODBC Data Sources (Optional)

An ODBC Data Source is an alternative way of specifying ODBC connection options. A Data Source is an OS-dependent location – usually a file – where the connection options are stored. When a Data Source is used, only the Data Source Name (DSN) has to appear in the connectionString, and the rest of the options are loaded from the Data Source. Options can still be given on the connectionString alongside the DSN, in which case they override the ones from the Data Source.

The process of creating a Data Source depends on the source operating system.

Creating a Data Source on Linux, Mac, and IBM i

On these systems, Data Sources are stored in a text file named odbc.ini. The location depends on the OS, but is typically /etc/odbc.ini; on IBM i it is /QOpenSys/etc/odbc.ini. The file can be edited directly, but it is recommended to use the odbcinst command line utility to add and remove Data Sources. On IBM i, odbcinst can be run in a PASE shell.

To add a Data Source, create a file named template.ini anywhere on the file system and copy the following lines into it:

template.ini

[myIBMi]
Driver=IBM i Access ODBC Driver
System=localhost
Naming=1
CommitMode=0
UnicodeSQL=1
TimestampFormat=1

Make the following adjustments to the file:

Data Source Name - The text myIBMi inside the square brackets is the Data Source Name. Change it to the name you want, leaving the square brackets in place. Adding a Data Source replaces any existing Data Source with the same name, so check the existing DSNs first:
odbcinst -q -s
System - If connecting from off system to IBM i, change the System property to the host name or IP address of the target IBM i system. Otherwise leave it as localhost.

Other options can be added to the file as desired. When template.ini is ready, add the Data Source:

odbcinst -i -s -l -f template.ini

If the command completes successfully, the DSN is added to odbc.ini. Confirm it with:

odbcinst -q -s -n DATA_SOURCE_NAME

The Data Source configuration is written to the screen. At this point template.ini is no longer needed and can be removed.

The Data Source Name can now be referenced in the connectionString:

config.js - connecting through a Data Source

"databaseConnections": [
  {
    "name": "myIBMi",
    "driver": "odbc",
    "type": "db2i",
    "credentialsFile": `${__dirname}/credentials`,
    "driverOptions": {
      // DSN pulls the driver, system, and required options from odbc.ini.
      // Options given here override the ones stored in the Data Source.
      "connectionString": "DSN=DATA_SOURCE_NAME;DBQ=,DATALIB1,DATALIB2"
    }
  }
]

A Data Source can be removed with:

odbcinst -u -s -l -n DATA_SOURCE_NAME

Creating a Data Source on Windows

On Windows, ODBC Data Sources are stored in files in a directory of your choosing. To find the directory currently configured on your system, launch the ODBC Data Sources application by typing odbc data sources into the Windows search box and choosing the correct version for your Windows installation (usually 64-bit):

The directory for Data Source files is shown on the File DSN tab. This tab also has an option to change the directory, if desired:

To install a Data Source, create a text file with a .dsn extension in that directory. The file name becomes the DSN name. Copy these contents into the file:

DATA_SOURCE_NAME.dsn

[ODBC]
Driver=IBM i Access ODBC Driver
System=myIBMi
Naming=1
CommitMode=0
UnicodeSQL=1
TimestampFormat=1

Change the System property to the target IBM i system’s host name or IP address. Additional connection options can be added as desired.

The Data Source Name can now be referenced in the connectionString. Specify the file name as the value of the FILEDSN option, without the .dsn extension:

config.js - connecting through a file Data Source

"databaseConnections": [
  {
    "name": "myIBMi",
    "driver": "odbc",
    "type": "db2i",
    "credentialsFile": `${__dirname}/credentials`,
    "driverOptions": {
      // FILEDSN names the .dsn file, without the .dsn extension.
      "connectionString": "FILEDSN=DATA_SOURCE_NAME;DBQ=,DATALIB1,DATALIB2"
    }
  }
]

To remove or change a DSN, simply edit or delete the file.

Optimizing ODBC Pool Performance

When the odbc package has to create several pool connections at once – such as when establishing the initial set of pool connections – queries are held up until all of those connections complete. Establishing each connection takes time, especially when connecting to IBM i over a WAN, which can produce a long delay on a query. To mitigate this, Profound.js begins establishing the connection pool immediately as the server starts up. Delays can still occur if the pool is configured so that it grows by large increments.

For the full list of pool options, see the odbc package documentation.

The recommended configuration prevents the pool from growing once the initial connections are established:

Fixed-size pool (recommended)

"driverOptions": {
  "connectionString": "DSN=myIBMi;",
  // Open all 10 connections up front...
  "initialSize": 10,
  // ...and never open more than that.
  "maxSize": 10,
  // Keep idle connections open instead of closing and reopening them.
  "shrink": false
}

If the pool does need to grow, make sure it grows by only one or two connections at a time:

Growable pool

"driverOptions": {
  "connectionString": "DSN=myIBMi;",
  "initialSize": 10,
  "maxSize": 20,
  // Add at most 2 connections at a time, so no single query waits on 10 new connections.
  "incrementSize": 2,
  "shrink": false
}

Tuning IBM i Server Jobs

Each ODBC connection attaches to its own server job named QZDASOINIT, which runs in the QUSRWRK subsystem. The subsystem is configured to prestart some QZDASOINIT jobs so that jobs are available before connections are established. However, the default subsystem configuration only creates one prestart job, which is usually not realistic and can cause delays when the system has to start many jobs at once.

The number of prestart jobs should be configured based on the ODBC connection pool size in use. For an explanation of how to change the prestart job configuration, see the IBM support page.

Security

If connecting to IBM i from outside the private network or VPN, it is recommended to use the option SSL=1 on the connectionString or DSN configuration to enable encrypted communication. This option requires that the relevant IBM i services are configured for SSL, using a certificate that is trusted by the source system.

For details, see the IBM support page.

Using ODBC Database Driver