Db2 - Wirekite Docs

Overview

Wirekite supports IBM Db2 for Linux, UNIX and Windows (LUW) 11.5 and above as a source database for:

Schema Extraction - Extract table definitions to Wirekite’s intermediate format
Data Extraction - Bulk extract table data for initial load
Change Extraction (CDC) - Capture ongoing inserts, updates, and deletes by reading the Db2 recovery log

Db2 Change Extraction reads the Db2 recovery (transaction) log directly through the db2ReadLog administrative API. This requires archive logging (not circular logging) and DATA CAPTURE CHANGES to be enabled on each tracked table.

Db2 is supported as a Data (one-time load) and Replicate (CDC) source. It is not currently a Target Sync source. As a Wirekite target, Db2 additionally supports Target Sync — see the Db2 target guide.

How it works

The Db2 source uses two extraction paths:

Path	Used for	Db2 access	Where it runs
Schema + initial load	Schema Extractor, Data Extractor	`go_ibm_db` over the relocatable clidriver (plain `SELECT`)	Any host that can reach the Db2 port
Change capture (CDC)	Change Extractor	`db2ReadLog` via the full Data Server Client	On a host with a Db2 instance (typically the Db2 server itself)

CDC type coverage is narrower than the initial load. The initial load extracts every mapped Db2 type; change capture decodes a narrower set from the log. Columns of type DECFLOAT, GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, DBCLOB, or LONG VARCHAR are not decodable from the log — a replicated table containing one of these columns loses that column and every column after it in the change stream. Avoid these types on replicated tables, or place them last in column order. They load correctly during the one-time Data load. See the Db2 datatype matrix.

Prerequisites

Before configuring Db2 as a Wirekite source, ensure the following requirements are met:

Database Configuration

Version: Db2 (LUW) 11.5 or above.
Archive logging (CDC only): The database must use archive logging (LOGARCHMETH1 set to a destination, not OFF/circular), which requires an initial full backup. Circular logging cannot drive db2ReadLog change capture.
DATA CAPTURE CHANGES (CDC only): Must be enabled on each tracked table so that full row images (not just keys) are written to the log.

File System Access

Ensure the user running Wirekite has write permissions to the output directory.
Verify sufficient disk space for extracted data files.

Replication Considerations

When performing the initial data extraction, quiesce writes where possible. The Data Extractor holds a LOCK TABLE ... IN SHARE MODE on every extracted table for the duration of the snapshot to produce a consistent image and capture a precise log position for handoff to CDC. Out-of-line values (LOB/XML) and relocated rows are resolved by primary-key lookups that converge correctly against a drained/quiescent source.

Source Database Setup

Step 1: Create the Wirekite User

Db2 authenticates against operating-system users. Create an OS user for Wirekite on the Db2 server and grant it database access:

-- Connect as an instance owner / DBADM
GRANT CONNECT ON DATABASE TO USER wirekite;

Step 2: Grant Read Access to Application Tables

For each application table that needs to be extracted:

GRANT SELECT ON <app_schema>.table1 TO USER wirekite;
GRANT SELECT ON <app_schema>.table2 TO USER wirekite;
-- repeat for each table to be extracted

Wirekite also reads column and constraint metadata from the SYSCAT catalog views (SYSCAT.COLUMNS, SYSCAT.TABLES, SYSCAT.TABCONST, SYSCAT.KEYCOLUSE, SYSCAT.REFERENCES, SYSCAT.CHECKS, SYSCAT.INDEXES, SYSCAT.INDEXCOLUSE), which are readable by any connected user by default.

Step 3 (CDC Only): Enable Archive Logging

Change capture reads the recovery log, which requires archive (not circular) logging. As the instance owner:

# Point the primary log archive at a destination
db2 update db cfg for <database> using LOGARCHMETH1 DISK:/db2/archive/

# Archive logging requires an initial full backup to activate
db2 backup db <database> to /db2/backup/

Step 4 (CDC Only): Enable DATA CAPTURE CHANGES

For each table to be tracked, enable full change capture to the log:

ALTER TABLE <app_schema>.table1 DATA CAPTURE CHANGES;
ALTER TABLE <app_schema>.table2 DATA CAPTURE CHANGES;
-- repeat for each table to be tracked

Verify:

SELECT TABSCHEMA, TABNAME, DATACAPTURE
FROM SYSCAT.TABLES
WHERE TABSCHEMA = '<APP_SCHEMA>';
-- DATACAPTURE should be 'L' (changes captured to log) for tracked tables

Without DATA CAPTURE CHANGES, the log only records primary-key (keys-only) images for a table, and Wirekite cannot reconstruct full-row changes. Pre-existing customer tables are usually not armed for capture by default.

Step 5 (CDC Only): Grant Log Read Authority

The db2ReadLog API is privileged and requires DBADM (or SYSADM):

GRANT DBADM ON DATABASE TO USER wirekite;

Step 6 (CDC Only): Set the Db2 Instance

The Change Extractor must run with the DB2INSTANCE environment variable set to the instance that owns the database (default db2inst1), because db2ReadLog operates in the instance context. This is provided by the db2Instance config key or the DB2INSTANCE environment variable.

