Heartbeat Application for IICS and CDI Change Data Capture

Challenge

In today’s world, business decisions are dependent on the availability of quality data from a variety of applications and data sources. Data latency is just one aspect of data quality. Data latency becomes especially important in an environment where changes in the data records are captured and replicated to other data sources and applications. In this context, a simple definition of data latency is the amount of time between when the change in the data record was committed and when the changed data record was replicated to the target.

Description

To minimize data latency, organizations will implement Change Data Capture (CDC) in a near real-time fashion and use service level agreements (SLAs) to formally set their expectations related to overall data latency. Determining data latency can be difficult to accomplish because the frequency of data changes within a given table or set of tables is not predictable. When both the source and target are relational, a Heartbeat application can assist in determining data latency.

From a high-level, a Heartbeat application is a rather simple application which involves the following:

1. An IICS or CDI mapping or other process will update a timestamp column in a source Heartbeat table record at a predetermined time interval. For example, every 15 minutes. Once the change is committed, the CDC process will capture the data change record.
2. The existing IICS or CDI CDC mappings that consume the CDC data records from the same source database will also contain a Heartbeat CDC pipeline to consume the CDC records from the source Heartbeat table. In the Heartbeat CDC pipeline, all of the CDC records will be inserted into the target table after populating some additional columns. These columns will be used to determine overall data latency from the source to the target table.

The target Heartbeat table is a historical table so a set of queries or reports can be generated to report the source to target Heartbeat data latency over a specified timeframe. Since the Heartbeat application uses the same source database, IICS or CDI CDC mappings, and target database as the other CDC tables, it’s data latency will be very similar to the data latency experienced by the other CDC tables. In addition, this data latency information can be used to assist in determining realistic SLAs and alerting the operations staff when the CDC process is not performing as expected. An alert example might be notifying the operations staff when the target Heartbeat table has not processed any new CDC records in the past 45 - 60 minutes.

To implement a simple Heartbeat application the following activities or tasks need to be completed.

• Each of the source databases using CDC should have a Heartbeat table that will be used to help determine data latency. Note that the primary key for this table is a composite primary key. This source Heartbeat table will only contain a single record that will be UPDATED at a predefined time interval, for example every 15 minutes, by another process.

Description of Source Heartbeat Table Columns

Sample Source Heartbeat Table DDL

CREATE TABLE schema.SRC_HEARTBEAT (
     REC_TYPE VARCHAR2 (9) NOT NULL,
     SRC_DBNAME VARCHAR2 (10) NOT NULL,
     LINE_OF_BUSINESS VARCHAR2 (10) NOT NULL,
     SRC_TIMESTAMP TIMESTAMP NOT NULL,
     SRC_DBTIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL);
COMMIT;
CREATE INDEX SRC_HEARTBEAT_INDEX
     ON schema.SRC_HEARTBEAT (REC_TYPE, SRC_DBNAME, LINE_OF_BUSINESS,
             SRC_TIMESTAMP);
COMMIT;

• A target Heartbeat table needs to be created in each of the CDC target databases being used. Note that the primary key for this table is a composite primary key. All of the source Heartbeat change records captured by the CDC process will be INSERTED into this target Heartbeat table. The records in this table will be used to determine the amount of data latency between the source and target databases in addition to other points in the process.

Description of Target Heartbeat Table Columns

Sample Target Table DDL

CREATE TABLE schema.TGT_HEARTBEAT (
     REC_TYPE VARCHAR2 (9) NOT NULL,
     SRC_DBNAME VARCHAR2 (10) NOT NULL,
     LINE_OF_BUSINESS VARCHAR2 (10) NOT NULL,
     SRC_TIMESTAMP TIMESTAMP NOT NULL,
     SRC_DBTIMESTAMP TIMESTAMP NOT NULL,
     CAPX_TIMESTAMP TIMESTAMP NOT NULL,
     CDC_MAP_NAME VARCHAR2 (20) NOT NULL,
     CDC_MAP_TYPE VARCHAR2 (3) NOT NULL,
     CDC_MAP_TIMESTAMP TIMESTAMP NOT NULL,
     TGT_DBTIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT TGT_HEARTBEAT_PK
     PRIMARY KEY (REC_TYPE, SRC_DBNAME, LINE_OF_BUSINESS , SRC_TIMESTAMP,
          CAPX_TIMESTAMP, CDC_MAP_NAME));
COMMIT;

• A CDC Change Registration will need to be created for the source Heartbeat table. A CDC Change Registration identifies that all data changes for this table should be captured.
• A process will need to be created that will update the single record in the source Heartbeat table at a predefined time interval. For example, every 15 minutes. This ensures that at least one data change record will be flowing through the IICS or CDI CDC mappings every 15 minutes.

Below are column values that this process will need to set.

• A Heartbeat CDC pipeline will need to be added into each of the IICS or CDI CDC mappings that will INSERT the Heartbeat CDC records into the target database. It is important to remember to change the target table Operation property so that all of the CDC records are treated as INSERTs.
  

Below are column values that this process will need to set.

• To facilitate the reporting on the Heartbeat CDC target table, a view should be created that includes the calculation of data latency between the various timestamp columns.

 Description of the Target Heartbeat Reporting View Columns

Sample Target Table View DDL for Reporting

CREATE VIEW TGT_HEARTBEAT_VIEW (
     REC_TYPE,
     SRC_DBNAME,
     LINE_OF_BUSINESS,
     SRC_TIMESTAMP,
     SRC_DBTIMESTAMP,
     CAPX_TIMESTAMP,
     CDC_MAP_NAME,
     CDC_MAP_TYPE,
     CDC_MAP_TIMESTAMP,
     TGT_DBTIMESTAMP,
     CAPX_TIMESTAMP - SRC_TIMESTAMP,
     MAP_TIMESTAMP - CAPX_TIMESTAMP,
     TGT_DBTIMESTAMP - MAP_TIMESTAMP,
     TGT_DBTIMESTAMP - SRC_TIMESTAMP)
AS SELECT DATA_SOURCE,
     REC_TYPE,
     SRC_DBNAME,
     LINE_OF_BUSINESS,
     SRC_TIMESTAMP,
     SRC_DBTIMESTAMP,
     CAPX_TIMESTAMP,
     CDC_MAP_NAME,
     CDC_MAP_TYPE,
     CDC_MAP_TIMESTAMP,
     TGT_TIMESTAMP,
     LATENCY_ BTW_CAPX_AND_SRC,
     LATENCY_ BTW_MAP_AND_CAPX,
     LATENCY_ BTW_TGT_AND_MAP,
     LATENCY_ BTN_TGT_AND_SRC
FROM schema.TGT_HEARTBEAT
     ORDERED BY (REC_TYPE, SRC_DBNAME, LINE_OF_BUSINESS, SRC_TIMESTAMP);
COMMIT; 

• A set of queries or reports will need to be created showing the data latency detail records or averages over a specified time period.
• An IICS or CDI mapping/taskflow or another process will need to be created that will purge obsolete records from the target Heartbeat tables.