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

The "odbc" driver can be used to run SQL statements against DB2 for i using the following APIs:

The "odbc" driver does not allow non-SQL APIs (such as for direct program/procedure call, CL command, etc.) to run against the target system.

Limitations

The current version of the IBM i Access ODBC driver does not support calls to stored procedures with CLOB parameters.
Installation of the odbc package is not as simple as most NPM packages. The package has to be built from source code at installation time – see below.

Installation

The "odbc" driver utilizes 3 different components that need to be installed on the 'source' system only. Meaning, the system where you are running Profound.js and want to connect to IBM i.

The Profound.js "odbc" driver. This component is built into Profound.js, starting with version 5.3.0 and relies on the following components for connectivity to the target system.
The open source package odbc, which must be installed separately via NPM. This package provides a Node.js interface for using ODBC drivers.
The ODBC driver for the target system. This package performs the actual work of communicating with the target database. For example, ODBC access to IBM i is provided by the IBM i Access ODBC Driver, which is supplied by IBM.

Installing the odbc Package with NPM

See the package's installation instructions on NPM, here:

...

Follow the instructions for installing necessary pre-requisites, depending on your operating system. When installing the package itself it's recommended to install the latest beta version, due to performance issues in the current release. To do that, run this command from your Profound.js installation directory instead of using the command suggested on the NPM page:

...

Note: The odbc package is a native add-on for Node.js, which means that it must be compiled from C++ code into a native executable on the source system. Native add-on builds are managed by NPM, but require some additional tools to be installed on the system:

Python
Visual Studio C++ Compiler (Windows)
GNU C++ compiler and GNU Make (Other platforms)

If you get an error attempting to install the package, ensure that these tools are installed/configured properly on your system. For example, to install the build tools on IBM i:

Code Block
yum install make-gnu python2 gcc-cplusplus

To install the build tools on Ubuntu Linux:

Code Block
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. To download the Application Package, visit the IBM i ACS Home Page here:

...

An IBM account is required to access the downloads. Click on Downloads for IBM i Access Client Solutions, accept the license agreement, and use the ACS App Pkg link for the appropriate 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 via the open-source package manager "yum". See here for instructions on setting up yum on IBM i:

https://www.ibm.com/support/pages/node/706903

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

Configuration

Connections are configured using the databaseConnections property in the Profound.js configuration file. For example:

Code Block

language	js

"databaseConnections": [
  {
    "name": "myIBMi",
    "driver": "odbc",
    "type": "db2i",
    "credentialsFile": `${__dirname}/credentials`,
    "driverOptions": {
      "connectionString": "systemDRIVER=IBM i Access ODBC Driver;SYSTEM=myIBMi;namNAM=1;cmtCMT=0;unicodesqlUNICODESQL=1;tsftTSFT=1;dbqDBQ=,MYLIB,MYLIB2,MYLIB3",
      "initialSize": 10,
      "maxSize": 10,
      "shrink": false
    }
  }
]

...

ODBC driver options (such as what system to connect to, etc.) are configured using the "connectionString" property of the "driverOptions" object. Option names/values are separated by an equal sign (=) and each name/value pair is separated by a semi-colon (;). Option names are not case-sensitivemust be specified in uppercase. See here for options supported by the IBM i Access ODBC driver:

...

For example, in the above configuration the library list is set to "MYLIB MYLIB2 MYLIB3" with no default schema, using the "dbqDBQ" connecting string option, and commitment control is disabled via the "cmtCMT" option. The following settings are required by Profound.js and should be set on all connections:

Option	Required Value
namNAM	1
unicodesqlUNICODESQL	1
tsftTSFT	1

Creating a Credentials File

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

...

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:

Code Block
;uidUID={decrypted_userid};pwdPWD={decrypted_password};

When using this option, specify the "connectionString" without the "uidUID" and "pwdPWD" options.

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 (such as a file) where ODBC connection options are stored. When using a Data Source, you can simply specify the Data Source Name (DSN) in the "connectionString", and the options are loaded from the Data Source. Connection options can also be specified on the "connectionString" along with the DSN. In this case, the options override those 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 file location depends on the OS, but is typically /etc/odbc.ini. On IBM i, the location is /QOpenSys/etc/odbc.ini. The file can be edited directly, but it's recommended to use the odbcinst command-line utility to add/remove data sources. On IBM i, the odbcinst command 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/paste the following lines into the file:

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