Privilege Summary

Privilege	Required For
`CONNECT`	Basic connectivity
`SELECT ON <app_schema>.<table>`	Reading application table data
Catalog (`SYSCAT.*`) read	Schema metadata (granted to connected users by default)
`DBADM`	CDC: `db2ReadLog` recovery-log access
`DATA CAPTURE CHANGES` (per table)	CDC: full row images in the log

Wirekite does not create any tracking tables, triggers, or stored procedures on the Db2 source. Change capture uses the db2ReadLog log-read API exclusively, and progress is tracked in a local file outside Db2.

Schema Extractor

The Schema Extractor reads table definitions from Db2’s SYSCAT catalog and outputs them to Wirekite’s intermediate schema format (.skt file). This schema file is used by target loaders to create corresponding tables in the destination database.

Required Parameters

dsnFile

string

required

Path to a file containing the Db2 connection string (see Connection String). The file should contain exactly one line.

tablesFile

string

required

Path to a file listing the tables to extract, one per line in schema.table format.

outputDirectory

string

required

Absolute path to the directory where Wirekite will write the schema file (wirekite_schema.skt). The directory must exist and be writable.

logFile

string

required

Absolute path to the log file where the Schema Extractor will write operational logs.

Optional Parameters

schemaRename

string

default:"none"

Rename source schemas in the output. Format: sourceSchema:targetSchema,source2:target2. Useful when the target database uses a different schema name.

The Schema Extractor emits columns, primary keys, unique constraints, foreign keys, check constraints, and regular secondary indexes. Column defaults are not translated (emitted as none). Db2 folds unquoted identifiers to uppercase in the catalog; Wirekite lowercases table and column names in the schema file.

Data Extractor

The Data Extractor performs bulk extraction of table data, writing records to Wirekite’s intermediate data format (.dkt files). Large tables are automatically split across multiple files based on the maxRowsPerDump setting.

Required Parameters

dsnFile

string

required

Path to a file containing the Db2 connection string.

tablesFile

string

required

Path to a file listing the tables to extract, one per line in schema.table format.

outputDirectory

string

required

Absolute path to the directory where Wirekite will write data files. Files are named schema.table.N.dkt where N is a sequence number.

logFile

string

required

Absolute path to the log file for Data Extractor operations.

Optional Parameters

maxThreads

integer

default:"5"

Number of parallel extraction threads. Each thread consumes one database connection. Tables are chunked by primary key: single-column integer keys use fast range predicates; other keys use ordered fetch windows; tables with no primary key are extracted as a single chunk.

maxRowsPerDump

integer

default:"200000"

Maximum number of rows written to each output file. Large tables are split into multiple files.

hexEncoding

boolean

default:"false"

When true, binary and string data is encoded as hexadecimal instead of base64. Hex encoding produces larger files but may be required for certain target databases.

sortFiles

boolean

default:"true"

When true, sorts output files by table name for easier management and verification.

capturePosition

boolean

default:"false"

When true, the Data Extractor captures the current recovery-log position at the end of the snapshot (while table locks are still held) and writes it to position.pkt. Used to hand off to the Change Extractor without losing any commits. The Orchestrator sets this automatically in data+change mode.

extractPauseMB

integer

default:"5120"

Pause extraction when free disk space on the output volume drops below this many megabytes (backpressure).

extractResumeMB

integer

default:"8192"

Resume extraction once free disk space rises back above this many megabytes.

schemaRename

string

default:"none"

Rename source schemas in the output. Format: sourceSchema:targetSchema.

Change Extractor

The Change Extractor captures ongoing data changes (INSERT, UPDATE, DELETE) by reading the Db2 recovery log via the db2ReadLog API. It groups log records by transaction and emits committed changes to Wirekite’s change format (.ckt files).

The Change Extractor requires the full Db2 Data Server Client and runs in the context of a Db2 instance (DB2INSTANCE). It is typically deployed on the Db2 server host.

Required Parameters

dsnFile

string

required

Path to a file containing the Db2 connection string. (Alternatively, provide the string via the connectionString key.)

tablesFile

string

required

Path to a file listing the tables to track for changes, one per line in schema.table format.

outputDirectory

string

required

Absolute path to the directory where Wirekite will write change files (N.ckt).

Position Parameters

position

string

default:"none"

The Db2 recovery-log position (a compact LRI) from which to start extracting changes. This is typically the position captured at the end of the initial snapshot. When running via the Orchestrator in data+change mode, this is captured and passed automatically.

In change-only mode (without an initial data extraction), you must specify the correct position to define where change capture should begin. Incorrect values may result in missing changes or duplicate data.

Optional Parameters

logFile

string

default:"[outputDirectory]/change_extractor.log"

Path to the log file for Change Extractor operations.

cktRowsPerFile

integer

default:"50000"

Number of change rows to write to each .ckt file before rotating to the next. Transactions never span multiple output files.

idleIntervalMs

integer

default:"500"

