DB2 Log-based Incremental Ingestion Architecture

This document describes the design and implementation of DB2 log-based incremental ingestion.

For details on enabling SQL replication for DB2 tables, see How to Enable SQL Replication for DB2.

Once SQL replication is set for a table in DB2, the following two tables are created: change data table and target table.

Both the tables use different table space in the database. Only change data table is required for Infoworks DataFoundry to capture the CDC records. Target table is used to maintain the full view of the table where all versions of the record is maintained. If target table is not required, it can be dropped once the SQL replication is set.

DB2 Log-based Incremental Ingestion Architecture

• Source Table: The table for which the user configures the DB2 logging.
• DB2 log: The log file where all the DML transaction statements are stored. These log files can either be read by the built-in DB2 capture program (DB2 replication service) or by third party tools (Infosphere or Attunity CDC agent). This design is limited to reading logs only through capture program.
• Capture Program: Once the capture program starts on the DB2 server (stand alone or mainframe z/os), the DB2 logs are read, the change data is added in the Change data table and the commit information is added in the UoW (Unit of Work) table.
• Change data table: This table contains all the changes (inserts, updates and deletes) of the table. This table includes the offset associated for each commit of the DML (Data Manipulation Language) statement along with the original DML statement with all the columns.

Sample Table

• UoW table: This table contains information about each commit on the source table. This information includes commit timestamp and offset associated with each commit transaction.

Sample Table

Reading CDC from Change Data Tables

By default, the change data table (table delta) is created for each table in <change_schema>.<change_tableName>.

Change data is read by joining the UoW table and Change data table to get the DML along with the commit timestamp. This timestamp is used to get the next CDC start time.

Sample CDC Query

select * from ( select 'null' as ZIW_ROW_ID,cast(IBMSNAP_LOGMARKER as date) as ZIW_SOURCE_START_DATE,IBMSNAP_LOGMARKER as ZIW_SOURCE_START_TIMESTAMP, cast('2017-04-14 07:32:08' as timestamp) as ZIW_TARGET_START_TIMESTAMP, cast('2017-04-14 07:32:08' as date) as ZIW_TARGET_START_DATE,cast('9999-09-09' as date) as ZIW_SOURCE_END_DATE,cast('9999-09-09 09:09:09' as timestamp) as ZIW_SOURCE_END_TIMESTAMP,cast('9999-09-09' as date) as ZIW_TARGET_END_DATE,cast('9999-09-09 09:09:09' as timestamp) as ZIW_TARGET_END_TIMESTAMP,'true' as ZIW_ACTIVE,'false' as ZIW_IS_DELETED,IBMSNAP_OPERATION as ZIW_STATUS_FLAG,"ID" as "ID" ,"NAME" as "NAME" from ( select asn.IBMSNAP_LOGMARKER,cd.* from ASN.IBMSNAP_UOW asn join DB2INST2.CD_TEST_TABLE cd on asn.IBMSNAP_COMMITSEQ=cd.IBMSNAP_COMMITSEQ where asn.IBMSNAP_LOGMARKER>'2017-04-14 07:11:15.0' and asn.IBMSNAP_LOGMARKER<='2017-04-14 11:32:08' and cd.IBMSNAP_OPERATION in ('I','U','D'))ziw_cdc_temp1)ziw_cdc_temp2

Sample Output