IBM DB2 source for ELT & CDC


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

Setup steps

Create the sync user

Create the user at the operating system level on the DB2 server host:

sudo useradd integrateio
sudo echo 'integrateio:<password>' | sudo chpasswd

Then connect to the database and grant database access privileges:

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

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:

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

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.

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

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.

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:

db2 connect to <your_database>
db2 -tvf setup.sql

What the setup SQL creates

The generated script contains 6 statements per table:

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.
An index on the CD table’s sequence columns.
A MERGE that registers the table in IIO_ASN.IBMSNAP_REGISTER.
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:

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.

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.

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.

Getting Started

Pipelines

Sources

Destinations

Monitoring

Security

Transforms

Data Observability

IBM DB2 source for ELT & CDC

Setting up IBM DB2 CDC for ELT & CDC

Requirements

Features

Setup steps

What the setup SQL creates

Re-running the setup SQL

Limitations

​Setting up IBM DB2 CDC for ELT & CDC

​Requirements

​Features

​Setup steps

​What the setup SQL creates

​Re-running the setup SQL

​Limitations

Setting up IBM DB2 CDC for ELT & CDC

Requirements

Features

Setup steps

What the setup SQL creates

Re-running the setup SQL

Limitations