To determine the change data latency of individual PowerCenter real-time sessions that have PowerExchange change data capture (CDC) sources, you can configure heartbeat tables on the source and target systems. Then include these tables in your PowerCenter CDC sessions to record time stamps that mark key points in the data flow from source to target. By analyzing these time stamps, you can identify and troubleshoot delays.

Include the source and target heartbeat tables within a mapping for a PowerCenter CDC session that you want to monitor. You can use the same source and target heartbeat tables for multiple CDC sessions.

The source heartbeat table contains a single column that is updated with the current system time stamp at a regular interval. You configure the update interval. The CDC session processes this update and adds other time stamps that mark key processing points. Then the rows with this time stamp information for the session are inserted into the target heartbeat table.

When configuring heartbeat table processing in PowerCenter, import the extraction map for the source heartbeat table so that the PowerExchange-generated DTL__CAPXTIMESTAMP column is available in the source definition. In the mapping for an existing CDC session, add the source and target definitions for the heartbeat tables, an Expression transformation, and an Update Strategy transformation. The Expression transformation adds columns for the session name and for the time stamp that indicates when the CDC session processed the source update. The Update Strategy transformation causes the data in all of the heartbeat columns to be passed to the target heartbeat table as inserts instead of updates. By using inserts, you maintain the target heartbeat table information for analysis across multiple updates and sessions.

The target heartbeat table contains a session-specific row for each source update that is captured. Each row contains a column for the session name and four different time stamp columns.You can compare the time stamp values to determine the data latency of a session and to troubleshoot any delays.