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

Note: The actual implementation uses port 5025 by default, but the UI shows 50000. Both ports should work with DB2 for z/OS connections.

Technical Details

The DB2 for z/OS driver implementation uses the ibm_db Node.js library with connection pooling capabilities.

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 SYSIBM system tables for metadata extraction (SYSTABLES, SYSCOLUMNS, SYSINDEXES)
• Constraint management is implemented using system procedures
• Journaling and locking capabilities specific to IBM z/OS are supported
• DDL generation uses the DB2 for z/OS UNLOAD and RELOAD utilities
• Supports MERGE operations for efficient data updates

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

Connection String Format

DB2 for z/OS connections are built using a connection string format specific to the ibm_db library:

DATABASE=[database];HOSTNAME=[host];PORT=[port];PROTOCOL=TCPIP;UID=[user];PWD=[password];

Additional connection parameters can be specified through configuration options in the driver.

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 Dependency

The DB2 for z/OS driver depends on the ibm_db Node.js library:

• Library: ibm_db
• Purpose: Connects to IBM DB2 for z/OS databases
• Features: Connection pooling, prepared statements, transactions

DB2 z/OS License Requirements

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.

Custom Params

The DB2 for z/OS driver supports additional custom parameters that can be specified in YAML format to fine-tune connection behavior, SSL settings, and other advanced options.

SSL Security Parameters

# SSL Connection Parameters
Security: SSL
SSLServerCertificate: /path/to/servercert.arm
ValidateServerCertificate: false  # Set to YES by default, NO to disable validation

# SSL Trust Store Parameters (GSKit format)
SSLTrustStoreLocation: /path/to/truststore.kdb
TrustStorePassword: truststore_password

# Client Certificate Authentication
SSLClientCertificate: /path/to/clientcert.arm
ClientCertificateLabel: "my_client_cert"

Connection Pool Parameters

# Connection pooling
pooled: true
connectionTimeout: 30  # Timeout in seconds for connection establishment
queryTimeout: 60       # Timeout in seconds for query execution

# Connection validation
connectionValidation: auto  # or timeout, count, manual
validationTimeout: 0       # Period after which connection is re-validated (0 = immediate)
validationCount: 0        # Number of uses before connection is re-validated (0 = every use)

Performance Tuning Options

# Performance parameters
fetchSize: 1000     # Number of rows to fetch per request
ArraySize: 100     # Number of parameter sets when using arrays
asyncMode: full    # or row, off - Asynchronous operation mode

# Advanced connection options
applicationName: "Gigantic DB2z Connection"
currentSchema: myschema
currentPath: "SYSIBM,SYSFUN,SYSPROC,SYSXML"

Example Configurations

SSL Configuration

Security: SSL
SSLServerCertificate: ${file:/path/to/db2server.arm}
ValidateServerCertificate: true
applicationName: "Gigantic Secure Connection"

Advanced Configuration with Performance Tuning

fetchSize: 5000
connectionTimeout: 60
queryTimeout: 300
pooled: true
connectionLimit: 20
asyncMode: full