Skip to main content

​
Overview

Wirekite supports IBM Db2 for Linux, UNIX and Windows (LUW) 11.5 and above as a target database for:
  • Schema Loading - Create target tables from Wirekite’s intermediate schema format
  • Data Loading - Bulk load extracted data into Db2 tables
  • Change Loading (CDC) - Apply ongoing changes using Db2’s native MERGE statement
The Db2 loaders use the go_ibm_db driver (IBM CLI / clidriver). The Change Loader applies changes through per-table staging tables (_wkm suffix) and a single MERGE per batch, giving idempotent, exactly-once change application.

​
Prerequisites

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

​
Database Configuration

  1. Version: Db2 (LUW) 11.5 or above. Db2 11.5 is required for native BOOLEAN, BINARY/VARBINARY, and DROP TABLE IF EXISTS.
  2. Primary keys: Every table loaded into Db2 must have a primary key. The Schema Loader errors on any table without one, because change application and validation identify rows by primary key.
  3. 32K tablespace for LOBs: Tables containing CLOB/BLOB columns are placed in a 32K-page tablespace named ts32k. Create this tablespace (with a matching 32K bufferpool and temp tablespace) before loading a schema that contains LOB columns. For example:
CREATE BUFFERPOOL bp32k IMMEDIATE SIZE 4000 PAGESIZE 32K;
CREATE TABLESPACE ts32k PAGESIZE 32K BUFFERPOOL bp32k;
CREATE USER TEMPORARY TABLESPACE tmp32k PAGESIZE 32K BUFFERPOOL bp32k;
  1. User Permissions: A dedicated Wirekite user with table creation and DML privileges on the target schema (see Privileges below).

​
File System Access

  • Ensure the user running Wirekite has write permissions to the input and work directories.
  • Verify sufficient disk space for intermediate data and change files.
Unlike the Oracle and PostgreSQL targets, Wirekite does not create metadata tables (wirekite_progress, wirekite_action) on the Db2 server. Progress and crash-recovery state are kept in a local SQLite database in the loader’s input directory (see Progress Tracking).
Use the Wirekite cmdline tool or the Db2 CLP (db2) to verify connectivity before running the loaders.

​
Schema Loader

The Schema Loader reads Wirekite’s intermediate schema format (.skt file) and generates Db2-appropriate DDL statements. It makes no database connection — it only writes SQL files, which are applied later (for example by the Wirekite cmdline tool).
The Schema Loader generates both base tables and staging/merge tables (with _wkm suffix) for CDC operations. Db2 forbids $ in unquoted identifiers, so Wirekite uses _wkm (staging table), _wkpk (primary-key copy columns), and wk_merge_op (operation marker) rather than the $wkm convention used by some other targets.

​
Required Parameters

​
schemaFile
string
required
Path to the Wirekite schema file (.skt) generated by the Schema Extractor. Must be an absolute path.
​
createTableFile
string
required
Output file for CREATE TABLE statements. Includes both base tables and staging/merge tables for CDC operations.
​
logFile
string
required
Absolute path to the log file for Schema Loader operations.

​
Optional Parameters

​
dropTableFile
string
default:"none"
Output file for DROP TABLE IF EXISTS statements (base and _wkm tables). Set to none to skip generation. In Db2, dropping a table cascade-drops its indexes, constraints, and foreign keys.
​
createIndexFile
string
default:"none"
Output file for secondary index definitions (CREATE INDEX / CREATE UNIQUE INDEX).
​
createConstraintFile
string
default:"none"
Output file for UNIQUE and CHECK constraint definitions.
​
createForeignKeyFile
string
default:"none"
Output file for FOREIGN KEY constraints. Db2 foreign keys are emitted as NOT ENFORCED DISABLE QUERY OPTIMIZATION — see the note below.
​
dropObjectsFile
string
default:"none"
Output file for dropping non-table objects. On Db2 this is emitted as a comment-only no-op (Db2 targets replicate tables and their indexes/constraints only).
​
createMergeTables
boolean
default:"true"
When true, generates staging/merge tables (_wkm suffix) required for CDC operations. Set to false for data-only loads without change capture.
​
mergeTablesOnly
boolean
default:"false"
When true, only creates staging/merge tables, skipping base table creation. Useful for bounce-back scenarios.
Foreign keys are not enforced on the Db2 target. They are emitted as ... NOT ENFORCED DISABLE QUERY OPTIMIZATION so that the parallel, out-of-order data load and concurrent change apply cannot raise referential-integrity violations. The source database remains the integrity authority. This matches the behavior of Wirekite’s Spanner target.
Db2-specific schema behavior:
  • Identifiers are emitted unquoted, so Db2 folds schema, table, and column names to UPPERCASE.
  • DECIMAL precision is capped at Db2’s maximum of 31. A non-primary-key numeric with higher (or unbounded) precision is stored as text (VARCHAR) to preserve the exact value; a primary-key numeric is capped to DECIMAL(31,s).
  • LOB columns (CLOB/BLOB) are emitted with INLINE LENGTH 4000 and their tables are placed IN ts32k. A CLOB/BLOB cannot be a primary key, so such a PK column is coerced to VARCHAR(4000).
  • String lengths use CODEUNITS32 (character counts). Oversized character columns become CLOB; oversized binary columns become BLOB.
  • Non-table objects (views, procedures, triggers, sequences, synonyms) are not created on Db2.