Milliseconds to sleep when the reader has caught up to the end of the log before polling again.

exitWhenIdle

boolean

default:"false"

When true, the extractor exits cleanly once it has caught up and stayed idle. When false (default), it runs continuously.

idleWaitSeconds

integer

default:"0"

Number of idle seconds to wait before exitWhenIdle triggers an exit.

resetProgress

boolean

default:"false"

When true, wipes existing change files and progress and restarts reading from the beginning of the available log. Used by the “restart replication” flow.

db2Instance

string

default:"db2inst1"

The Db2 instance (DB2INSTANCE) that owns the database, required for db2ReadLog.

schemaRename

string

default:"none"

Rename the source schema in the output. Format: sourceSchema:targetSchema.

Connection String

The Db2 extractors connect through the go_ibm_db driver (schema/data) and the Db2 client (CDC), both of which use a keyword-style connection string (not a URL). The dsnFile should contain exactly one line:

HOSTNAME=<host>;PORT=<port>;PROTOCOL=TCPIP;DATABASE=<database>;UID=<user>;PWD=<password>

Example DSN file contents:

HOSTNAME=db2-source.example.com;PORT=50000;PROTOCOL=TCPIP;DATABASE=APP_DB;UID=wirekite;PWD=secretpass

The default Db2 port is 50000. For the CDC helper tools, the connection string is passed on standard input so it never lands on disk.

Orchestrator Configuration

When using the Wirekite Orchestrator, prefix source parameters with source.schema., source.data., or source.change. depending on the operation mode. Example orchestrator configuration for Db2 source:

# Main configuration
source=db2
target=snowflake

# Schema extraction
source.schema.dsnFile=/opt/wirekite/config/db2.dsn
source.schema.tablesFile=/opt/wirekite/config/tables.txt
source.schema.outputDirectory=/opt/wirekite/output/schema
source.schema.logFile=/var/log/wirekite/schema-extractor.log

# Data extraction
source.data.dsnFile=/opt/wirekite/config/db2.dsn
source.data.tablesFile=/opt/wirekite/config/tables.txt
source.data.outputDirectory=/opt/wirekite/output/data
source.data.logFile=/var/log/wirekite/data-extractor.log
source.data.maxThreads=8
source.data.maxRowsPerDump=100000

# Change extraction (CDC)
source.change.dsnFile=/opt/wirekite/config/db2.dsn
source.change.tablesFile=/opt/wirekite/config/tables.txt
source.change.outputDirectory=/opt/wirekite/output/changes
source.change.logFile=/var/log/wirekite/change-extractor.log
source.change.db2Instance=db2inst1
source.change.cktRowsPerFile=50000

When running in data+change mode, the Orchestrator captures the recovery-log position after data extraction and passes it to the Change Extractor. You do not need to manually specify position in this mode.

Limitations

Target Sync is not supported with a Db2 source (Data and Replicate only).
CDC type coverage is narrower than the initial load: DECFLOAT, GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, DBCLOB, and LONG VARCHAR columns are not decodable from the log and truncate the change row. They load fine during the one-time Data load.
Primary keys: tables without a primary key can be bulk-extracted, but have no reliable row identity for change capture. Use primary keys on replicated tables.
LOB and XML values are stored out-of-line and resolved by a primary-key lookup at commit time; they converge correctly against a quiescent source. LOB primary-key resolution supports integer and character key columns.
TIME has no sub-second precision in Db2.
Column defaults are not translated into the schema output.
Unknown/unmapped Db2 types fall back to a wide character type on the initial load (with a warning) and are not supported by CDC.

For details on how each Db2 type is converted, see the Db2 datatype matrix.

​Overview

​How it works

​Prerequisites

​Database Configuration

​File System Access

​Replication Considerations

​Source Database Setup

​Step 1: Create the Wirekite User

​Step 2: Grant Read Access to Application Tables

​Step 3 (CDC Only): Enable Archive Logging

​Step 4 (CDC Only): Enable DATA CAPTURE CHANGES

​Step 5 (CDC Only): Grant Log Read Authority

​Step 6 (CDC Only): Set the Db2 Instance

​Privilege Summary

​Schema Extractor

​Required Parameters

​Optional Parameters

​Data Extractor

​Required Parameters

​Optional Parameters

​Change Extractor

​Required Parameters

​Position Parameters

​Optional Parameters

​Connection String

​Orchestrator Configuration

​Limitations

Overview

How it works

Prerequisites

Database Configuration

File System Access

Replication Considerations

Source Database Setup

Step 1: Create the Wirekite User

Step 2: Grant Read Access to Application Tables

Step 3 (CDC Only): Enable Archive Logging

Step 4 (CDC Only): Enable DATA CAPTURE CHANGES

Step 5 (CDC Only): Grant Log Read Authority

Step 6 (CDC Only): Set the Db2 Instance

Privilege Summary

Schema Extractor

Required Parameters

Optional Parameters

Data Extractor

Required Parameters

Optional Parameters

Change Extractor

Required Parameters

Position Parameters

Optional Parameters

Connection String

Orchestrator Configuration

Limitations