DB2 for i (AS/400) Driver Documentation

This documentation explains the DB2 for i driver implementation and how connection parameters map to UI fields in the Gigantic application.

Connection Parameters

In the driver list this driver is shown as DB2 iSeries. Which fields matter depends on the connection mode.

Connection Modes

The driver can reach the IBM i in four ways. The mode is chosen in the Backend Mode field, in the General section of the tap or sink form, right below Driver. The field appears only when the driver is DB2 iSeries.

New taps and sinks get JS400. A datasource saved without a mode, such as one created before the field existed, also uses js400. A mode key in Custom Params overrides the field, because Custom Params are applied last.

JS400

The default mode. Gigantics talks to the IBM i directly with js400, a JavaScript port of JTOpen, so nothing has to be installed on the Gigantics host and no native module is loaded.

• It signs on through the IBM i host servers. Their ports are found through the port mapper (port 449); if the port mapper does not answer, it uses the standard ports, 8476 for sign-on and 8471 for the database server.
• The Port field sets the port of the database host server, for example when it listens on a port of its own. Left at 446 (or 947), the DRDA ports, or empty, it is not used and the port mapper finds the port. Sign-on always goes through the port mapper or port 8476.
• Set secure: true in Custom Params to connect with TLS (standard ports 9476 and 9471).
• Reads in blocks of 8,000 rows and writes in batches of 16,000 rows by default.

Choose it unless you have a reason for another mode.

ODBC

Gigantics connects through the IBM i Access ODBC Driver with the odbc Node.js module.

• The Gigantics host needs the IBM i Access ODBC Driver (from IBM i Access Client Solutions) registered under the name IBM i Access ODBC Driver, an ODBC driver manager (unixODBC on Linux) and the odbc module, which is an optional dependency of Gigantics. If the module is missing, connections in this mode fail.
• The ODBC driver also reaches the IBM i through the host servers and the port mapper. The driver has no port keyword, so the Port field is not used: a value other than 446, 8471 or 9471 only logs a warning (see Connection String Format).
• Extra ODBC keywords go in the connectionString Custom Param.

Choose it when your organization already uses the IBM i Access ODBC Driver and wants Gigantics to go through it.

IBM-DB

Gigantics connects with the ibm_db module, IBM's CLI driver, over DRDA to the Port field (446 by default).

• Connecting from Linux, Windows or macOS to DB2 for i with ibm_db needs a DB2 Connect license, as for DB2 for z/OS. Without it the server answers with SQL1598N. See DB2 z/OS License Requirements.
• Extra CLI keywords go in the connectionString Custom Param.

Choose it when only the DRDA port of the IBM i is reachable, for example through an SSH tunnel, and you have a DB2 Connect license.

SSH

Gigantics opens an SSH connection to the IBM i and does the work there with IBM i commands. Nothing is installed on the Gigantics host and no database port has to be open, only SSH.

When this mode is selected the form changes:

The Enable SSH? tunnel checkbox is not shown in SSH mode. The SSH user and password are the Username and Password of the Authentication section.

How it works:

• SQL (metadata, DDL, filtered reads) runs in one qsh -c "db2 -t -i" session kept open for the whole connection.
• A table is read in blocks of relative record numbers: CPYF ... FROMRCD() TORCD() copies each block into a temporary file in the same library (its name starts with DB and it is deleted after each block), CPYTOIMPF exports it to a CSV file in Remote Temp Path, compress packs it and SFTP brings it to Gigantics. The block size is batchSize (65,536 rows). The blocks cover every record of the file, deleted rows included (NUMBER_ROWS plus NUMBER_DELETED_ROWS of QSYS2.SYSTABLESTAT), so the rows after the deleted ones are read too. A block that has only deleted rows is skipped.
• A read with a filter, a sort, a column selection or a custom query runs as SQL in the db2 session instead. Its rows are passed on as the session prints them, and the session pauses while they wait to be written, so the result is not held in memory.
• A load writes CSV files on the Gigantics host, compresses them, uploads them by SFTP, runs uncompress and loads them with CPYFRMIMPF.
• A load that empties the table copies its first batch with MBROPT(*REPLACE) and the rest with MBROPT(*ADD). An append load copies every batch with MBROPT(*ADD), so the rows already in the file stay.
• Update and merge loads copy each batch into a duplicate of the file in QTEMP and run a MERGE from it. An update changes only the rows that already exist; a merge also inserts the new ones (WHEN NOT MATCHED THEN INSERT).
• Dates and times are written in the formats CPYFRMIMPF reads by default: DATE columns as YYYY-MM-DD, TIME columns as HH.MM.SS and TIMESTAMP columns as YYYY-MM-DD-HH.MM.SS.ffffff. A value with a time zone is written in UTC.

On the IBM i, the user needs:

• the SSH server running and a shell that can run qsh, db2, system, compress and uncompress;
• authority to create and delete files in the library being read (CPYF ... CRTFILE(*YES) and DLTF), and to run CPYTOIMPF and CPYFRMIMPF;
• write access to Remote Temp Path.

Choose it when only SSH is open to the IBM i.

Differences Between Modes

In SSH mode tables are read with one cursor and loaded in one piece. See Large Tables and Resume.

Technical Details

The DB2 for i driver selects one of four implementations at connection time, according to the connection mode: js400 (the default), odbc, ibmdb or ssh.

Key technical aspects:

• Connection pooling is supported through the pooled flag in configuration (all modes except ssh)
• Schema/library selection is critical and affects all query generation
• The driver uses QSYS2 system tables for metadata extraction (SYSTABLES, SYSCOLUMNS2, SYSINDEXES)
• Constraint management is implemented using system procedures like QSYS2.CREATE_CONSTRAINT_INFO
• Journaling and locking capabilities specific to IBM i are supported