Make the following adjustments to the file:

The text 'myIBMi' inside the square brackets specifies the Data Source Name. Adding the Data Source will replace an existing Data Source of the same name. Change the 'myIBMi' text to the desired name, leaving the square brackets in place. To check existing DSNs, you can use this command:

Code Block
odbcinst -q -s

If connecting from off system to IBM i, change the value of the 'System' property to the host name or IP address of the target IBM i system. Otherwise, leave as localhost.

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

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

If the command completes successfully, the DSN is added to the "odbc.ini" file. This command can be used to confirm:

Code Block
odbcinst -q -s -n DATA_SOURCE_NAME

The Data Source configuration should be output to the screen. At this point, the "template.ini" file is no longer necessary and can be removed.

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

Code Block

language	js

"databaseConnections": [
  {
    "name": "myIBMi",
    "driver": "odbc",
    "type": "db2i",
    "credentialsFile": `${__dirname}/credentials`,
    "driverOptions": {
      "connectionString": "DSN=DATA_SOURCE_NAME;DBQ=,DATALIB1,DATALIB2",
    }
  }
]

A data source can be removed with this command:

Code Block
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 as the location for Data Source files 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):

Image Added

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;

Image Added

To install a Data Source, create a text file with .dsn extension in the appropriate directory. The file name will be the DSN name. Copy/paste these contents into the file:

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

Change the value of 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" like this:

Code Block

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

Specify the file name as the value of the FILEDSN option, without the .dsn extension.

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 can get held up until all the connections are completed. Establishing each connection can take some time, especially when connecting to IBM i over a WAN. This can result in a long delay running a query while connections are being established. To mitigate this, Profound.js begins the process of establishing the connection pool immediately as the server is starting up. However, delays can occur if the connection pool is configured such that it can grow by large increments. See the pool configuration options here:

https://www.npmjs.com/package/odbc#constructor-odbcpoolconnectionstring

It's recommended to use a configuration like this to prevent the pool from growing once the initial connections are established:

Code Block
"driverOptions": { "connectionString": "DSN=myIBMi;", "initialSize": 10, "maxSize": 10, "shrink": false }

If you need to configure the pool so that it can grow, make sure that it grows by only 1 or 2 connections at a time. For example:

Code Block
"driverOptions": { "connectionString": "DSN=myIBMi;", "initialSize": 10, "maxSize": 20, "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 sub-system. The sub-system is configured to prestart some QZDASOINIT jobs so that jobs are available before connections are established. However, the default sub-system configuration only creates 1 pre-start job, which is usually not realistic. This can cause delays in establishing ODBC connections if the system has to start many jobs at once as connections are made. See here for an explanation of how to change the prestart job configuration:

https://www.ibm.com/support/pages/ibm-i-database-host-server-and-qzdasoinit-prestart-jobs

The number of pre-start jobs should be configured based on the ODBC connection pool size used.

Security

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

See here for details:

https://www.ibm.com/support/pages/host-servers-requiring-digital-certificate-assigned-use-secure-socket-layer-ssl-iseries-access-odbc-driver

Versions Compared

Old Version 18

New Version Current

Key

Limitations

Installation

Installing the odbc Package with NPM

Installing the IBM i Access ODBC Driver

Configuration

Creating a Credentials File

Using ODBC Data Sources (optional)

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

Creating a Data Source on Windows

Optimizing ODBC Pool Performance

Tuning IBM i Server Jobs

Security

Page Comparison

Versions Compared

Old Version 18

New Version Current

Key

Limitations

Installation

Installing the odbc Package with NPM

Installing the IBM i Access ODBC Driver

Configuration

Creating a Credentials File

Using ODBC Data Sources (optional)

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

Creating a Data Source on Windows

Optimizing ODBC Pool Performance

Tuning IBM i Server Jobs

Security