Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 12 Next »

Support for ODBC was added in Profound.js 5.3.0.

Profound.js's "odbc" database driver works with the odbc package from NPM to allow database connectivity via the Open Database Connectivity (ODBC) standard. The "odbc" driver can be used to connect to any database supported by Profound.js, but is primarily designed and tested for connecting to DB2 on IBM i. For the other supported databases, the DB-specific drivers should be used instead.

The "odbc" driver offers a few advantages over Profound.js's "IBMi" driver:

  • Increased performance, both for connections to a remote IBM i and for connections on IBM i to the local database.
  • Connections to multiple IBM i systems are supported. The "IBMi" driver only supports connecting to a single IBM i system.
  • Nothing needs to be installed on target IBM i systems. The "IBMi" driver requires a Profound.js installation on the target IBM i system.

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

Installation

The "odbc" driver utilizes 3 different components that need to be installed on the 'source' system. 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:

https://www.npmjs.com/package/odbc

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:

npm install odbc@beta

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:

https://www.ibm.com/support/pages/ibm-i-access-client-solutions

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:

"databaseConnections": [
  {
    "name": "myIBMi",
    "driver": "odbc",
    "type": "db2i",
    "credentialsFile": `${__dirname}/credentials`,
    "driverOptions": {
      "connectionString": "DSN=myIBMi;dbq=,mylib,mylib2,mylib3;",
      "initialSize": 10,
      "maxSize": 10,
      "shrink": false
    }
  }
]

The database connections entry has the following properties:

  • name (required): A name for the connection. The name can be whatever you choose, but must be unique.
  • driver (required): Set to "odbc".
  • type (required): The target database type. Can be any of "db2i", "mysql", "mssql", or "oracledb". However, this driver is primarily intended for use with DB2 for IBM i. For other databases, it's recommended to use the DB-specific drivers.
  • credentialsFile: (optional): Path to a file containing an encrypted user id / password created by the "store_credentials" utility provided with Profound.js. 
  • driverOptions: (required): An object containing options for the odbc package's Pool constructor. See the NPM page for supported options. Profound.js's "odbc" driver only supports pooled connections. A pool is created for each databaseConnections entry. 

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 (;). See here for options supported by the IBM i Access ODBC driver:

https://www.ibm.com/docs/en/i/7.4?topic=details-connection-string-keywords

Driver options can be specified directly, or alternatively via an ODBC Data Source. A Data Source is an OS-specific location (such as a file) where ODBC settings are stored. To use a Data Source, reference the name using the DSN (Data Source Name) property. It's also possible to use a combination of these approaches – properties specified directly in "connectionString" will override the settings from a Data Source. For example, in the above configuration a Data Source named "myIBMi" is used, and the library list is overridden to "MYLIB, MYLIB2, MYLIB3".

It's recommended to use a Data Source configured with the following options:

Driver           = IBM i Access ODBC Driver
System           = ibmi_hostname_or_ip
Naming           = 1
CommitMode       = 0
UnicodeSQL       = 1
TimestampFormat  = 1

If connecting to IBM i from outside the private network, it's recommended to enable SSL with this setting:

SSL=1

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

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


Creating a Data Source on Windows


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:

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.

  • No labels