When SSH tunneling is enabled, connections are first established through the tunnel before connecting to the DB2 for i server.

Connection String Format

Only the odbc and ibmdb modes build a connection string. The js400 mode passes host, user, password and the port of the database host server to js400, and the ssh mode opens an SSH session.

In odbc mode, with the IBM i Access ODBC Driver:

DRIVER={IBM i Access ODBC Driver};SYSTEM=[host];DATABASE=[database];CCSID=1208;UserID=[user];Password=[password];[connectionString]

The ODBC string has no port: the IBM i Access ODBC Driver has no port keyword, and it connects to the database host server (8471, or 9471 with TLS) that it finds in the services table or through the port mapper. The Port field is not used; a value other than 446, 8471 or 9471 logs a warning. DATABASE is left out when the Database field is empty.

In ibmdb mode, with ibm_db:

HOSTNAME=[host];DATABASE=[database];PORT=[port];CURRENTSCHEMA=[schema];UID=[user];PWD=[password];[connectionString]

DATABASE, PORT and CURRENTSCHEMA are left out when empty. In both modes, [connectionString] is the connectionString Custom Param, appended as is.

Authentication Options

DB2 for i supports several authentication types:

SSH Tunnel Support

The DB2 for i driver supports SSH tunneling for secure access to databases behind firewalls or in private networks. For detailed information on configuring SSH tunnels, please refer to the SSH Tunnelling documentation.

The tunnel forwards a single port, the one in the Port field. Only the ibmdb mode connects through that port alone, so use the tunnel with IBM-DB. The js400 mode connects to the database server on that port, but it signs on through the port mapper and the sign-on server, which the tunnel does not forward. The odbc mode has no port to set. In ssh mode the tunnel option is not shown: the connection is already SSH.

API Endpoints Used

DB2 for i connections are primarily used in:

• Tap creation (data source discovery)
• Sink creation (data destination for anonymized data)
• Pipeline execution (data extraction and loading)

Large Tables and Resume

This section lists only what is specific to DB2 for i. How resume, parallel ranges and row estimates work in every driver is explained in Large databases.

The driver works in one of the four modes described in Connection Modes: js400 (the default), odbc, ibmdb and ssh. What follows applies to js400, odbc and ibmdb. In ssh mode (CSV files over SFTP) tables are read with one cursor and loaded in one piece, so they are not resumed or split into ranges.

Reading

• Tables are read in chunks ordered by the primary key (from QSYS2.SYSCST), so a job that stops resumes after the last key it saved. A file with no primary key constraint is read with one cursor and starts over if the job stops: the relative record number is not used.
• Every primary key column must be SMALLINT, INTEGER, BIGINT or text. A key with a DECIMAL or NUMERIC column, common in older physical files, is not used even when it has no decimals.

Parallel Ranges and Row Estimates

• Ranges are cut only for a key of one integer column, split evenly between its minimum and maximum.
• There is no live row estimate: the row count saved in the model (NUMBER_ROWS of QSYS2.SYSTABLESTAT when it was scanned) decides how many ranges to cut. Scan the model again after a table grows a lot.
• In modes js400 and ibmdb, update and merge loads stage the rows in a session table, so a table loaded into DB2 for i by update or merge is read in one stream.

Loading

• During the load, the constraints of the files that have them are disabled with CHGPFCST ... CST(*ALL) STATE(*DISABLED) and enabled again with STATE(*ENABLED) at the end. They are not recorded: if a job dies, they stay disabled until a later load of those files ends or they are enabled by hand.
• Triggers stay enabled.
• In modes js400 and ibmdb, update and merge loads match all the columns of a composite primary key.

Permissions

For a DB2 for i sink, the user needs the authority to run CHGPFCST on the loaded files through QSYS2.QCMDEXC.

Custom Params

The DB2 for i driver supports additional custom parameters that can be specified in YAML format to fine-tune connection behavior, pooling, and other advanced options. Each mode reads its own keys; a key that a mode does not read is ignored.

Parameters for All Modes

mode: js400        # js400, odbc, ibmdb or ssh. Overrides the Backend Mode field
ddlTimeout: 30     # Seconds allowed to extract the DDL of a table
journal: MYLIB/MYJRN  # Journal used to start journaling a table that was created
                      # without one (js400, ibmdb and ssh modes)

JS400 Mode Parameters

secure: true       # Connect with TLS
timeout: 60000     # Connection timeout in milliseconds
fetchSize: 8000    # Rows fetched per block when reading
writeBatchSize: 16000  # Rows per batch when writing
blockSize: 512     # Fetch block size in KB
pooled: true       # Use a pool of connections (up to 10)

ODBC Mode Parameters

connectionString: "BLOCKSIZE=512;COMPRESSION=1"  # Extra keywords appended to the ODBC string
connectParams:     # Options passed to the odbc connection pool
  connectionTimeout: 30
  loginTimeout: 30
batchSize: 60000   # Rows per read batch
writeBatchSize: 1000  # Rows per write batch
pooled: true

IBM-DB Mode Parameters

connectionString: "SECURITY=SSL"  # Extra keywords appended to the CLI string
batchSize: 8000    # Rows per read and write batch
pooled: true

SSH Mode Parameters

batchSize: 65536     # Rows per CPYF block when reading and per CSV file when writing
readBatchProcs: 1    # Blocks downloaded at the same time
writeBatchProcs: 1   # CSV files uploaded and loaded at the same time

Example Configurations

Basic Configuration

pooled: true

TLS Configuration (JS400 mode)

secure: true

Advanced Configuration with Performance Tuning (JS400 mode)

fetchSize: 16000
writeBatchSize: 16000
blockSize: 512
ddlTimeout: 300
pooled: true