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

# IBM DB2 source for ELT & CDC

> How to set up IBM DB2 as a CDC source in Integrate.io ELT & CDC, covering the sync user, control tables, and the generated per-table setup SQL.

|                           |                                 |
| :------------------------ | :------------------------------ |
| **Type**                  | Trigger-based (ASN) replication |
| **Supported Replication** | Initial Sync, Continuous Sync   |
| **Authentication Type**   | Password Authentication         |

## Setting up IBM DB2 CDC for ELT & CDC

ELT & CDC uses ASN-based change data capture for IBM DB2. Triggers on each source table write every `INSERT`, `UPDATE`, and `DELETE` into a CD (Change Data) table under a dedicated CDC schema (`IIO_ASN`). The connector reads changes from the CD tables and deletes records it has processed.

The sync user only needs read access to your source tables. All CDC objects live in the `IIO_ASN` schema, so nothing is created in your source schema.

### Requirements

* IBM DB2 for Linux, UNIX, and Windows (LUW) **9.7** or above. The generated setup SQL uses `CREATE OR REPLACE TRIGGER`.
* A dedicated sync user with `CONNECT` and read access to the source and CDC schemas.
* The one-time control table setup and the per-table setup SQL must be run by a user who can create objects in the database (for example, a database administrator).

### Features

| Feature                | Supported | Notes                               |
| :--------------------- | :-------- | :---------------------------------- |
| Full (Historical) sync | Yes       |                                     |
| Incremental sync       | Yes       |                                     |
| Replicate DELETE       | Yes       |                                     |
| SSL support            | Yes       |                                     |
| SSH tunnel             | Yes       | [SSH tunnel guide](/docs/cdc/ssh-tunnel) |

## Setup steps

<Steps>
  <Step title="Create the sync user">
    Create the user at the operating system level on the DB2 server host:

    ```bash theme={null}
    sudo useradd integrateio
    sudo echo 'integrateio:<password>' | sudo chpasswd
    ```

    Then connect to the database and grant database access privileges:

    ```sql theme={null}
    -- Grant CONNECT privilege to allow database connections
    GRANT CONNECT ON DATABASE TO USER integrateio;

    -- Grant DATAACCESS privilege for read access to all tables
    GRANT DATAACCESS ON DATABASE TO USER integrateio;
    ```
  </Step>

  <Step title="Grant schema privileges">
    Grant the sync user read access to the source schema and to the CDC schema. Also grant delete access on the CDC schema so the connector can clean up change records it has processed:

    ```sql theme={null}
    -- Grant read access to source schema
    GRANT SELECTIN ON SCHEMA <schema> TO USER integrateio;

    -- Grant read access to CDC schema (control tables and CD tables)
    GRANT SELECTIN ON SCHEMA IIO_ASN TO USER integrateio;

    -- Grant delete on CDC schema (automatic cleanup of processed change records)
    GRANT DELETEIN ON SCHEMA IIO_ASN TO USER integrateio;
    ```
  </Step>

  <Step title="Create the CDC control tables (one-time)">
    Run the following once per database to create the `IIO_ASN` schema, the ASN control tables, and the sequence the capture triggers use. The source creation wizard shows the same SQL with your schema filled in.

    ```sql expandable theme={null}
    -- Create the IIO_ASN schema
    CREATE SCHEMA IIO_ASN AUTHORIZATION <your_schema>;

    -- Registration catalog
    CREATE TABLE IIO_ASN.IBMSNAP_REGISTER (
      SOURCE_OWNER     VARCHAR(128) NOT NULL,
      SOURCE_TABLE     VARCHAR(128) NOT NULL,
      SOURCE_VIEW_QUAL SMALLINT NOT NULL DEFAULT 0,
      GLOBAL_RECORD    CHAR(1) NOT NULL DEFAULT 'Y',
      SOURCE_STRUCTURE SMALLINT NOT NULL DEFAULT 1,
      SOURCE_CONDENSED CHAR(1) NOT NULL DEFAULT 'Y',
      SOURCE_COMPLETE  CHAR(1) NOT NULL DEFAULT 'Y',
      CD_OWNER         VARCHAR(128),
      CD_TABLE         VARCHAR(128),
      PHYS_CHANGE_OWNER VARCHAR(128),
      PHYS_CHANGE_TABLE VARCHAR(128),
      CD_OLD_SYNCHPOINT VARCHAR(254),
      CD_NEW_SYNCHPOINT VARCHAR(254),
      DISABLE_REFRESH  SMALLINT NOT NULL DEFAULT 0,
      CCD_OWNER        VARCHAR(128),
      CCD_TABLE        VARCHAR(128),
      CCD_OLD_SYNCHPOINT VARCHAR(254),
      CCD_NEW_SYNCHPOINT VARCHAR(254),
      CHG_UPD_TO_DEL_INS CHAR(1) NOT NULL DEFAULT 'N',
      RECAPTURE        CHAR(1) NOT NULL DEFAULT 'N',
      OPTION_FLAGS     VARCHAR(4) NOT NULL DEFAULT '0000',
      STOP_ON_ERROR    CHAR(1) NOT NULL DEFAULT 'Y',
      STATE            CHAR(1) NOT NULL DEFAULT 'A',
      STATE_INFO       CHAR(8)
    );

    -- Prune control table
    CREATE TABLE IIO_ASN.IBMSNAP_PRUNCNTL (
      TARGET_SERVER    CHAR(18) NOT NULL,
      TARGET_OWNER     VARCHAR(128) NOT NULL,
      TARGET_TABLE     VARCHAR(128) NOT NULL,
      SYNCHTIME        TIMESTAMP,
      SYNCHPOINT       VARCHAR(254),
      SOURCE_OWNER     VARCHAR(128) NOT NULL,
      SOURCE_TABLE     VARCHAR(128) NOT NULL,
      SOURCE_VIEW_QUAL SMALLINT NOT NULL DEFAULT 0,
      APPLY_QUAL       CHAR(18) NOT NULL DEFAULT '0001',
      SET_NAME         CHAR(18) NOT NULL DEFAULT '0001',
      CNTL_SERVER      CHAR(18),
      CNTL_ALIAS       CHAR(8),
      MAP_ID           VARCHAR(10) NOT NULL DEFAULT '1'
    );

    -- Signal table
    CREATE TABLE IIO_ASN.IBMSNAP_SIGNAL (
      SIGNAL_TIME      TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
      SIGNAL_TYPE      VARCHAR(30) NOT NULL,
      SIGNAL_SUBTYPE   VARCHAR(30),
      SIGNAL_INPUT_IN  VARCHAR(300),
      SIGNAL_STATE     CHAR(1) NOT NULL DEFAULT 'P',
      SIGNAL_LSN       VARCHAR(254)
    );

    -- Commit sequence generator
    CREATE SEQUENCE IIO_ASN.COMMITSEQ_GEN AS BIGINT
      START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE;
    ```

    If you run DB2 for LUW with an IIDR license, you can instead use the built-in ASNCDC management user-defined functions (UDFs) and the ASN Capture agent to populate CD tables from the transaction log. The source creation wizard shows the commands for that variant.
  </Step>

  <Step title="Create the source">
    In the Integrate.io ELT & CDC dashboard, create a new IBM DB2 source and enter the host, port (default `50000`), database user, password, database, and schema. The connector supports SSH tunnel and SSL connections.
  </Step>

  <Step title="Run the per-table setup SQL">
    When you select tables in the pipeline schema step, click **Generate CDC Setup SQL**. Integrate.io connects to your database, checks which of the selected tables already have CDC configured, and generates SQL only for the tables that still need setup.

    Run the generated script as a user who can create objects in the `IIO_ASN` schema and create triggers on the source tables. With the DB2 command line processor:

    ```bash theme={null}
    db2 connect to <your_database>
    db2 -tvf setup.sql
    ```
  </Step>
