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

UI Field	Technical Implementation	Description	Required	Default Value
Host	`host` in odbc connection string	The hostname or IP address of the DB2 for i server	Yes	localhost
Port	Part of connection string in odbc config	The port number DB2 for i server is listening on	Yes	50000 (from driverPorts)
Database	Part of connection string in odbc config	The name of the database/library to connect to	No	None (defaults to user's library)
Schema	`dbSchema` in gigadb config	The schema/library to use for queries and metadata extraction	Yes	None (must be specified)
Username	`user` in odbc connection config	Database user for authentication	Yes	None (must be specified)
Password	`password` in odbc connection config	Password for the database user	Yes	None (must be specified)

Technical Details

The DB2 for i driver implementation uses the odbc Node.js library with custom wrapper functionality for IBM iSeries connections.

Key technical aspects:

Connection pooling is supported through the pooled flag in configuration
Uses IBM i Access ODBC Driver for connectivity
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

DB2 for i connections are built using ODBC connection strings with the IBM i Access ODBC Driver:

DRIVER=IBM i Access ODBC Driver;SYSTEM=[host];PORT=[port];UID=[user];PWD=[password];

Additional ODBC connection parameters can be specified through configuration options.

Authentication Options

DB2 for i supports several authentication types:

Auth Type	UI Mapping	Description
None	Not applicable for DB2i	DB2i always requires authentication
Password	Username/Password fields	Standard username/password authentication
Password Manager	Various password manager fields	Fetches credentials from external password managers

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.

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)

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.

ODBC Connection Parameters

# Basic connection parameters
# Connection Behavior
CONNTYPE: 1  # Connection type: 0=Default, 1=Primary, 2=Secondary
SORTTYPE: 2  # Sort type: 0=System, 1=Hex, 2=Table
SORTWEIGHT: 1  # Sort weight: 0=Case insensitive, 1=Case sensitive
LANGUAGEID: "ENU"  # Language ID for national language support

# Performance Options
MAXBUFLEN: 32767  # Maximum buffer length for column data
LOBThreshold: 32767  # Threshold for LOB data handling
BLOCKING: 100  # Number of rows to fetch per request

# Security Options
ALLOWNULL: false  # Allow NULL values in result sets
DEBUG: false  # Enable debug mode for connection
SECURITY: "SSL"  # Security protocol (SSL, TLS, etc.)

# Timeout Settings
loginTimeout: 30  # Login timeout in seconds
queryTimeout: 60  # Query timeout in seconds
connectionTimeout: 30  # Connection establishment timeout

Connection Pool Parameters

# Pool configuration
pooled: true
poolMin: 0
poolMax: 10
poolIncrement: 1
poolTimeout: 60  # Pool timeout in seconds

Example Configurations

Basic Configuration

pooled: true
CONNTYPE: 1

SSL Configuration

SECURITY: "SSL"
ValidateServerCertificate: true

Advanced Configuration with Performance Tuning

CONNTYPE: 1
SORTTYPE: 2
BLOCKING: 500
LOBThreshold: 65536
queryTimeout: 300
pooled: true
poolMax: 20