DB2 Database Environment Preparation

Database-Level Prerequisites

Every DB2 source database must meet these requirements before table-level CDC can capture changes.

Enable Archive Logging

ASN Capture requires archival logging.

db2 update db cfg for DB_NAME using LOGARCHMETH1 DISK:/database/logs/
db2 backup db DB_NAME to /dev/null
db2 restart db DB_NAME

Verify:

db2 get db cfg for DB_NAME | egrep -i "LOGARCHMETH|Backup pending|Rollforward pending"

Expected values are LOGARCHMETH1 = DISK:/database/logs/, Backup pending = NO, and Rollforward pending = NO.

Compile and Install the ASN Management UDF

The current container includes Debezium ASN tools:

cd /asncdctools/src
/opt/ibm/db2/V11.5/samples/c/bldrtn asncdc
cp /asncdctools/src/asncdc /database/config/db2inst1/sqllib/function

Install the UDF:

db2 connect to DB_NAME
db2 -tvmf /asncdctools/src/asncdc_UDF.sql

Create ASN Control Tables

db2 connect to DB_NAME
db2 -tvmf /asncdctools/src/asncdctables.sql
db2 -tvmf /asncdctools/src/asncdcaddremove.sql

You can also run the official setup script:

cd /asncdctools/src
./dbsetup.sh DB_NAME

Note

dbsetup.sh binds objects, backs up and restarts the database, creates the UDF and control tables, and starts the agent. Do not run it again on an initialized database; first check whether the ASNCDC schema exists.

Start the ASN Capture Agent

CONNECT TO DB_NAME;
VALUES ASNCDC.ASNCDCSERVICES('start','asncdc');
VALUES ASNCDC.ASNCDCSERVICES('status','asncdc');

Verify the process:

ps -ef | grep -i "[a]sncap.*DB_NAME"

The status should include HoldLThread, AdminThread, PruneThread, WorkerThread, and LogrdThread. If startup returns ASN0539E, check the archive-log configuration.

Enable Table-Level CDC

Apply both operations to every business table.

Enable DB2 Data Capture

ALTER TABLE SCHEMA_NAME.TABLE_NAME DATA CAPTURE CHANGES;

Verify DATACAPTURE = Y:

SELECT TABSCHEMA, TABNAME, DATACAPTURE FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'SCHEMA_NAME' AND TABNAME = 'TABLE_NAME';

Add the Table to ASN Capture Mode

CALL ASNCDC.ADDTABLE('SCHEMA_NAME', 'TABLE_NAME');

This creates ASNCDC.CDC_SCHEMA_NAME_TABLE_NAME, for example ASNCDC.CDC_DB2INST1_ECOMMERCE_ORDER_EVENT.

Activate Registration and Reload Capture

If the registration created by ADDTABLE is not active, activate and reinitialize it:

UPDATE ASNCDC.IBMSNAP_REGISTER SET STATE = 'A'
WHERE SOURCE_OWNER = 'SCHEMA_NAME' AND SOURCE_TABLE = 'TABLE_NAME';
COMMIT;
VALUES ASNCDC.ASNCDCSERVICES('reinit','asncdc');
VALUES ASNCDC.ASNCDCSERVICES('status','asncdc');

Use IN (...) for multiple tables.

Final Validation

Validate Registration

SELECT SOURCE_OWNER, SOURCE_TABLE, CD_OWNER, CD_TABLE, STATE
FROM ASNCDC.IBMSNAP_REGISTER
WHERE SOURCE_OWNER = 'SCHEMA_NAME' AND SOURCE_TABLE = 'TABLE_NAME';

Expect STATE = A, CD_OWNER = ASNCDC, and CD_TABLE = CDC_SCHEMA_NAME_TABLE_NAME.

Validate the Capture Trace

SELECT OPERATION, TRACE_TIME, SUBSTR(DESCRIPTION, 1, 240) AS DESCRIPTION
FROM ASNCDC.IBMSNAP_CAPTRACE ORDER BY TRACE_TIME DESC FETCH FIRST 20 ROWS ONLY;

Expect a message such as capturing changes for "N" registrations; the target table must not be stopped or inactive.

Validate the Change-Data Table

SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'ASNCDC' AND TABNAME = 'CDC_SCHEMA_NAME_TABLE_NAME';

Short Command Template for the Current Environment

Run in the db2-cdc-remote-debug container:

su - db2inst1
cd /asncdctools/src
./dbsetup.sh DB_NAME
db2 connect to DB_NAME
db2 update db cfg for DB_NAME using LOGARCHMETH1 DISK:/database/logs/
db2 backup db DB_NAME to /dev/null
db2 restart db DB_NAME
db2 "ALTER TABLE SCHEMA_NAME.TABLE_NAME DATA CAPTURE CHANGES"
db2 "CALL ASNCDC.ADDTABLE('SCHEMA_NAME', 'TABLE_NAME')"
db2 "UPDATE ASNCDC.IBMSNAP_REGISTER SET STATE='A' WHERE SOURCE_OWNER='SCHEMA_NAME' AND SOURCE_TABLE='TABLE_NAME'"
db2 "COMMIT"
db2 "VALUES ASNCDC.ASNCDCSERVICES('start','asncdc')"
db2 "VALUES ASNCDC.ASNCDCSERVICES('reinit','asncdc')"

Limitations and Notes

• SQL Replication does not support CDC for BOOLEAN columns; snapshot the table or change the type.
• Maintain the matching ASNCDC.CDC_* table after source-table DDL changes.
• Install the DB2 JDBC driver and license in the Flink/Debezium runtime.
• ADDTABLE uses the current source schema; confirm it before enabling CDC.
• Monitor archive-log capacity because a full log location can stop Capture.

Deployment Topology Differences

Deployment	Required configuration	Recommended configuration	Risk	Validation
DB2 LUW standalone	SQL Replication/ASN, UDF, ASN agent, and table capture mode	Monitor ASN delay, CPU, and change-data-table growth	Capture polling adds source load	VALUES ASNCDC.ASNCDCSERVICES('status','asncdc');
HADR/HA	Available ASN Capture and change-data tables on the active node	Verify ASN service, capture mode, and log continuity after failover	Failover can require revalidation or rebuilding Capture	ASN status and table capture state
Managed DB2	Platform support for SQL Replication/ASN objects and UDFs	Confirm UDF installation and change-data-table access in advance	CDC prerequisites fail if UDF installation or the ASN agent is prohibited	Platform permission check and ASN status