> ## 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 source database for Wirekite data extraction and replication.

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

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

<Note>
  Db2 is supported as a **Data** (one-time load) and **Replicate** (CDC) source. It is not currently a [Target Sync](/run/targetsync) source. As a Wirekite **target**, Db2 additionally supports Target Sync — see the [Db2 target guide](/targetguides/db2target).
</Note>

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

<Warning>
  **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](/datatypes/db2).
</Warning>

## Prerequisites

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

### Database Configuration

1. **Version**: Db2 (LUW) 11.5 or above.
2. **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.
3. **`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

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

***

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

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

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

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

```sql theme={null}
ALTER TABLE <app_schema>.table1 DATA CAPTURE CHANGES;
ALTER TABLE <app_schema>.table2 DATA CAPTURE CHANGES;
-- repeat for each table to be tracked
```

**Verify:**

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

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

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

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

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

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

***

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

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

<ResponseField name="tablesFile" type="string" required>
  Path to a file listing the tables to extract, one per line in `schema.table` format.
</ResponseField>

<ResponseField name="outputDirectory" type="string" required>
  Absolute path to the directory where Wirekite will write the schema file (`wirekite_schema.skt`). The directory must exist and be writable.
</ResponseField>

<ResponseField name="logFile" type="string" required>
  Absolute path to the log file where the Schema Extractor will write operational logs.
</ResponseField>

### Optional Parameters

<ResponseField name="schemaRename" type="string" default="none">
  Rename source schemas in the output. Format: `sourceSchema:targetSchema,source2:target2`. Useful when the target database uses a different schema name.
</ResponseField>

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

***

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

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

<ResponseField name="tablesFile" type="string" required>
  Path to a file listing the tables to extract, one per line in `schema.table` format.
</ResponseField>

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

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

### Optional Parameters

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

<ResponseField name="maxRowsPerDump" type="integer" default="200000">
  Maximum number of rows written to each output file. Large tables are split into multiple files.
</ResponseField>

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

<ResponseField name="sortFiles" type="boolean" default="true">
  When `true`, sorts output files by table name for easier management and verification.
</ResponseField>

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

<ResponseField name="extractPauseMB" type="integer" default="5120">
  Pause extraction when free disk space on the output volume drops below this many megabytes (backpressure).
</ResponseField>

<ResponseField name="extractResumeMB" type="integer" default="8192">
  Resume extraction once free disk space rises back above this many megabytes.
</ResponseField>

<ResponseField name="schemaRename" type="string" default="none">
  Rename source schemas in the output. Format: `sourceSchema:targetSchema`.
</ResponseField>

***

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

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

### Required Parameters

<ResponseField name="dsnFile" type="string" required>
  Path to a file containing the Db2 connection string. (Alternatively, provide the string via the `connectionString` key.)
</ResponseField>

<ResponseField name="tablesFile" type="string" required>
  Path to a file listing the tables to track for changes, one per line in `schema.table` format.
</ResponseField>

<ResponseField name="outputDirectory" type="string" required>
  Absolute path to the directory where Wirekite will write change files (`N.ckt`).
</ResponseField>

### Position Parameters

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

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

### Optional Parameters

<ResponseField name="logFile" type="string" default="[outputDirectory]/change_extractor.log">
  Path to the log file for Change Extractor operations.
</ResponseField>

<ResponseField name="cktRowsPerFile" type="integer" default="50000">
  Number of change rows to write to each `.ckt` file before rotating to the next. Transactions never span multiple output files.
</ResponseField>

<ResponseField name="idleIntervalMs" type="integer" default="500">
  Milliseconds to sleep when the reader has caught up to the end of the log before polling again.
</ResponseField>

<ResponseField name="exitWhenIdle" type="boolean" default="false">
  When `true`, the extractor exits cleanly once it has caught up and stayed idle. When `false` (default), it runs continuously.
</ResponseField>

<ResponseField name="idleWaitSeconds" type="integer" default="0">
  Number of idle seconds to wait before `exitWhenIdle` triggers an exit.
</ResponseField>

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

<ResponseField name="db2Instance" type="string" default="db2inst1">
  The Db2 instance (`DB2INSTANCE`) that owns the database, required for `db2ReadLog`.
</ResponseField>

<ResponseField name="schemaRename" type="string" default="none">
  Rename the source schema in the output. Format: `sourceSchema:targetSchema`.
</ResponseField>

***

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

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

***

## 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](/datatypes/db2).