DB2 for z/OS Driver Documentation

This documentation explains the DB2 for z/OS 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 Z/OS. Both connection modes use the same fields.

Note: the form fills the Port with 50000. The drivers fall back to port 5025 only when no port reaches them.

Connection Modes

The driver can reach DB2 for z/OS in two 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 Z/OS.

Both modes connect to the same DRDA port of the DB2 subsystem, the Port field.

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

IBM-DB

The default mode. Gigantics connects with the ibm_db module, IBM's CLI driver.

• Connecting from Linux, Windows or macOS to DB2 for z/OS with ibm_db needs a DB2 Connect license. Without it the server answers with SQL1598N. See DB2 z/OS License Requirements.
• Extra CLI keywords, such as the SSL ones, go in the connectionString Custom Param.
• Buffers up to 60,000 rows when reading and writes in batches of 60,000 rows by default (update loads in batches of 500).
• Merge loads copy each batch into a declared global temporary table (SESSION) and run a MERGE from it. Update loads run a MERGE from the VALUES of each batch.

JS390

Gigantics talks to DB2 for z/OS directly with js390, a DRDA client written in JavaScript, so nothing has to be installed on the Gigantics host and no native module is loaded. It does not use the IBM CLI driver, so the client-side DB2 Connect license file described below does not apply to it.

• Set secure: true in Custom Params to connect with TLS. The Port must then be the TLS port of the DB2 subsystem.
• Each statement is committed as it runs.
• Writes go in batches of 4,000 rows by default. Each batch is atomic: if a row fails, the whole batch is rolled back and the error shows the SQL code of DB2 with its description.
• Update and merge loads run a MERGE with the rows of each batch as VALUES, with no temporary table, a form that DB2 for z/OS V10 accepts too.
• The connectionString and connectParams Custom Params of IBM-DB are not used.

Choose it when you do not want to install the IBM CLI driver and a DB2 Connect license on the Gigantics host.

Differences Between Modes

Technical Details

The DB2 for z/OS driver selects one of two implementations at connection time, according to the connection mode: ibmdb (the default) or js390. Only the selected one is loaded, so ibm_db and its native bindings are not loaded in js390 mode.

Key technical aspects:

• Connection pooling is supported through the pooled flag in configuration
• Schema selection is critical and affects all query generation
• The driver uses the SYSIBM catalog tables for metadata extraction (SYSTABLES, SYSCOLUMNS, SYSINDEXES, SYSFOREIGNKEYS, SYSRELS)
• The DDL of a table is built from the catalog: CREATE TABLE, a unique index and ALTER TABLE ... ADD PRIMARY KEY for the primary key, and ALTER TABLE ... ADD FOREIGN KEY for each foreign key
• Update and merge loads use MERGE

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

Connection String Format

Only the ibmdb mode builds a connection string. The js390 mode passes host, port, database, user and password to js390.

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. [connectionString] is the connectionString Custom Param, appended as is.

Authentication Options

DB2 for z/OS supports several authentication types:

SSH Tunnel Support

The DB2 for z/OS 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.

API Endpoints Used

DB2 for z/OS connections are primarily used in:

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

Node.js Driver Dependencies

The DB2 for z/OS driver depends on one Node.js library for each mode:

DB2 z/OS License Requirements

In IBM-DB mode, to connect to DB2 for z/OS servers, the ibm_db library requires a valid DB2 Connect license. For DB2 for z/OS, you need the "DB2 Connect Unlimited Edition for System z" license certificate file.

The license file (typically named db2consv_zs.lic) must be copied to the clidriver/license directory within the ibm_db installation. The exact path depends on where the ibm_db library is installed in your system, but it's typically:

• In local installations: node_modules/ibm_db/installer/clidriver/license/
• In global installations: <ibm_db_install_path>/installer/clidriver/license/

SQL1598N License Error

When connecting from distributed platforms (Linux, Unix, Windows, MacOS) to Db2 for z/OS or Db2 for i (AS400) without a valid db2connect license, the server will return an SQL1598N error. This error indicates unauthorized access due to missing licensing.

If you encounter the SQL1598N error, you can resolve it by either:

1. Having your DBA run the db2connectactivate utility on the Db2 server to activate the license server-side
2. Applying a client-side db2connect license file by copying it to the clidriver/license directory as described above

Note that a db2connect license of version 12.1 is required for current ibm_db versions, though older V11.5 licenses can work with specific clidriver versions.

It's important to note that if your z/OS system already has ODBC driver licensing properly configured and unlocked, a separate Gigantic DB2 z/OS license file may not be required for connections to succeed.

For more information on DB2 license configuration and the SQL1598N error, please refer to the IBM DB2 License Configuration Documentation and the ibm_db GitHub README section on SQL1598N.

Large Tables and Resume

This section lists only what is specific to DB2 for z/OS. How resume, parallel ranges and row estimates work in every driver is explained in Large databases. It applies to both connection modes, ibmdb (the default) and js390, chosen in the Backend Mode field.

Reading

• Tables are read in chunks ordered by the primary key (the KEYSEQ columns of SYSIBM.SYSCOLUMNS), so a job that stops resumes after the last key it saved. A table with no primary key is read with one cursor and starts over if the job stops.
• Every primary key column must be SMALLINT, INTEGER, BIGINT or text. A key with a DECIMAL or NUMERIC column 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 (CARD of SYSIBM.SYSTABLES when it was scanned, or a count when RUNSTATS never ran) decides how many ranges to cut. Scan the model again after a table grows a lot.
• In ibmdb mode, a table loaded into DB2 for z/OS by update or merge is read in one stream, because the driver stages merges in a session table. In js390 mode an update or merge load does not prevent parallel ranges.

Loading

• Constraints and triggers are not disabled during a load.
• Insert loads empty the existing tables with TRUNCATE TABLE, unless the truncate option is turned off. A resumed table is never emptied: it keeps the rows the failed run loaded.
• Update and merge loads match all the columns of a composite primary key.

Custom Params

The DB2 for z/OS 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: js390        # ibmdb or js390. Overrides the Backend Mode field
pooled: true       # Use a pool of connections

IBM-DB Mode Parameters

connectionString: "Security=SSL;SSLServerCertificate=/path/to/db2server.arm"  # Extra CLI keywords appended to the connection string
connectParams:     # Options passed to ibm_db when opening the connection
  connectTimeout: 30   # Seconds (30 by default without pooled)
batchSize: 60000   # Rows buffered when reading and per write batch (update loads use 500)

JS390 Mode Parameters

secure: true         # Connect with TLS
timeout: 60000       # Connection timeout in milliseconds
batchSize: 4000      # Rows buffered when reading
blockSize: 262144    # Fetch block size in bytes (256 KB)
writeBatchSize: 4000 # Rows per write batch

With pooled: true, JS390 keeps a pool of up to 10 connections.

Example Configurations

SSL Configuration (IBM-DB mode)

connectionString: "Security=SSL;SSLServerCertificate=/path/to/db2server.arm"

TLS Configuration (JS390 mode)

secure: true

Advanced Configuration with Performance Tuning (JS390 mode)

blockSize: 524288
writeBatchSize: 8000
pooled: true