​
Data Loader

The Data Loader reads Wirekite’s intermediate data format (.dkt files) and loads records into Db2 target tables using batched multi-row INSERT statements over the go_ibm_db connection.
Db2’s LOAD/IMPORT utilities read server-side files and cannot see a remote loader’s data, so Wirekite loads via bind-parameter INSERT batches. Each batch commits independently (implicit autocommit) to keep the Db2 active log bounded.

​
Required Parameters

​
dsnFile
string
required
Path to a file containing the Db2 connection string (see Connection String).
​
inputDirectory
string
required
Directory containing data files (.dkt) to load. Files are processed in parallel based on maxThreads.
​
schemaFile
string
required
Path to the Wirekite schema file used by the Schema Loader. Required for table structure information.
​
logFile
string
required
Absolute path to the log file for Data Loader operations.

​
Optional Parameters

​
maxThreads
integer
default:"5"
Maximum number of parallel threads for loading tables. Each thread loads one data file at a time over its own database connection.
​
db2BatchRows
integer
default:"2000"
Desired number of rows per INSERT batch. The effective batch size is capped so the statement stays under Db2’s parameter-marker limit (roughly 30000 / number_of_columns).
​
hexEncoding
boolean
default:"false"
Set to true if data was extracted using hex encoding instead of base64.
After loading each table, the Data Loader runs RUNSTATS (CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE ... WITH DISTRIBUTION AND INDEXES ALL')) so that the Change Loader’s first MERGE compiles an efficient primary-key index plan. This requires DBADM or SQLADM authority (satisfied by the dev-mode grants below).
For best performance, set maxThreads close to the number of CPU cores available on the loader host.

​
Change Loader

The Change Loader applies ongoing data changes (INSERT, UPDATE, DELETE) to Db2 target tables. For each batch it collapses the change files down to one final operation per primary key, populates a per-table staging table (_wkm), and applies a single native Db2 MERGE.
The Change Loader requires the staging/merge tables to be created by the Schema Loader (createMergeTables=true). The MERGE is idempotent, so re-processing a partially applied batch after a crash is a no-op for already-applied rows.

​
Required Parameters

​
dsnFile
string
required
Path to a file containing the Db2 connection string.
​
inputDirectory
string
required
Directory containing change files (.ckt) from the Change Extractor.
​
workDirectory
string
required
Working directory for progress/crash-recovery state. Must be writable.
​
schemaFile
string
required
Path to the Wirekite schema file for table structure information.
​
logFile
string
required
Absolute path to the log file for Change Loader operations.

​
Optional Parameters

​
maxFilesPerBatch
integer
default:"60"
Maximum number of change files aggregated into a single collapsed batch before executing MERGE operations.
​
populateWorkers
integer
default:"8"
Number of parallel workers that populate a table’s staging table over disjoint primary-key ranges. Parallel populate engages only above ~4000 rows for a table.
​
hexEncoding
boolean
default:"false"
Set to true if changes were extracted using hex encoding instead of base64.
​
logDetail
string
default:"basic"
Set to verbose to emit per-table insert/update/delete breakdowns to the load-statistics charts. Accepts basic or verbose.
​
resetProgress
boolean
default:"false"
When true, purges the Change Loader’s progress state and reprocesses change files from the start. Used by the “restart replication” flow. The Orchestrator sets this automatically — set it manually only when running the Change Loader standalone.
The Change Loader should not start until the Data Loader has successfully completed the initial full load.
The MERGE applies four operation types: i (insert), u (update), d (delete), and r (resync/upsert — matches both the update and insert branches). Unchanged columns in a sparse update are preserved on the target rather than overwritten. Changes within a batch are applied in primary-key-ordered chunks and retried automatically on transient Db2 lock-timeout/deadlock errors (SQLSTATE 40001).

​
Connection String

The Db2 loaders connect through the go_ibm_db driver, which uses 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-target.example.com;PORT=50000;PROTOCOL=TCPIP;DATABASE=APP_DB;UID=wirekite;PWD=secretpass
The default Db2 port is 50000. When Wirekite generates the DSN through the Web GUI or API, it also injects ConnectTimeout=15 (seconds) so a failed connection does not hang indefinitely. The target schema is taken from the schema file, not the connection string.

​
Progress Tracking

Wirekite does not create metadata tables on the Db2 server. Instead, each loader keeps its progress state in a local SQLite database in its inputDirectory:
FileLoaderPurpose
wirekite_progress_dl.dbData LoaderPer-file load progress, resume, “already-complete” skipping
wirekite_progress_cl.dbChange LoaderPer-batch change-apply progress and crash recovery
Each file contains a wirekite_progress table (one row per processed file) and a wirekite_action control row (RUN/PAUSE/STOP) that the Change Loader polls for lifecycle control. Exactly-once behavior comes from the combination of these marks and the idempotent MERGE.

​
Privileges

Create a dedicated Wirekite user (for example WIREKITE) and grant it the privileges below.

​
Development / Self-Provisioned

For a dedicated, self-managed Db2 instance, the broad grant set is simplest: 
GRANT CONNECT ON DATABASE TO USER wirekite;
GRANT DBADM, DATAACCESS, ACCESSCTRL ON DATABASE TO USER wirekite;
DBADM also covers the RUNSTATS call the Data Loader issues after each table load.

​
Production Least-Privilege

For a shared or production Db2, grant only what the loaders use. Run these as a DBADM/SECADM operator: 
-- Connectivity
GRANT CONNECT ON DATABASE TO USER wirekite;

-- Create the base and _wkm tables
GRANT CREATETAB ON DATABASE TO USER wirekite;

-- DML + DDL within the target schema
GRANT CREATEIN, ALTERIN, DROPIN, SELECTIN, INSERTIN, UPDATEIN, DELETEIN
    ON SCHEMA <target_schema> TO USER wirekite;
Under a fully RESTRICTIVE Db2 (where PUBLIC does not hold tablespace USE), also grant USE OF TABLESPACE on the data tablespace (for example USERSPACE1) and on ts32k for LOB tables. The RUNSTATS step run by the Data Loader requires DBADM or SQLADM; if you cannot grant that, disable it or run it manually.

​
Privilege Summary

PrivilegeRequired For
CONNECTBasic connectivity
CREATETABCreating base and _wkm tables
CREATEIN / DROPIN on schemaCREATE TABLE / DROP TABLE IF EXISTS in the target schema
ALTERIN on schemaALTER TABLE ... VOLATILE, ADD CONSTRAINT
SELECTIN / INSERTIN / UPDATEIN / DELETEIN on schemaData load INSERT; change-apply MERGE (insert/update/delete + staging reads)
USE OF TABLESPACEOnly under RESTRICTIVE Db2 (data tablespace + ts32k)
DBADM or SQLADMRUNSTATS after data load

​
Orchestrator Configuration

When using the Wirekite Orchestrator, prefix target parameters with target.schema., target.data., or target.change. depending on the operation. Example orchestrator configuration for Db2 target: 
# Main configuration
source=postgres
target=db2

# Schema loading
target.schema.schemaFile=/opt/wirekite/output/schema/wirekite_schema.skt
target.schema.createTableFile=/opt/wirekite/output/schema/create_tables.sql
target.schema.createIndexFile=/opt/wirekite/output/schema/indexes.sql
target.schema.createConstraintFile=/opt/wirekite/output/schema/constraints.sql
target.schema.createForeignKeyFile=/opt/wirekite/output/schema/foreign_keys.sql
target.schema.logFile=/var/log/wirekite/schema-loader.log

# Data loading
target.data.dsnFile=/opt/wirekite/config/db2.dsn
target.data.inputDirectory=/opt/wirekite/output/data
target.data.schemaFile=/opt/wirekite/output/schema/wirekite_schema.skt
target.data.logFile=/var/log/wirekite/data-loader.log
target.data.maxThreads=8
target.data.db2BatchRows=2000

# Change loading (CDC)
target.change.dsnFile=/opt/wirekite/config/db2.dsn
target.change.inputDirectory=/opt/wirekite/output/changes
target.change.workDirectory=/opt/wirekite/work
target.change.schemaFile=/opt/wirekite/output/schema/wirekite_schema.skt
target.change.logFile=/var/log/wirekite/change-loader.log
target.change.maxFilesPerBatch=60
target.change.populateWorkers=8
For complete Orchestrator documentation, see the Execution Guide.

​
Limitations

  • Primary key required on every table — a table with no primary key is a hard error.
  • Foreign keys are not enforced (NOT ENFORCED); the source is the integrity authority.
  • Non-table objects (views, materialized views, procedures, functions, triggers, packages, sequences, synonyms) are not created on Db2.
  • DECIMAL precision is capped at 31; wider non-PK numerics are preserved as text.
  • TIME values are stored as VARCHAR(64) because Db2 TIME has no sub-second precision; timestamp-with-time-zone values are normalized to UTC TIMESTAMP.
  • UNIQUE constraints require NOT NULL columns in Db2; model a nullable uniqueness rule as a unique index instead.
  • See the Db2 datatype matrix for how each source type is mapped into Db2.