</Steps>

## What the setup SQL creates

The generated script contains 6 statements per table:

1. A CD table `IIO_ASN.CD_<TABLE>` that stores captured changes together with the `IBMSNAP_COMMITSEQ`, `IBMSNAP_INTENTSEQ`, `IBMSNAP_OPERATION`, and `IBMSNAP_LOGMARKER` control columns.
2. An index on the CD table's sequence columns.
3. A `MERGE` that registers the table in `IIO_ASN.IBMSNAP_REGISTER`.
4. Three `CREATE OR REPLACE TRIGGER` statements, one each for `INSERT`, `UPDATE`, and `DELETE`, that copy changed rows into the CD table.

For example, the trigger generated for inserts on a table `MYSCHEMA.ORDERS` looks like this:

```sql theme={null}
CREATE OR REPLACE TRIGGER "IIO_ASN"."CDC_ORDERS_INSERT"
  AFTER INSERT ON "MYSCHEMA"."ORDERS"
  REFERENCING NEW AS N
  FOR EACH ROW
  INSERT INTO "IIO_ASN"."CD_ORDERS"
    ("IBMSNAP_COMMITSEQ", "IBMSNAP_INTENTSEQ", "IBMSNAP_OPERATION", "IBMSNAP_LOGMARKER",
     "ORDER_ID", "STATUS")
  VALUES
    (CAST(LPAD(CAST(NEXT VALUE FOR "IIO_ASN"."COMMITSEQ_GEN" AS VARCHAR(10)), 10, '0') AS CHAR(10) FOR BIT DATA),
     CAST(LPAD(CAST(NEXT VALUE FOR "IIO_ASN"."COMMITSEQ_GEN" AS VARCHAR(10)), 10, '0') AS CHAR(10) FOR BIT DATA),
     'I', CURRENT TIMESTAMP,
     N."ORDER_ID", N."STATUS");
```

Every statement in the script is a single semicolon-terminated statement, so it runs as-is on the DB2 command line processor with the default statement terminator (`db2 -tvf`).

## Re-running the setup SQL

The script is safe to re-run:

* `CREATE OR REPLACE TRIGGER` re-creates each trigger atomically, so there is no window in which changes go uncaptured.
* Re-running the `CREATE TABLE`, `CREATE INDEX`, and `MERGE` statements against existing objects returns `SQL0601N`, `SQL0605W`, and `SQL0100W`. These codes are expected on a re-run and harmless.

<Note>
  Replacing a trigger that was created by a different user requires ownership of the trigger or `DBADM` authority. This only matters when a different administrator re-runs the script; the sync user's privileges are not affected.
</Note>

## Limitations

* Table names, column names, and schema names must not contain a semicolon. SQL generation fails with an error for such identifiers because a semicolon would split the generated statement.
* IBM DB2 for i (AS/400) is a separate connector and does not use this setup.