> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wirekite.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Db2

> This guide explains how to configure IBM Db2 (LUW) as a target database for Wirekite data loading and replication.

## 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

<Note>
  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.
</Note>

## 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:

```sql theme={null}
CREATE BUFFERPOOL bp32k IMMEDIATE SIZE 4000 PAGESIZE 32K;
CREATE TABLESPACE ts32k PAGESIZE 32K BUFFERPOOL bp32k;
CREATE USER TEMPORARY TABLESPACE tmp32k PAGESIZE 32K BUFFERPOOL bp32k;
```

4. **User Permissions**: A dedicated Wirekite user with table creation and DML privileges on the target schema (see [Privileges](#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.

<Note>
  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](#progress-tracking)).
</Note>

<Tip>
  Use the Wirekite cmdline tool or the Db2 CLP (`db2`) to verify connectivity before running the loaders.
</Tip>

***

## 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).

<Note>
  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.
</Note>

### Required Parameters

<ResponseField name="schemaFile" type="string" required>
  Path to the Wirekite schema file (`.skt`) generated by the Schema Extractor. Must be an absolute path.
</ResponseField>

<ResponseField name="createTableFile" type="string" required>
  Output file for CREATE TABLE statements. Includes both base tables and staging/merge tables for CDC operations.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file for Schema Loader operations.
</ResponseField>

### Optional Parameters

<ResponseField name="dropTableFile" type="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.
</ResponseField>

<ResponseField name="createIndexFile" type="string" default="none">
  Output file for secondary index definitions (`CREATE INDEX` / `CREATE UNIQUE INDEX`).
</ResponseField>

<ResponseField name="createConstraintFile" type="string" default="none">
  Output file for UNIQUE and CHECK constraint definitions.
</ResponseField>

<ResponseField name="createForeignKeyFile" type="string" default="none">
  Output file for FOREIGN KEY constraints. Db2 foreign keys are emitted as `NOT ENFORCED DISABLE QUERY OPTIMIZATION` — see the note below.
</ResponseField>

<ResponseField name="dropObjectsFile" type="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).
</ResponseField>

<ResponseField name="createMergeTables" type="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.
</ResponseField>

<ResponseField name="mergeTablesOnly" type="boolean" default="false">
  When `true`, only creates staging/merge tables, skipping base table creation. Useful for bounce-back scenarios.
</ResponseField>

<Warning>
  **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.
</Warning>

<Note>
  **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.
</Note>

***

## 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.

<Note>
  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.
</Note>

### Required Parameters

<ResponseField name="dsnFile" type="string" required>
  Path to a file containing the Db2 connection string (see [Connection String](#connection-string)).
</ResponseField>

<ResponseField name="inputDirectory" type="string" required>
  Directory containing data files (`.dkt`) to load. Files are processed in parallel based on `maxThreads`.
</ResponseField>

<ResponseField name="schemaFile" type="string" required>
  Path to the Wirekite schema file used by the Schema Loader. Required for table structure information.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file for Data Loader operations.
</ResponseField>

### Optional Parameters

<ResponseField name="maxThreads" type="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.
</ResponseField>

<ResponseField name="db2BatchRows" type="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`).
</ResponseField>

<ResponseField name="hexEncoding" type="boolean" default="false">
  Set to `true` if data was extracted using hex encoding instead of base64.
</ResponseField>

<Note>
  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).
</Note>

<Tip>
  For best performance, set `maxThreads` close to the number of CPU cores available on the loader host.
</Tip>

***

## 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`.

<Note>
  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.
</Note>

### Required Parameters

<ResponseField name="dsnFile" type="string" required>
  Path to a file containing the Db2 connection string.
</ResponseField>

<ResponseField name="inputDirectory" type="string" required>
  Directory containing change files (`.ckt`) from the Change Extractor.
</ResponseField>

<ResponseField name="workDirectory" type="string" required>
  Working directory for progress/crash-recovery state. Must be writable.
</ResponseField>

<ResponseField name="schemaFile" type="string" required>
  Path to the Wirekite schema file for table structure information.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file for Change Loader operations.
</ResponseField>

### Optional Parameters

<ResponseField name="maxFilesPerBatch" type="integer" default="60">
  Maximum number of change files aggregated into a single collapsed batch before executing `MERGE` operations.
</ResponseField>

<ResponseField name="populateWorkers" type="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.
</ResponseField>

<ResponseField name="hexEncoding" type="boolean" default="false">
  Set to `true` if changes were extracted using hex encoding instead of base64.
</ResponseField>

<ResponseField name="logDetail" type="string" default="basic">
  Set to `verbose` to emit per-table insert/update/delete breakdowns to the load-statistics charts. Accepts `basic` or `verbose`.
</ResponseField>

<ResponseField name="resetProgress" type="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.
</ResponseField>

<Warning>
  The Change Loader should not start until the Data Loader has successfully completed the initial full load.
</Warning>

<Note>
  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).
</Note>

***

## 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`:

| File                      | Loader        | Purpose                                                     |
| ------------------------- | ------------- | ----------------------------------------------------------- |
| `wirekite_progress_dl.db` | Data Loader   | Per-file load progress, resume, "already-complete" skipping |
| `wirekite_progress_cl.db` | Change Loader | Per-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:

```sql theme={null}
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:

```sql theme={null}
-- 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;
```

<Note>
  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.
</Note>

### Privilege Summary

| Privilege                                                   | Required For                                                                    |
| ----------------------------------------------------------- | ------------------------------------------------------------------------------- |
| `CONNECT`                                                   | Basic connectivity                                                              |
| `CREATETAB`                                                 | Creating base and `_wkm` tables                                                 |
| `CREATEIN` / `DROPIN` on schema                             | `CREATE TABLE` / `DROP TABLE IF EXISTS` in the target schema                    |
| `ALTERIN` on schema                                         | `ALTER TABLE ... VOLATILE`, `ADD CONSTRAINT`                                    |
| `SELECTIN` / `INSERTIN` / `UPDATEIN` / `DELETEIN` on schema | Data load `INSERT`; change-apply `MERGE` (insert/update/delete + staging reads) |
| `USE OF TABLESPACE`                                         | Only under `RESTRICTIVE` Db2 (data tablespace + `ts32k`)                        |
| `DBADM` or `SQLADM`                                         | `RUNSTATS` 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](/run/execution/commandline).

***

## 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](/datatypes/db2) for how each source type is mapped into Db2.