SYSTOOLS.REPL_MQT_LATENCY table

IBM® InfoSphere® Change Data Capture communicates replication latency information to the Db2® server through the SYSTOOLS.REPL_MQT_LATENCY table.

The SYSTOOLS.REPL_MQT_LATENCY table is row-organized. It contains one row with the following columns:

COMMIT_POINT: This BIGINT column is a time stamp (in seconds since the epoch, UTC) that represents the last time a commit was issued after applying changes to target shadow tables. It is the time at which the DELAY_OFFSET value is generated.
DELAY_OFFSET: This BIGINT column is the number of seconds between the time at which the source table data is read and the last time that applied changes to target shadow tables were committed.

Note: To use the SYSTOOLS.REPL_MQT_LATENCY table, make sure that the maintain_replication_mqt_latency_table system parameter is activated within the CDC Replication Engine for Db2® for LUW. For more information refer to maintain_replication_mqt_latency_table.

The maintain_replication_mqt_latency_table system parameter can be set using the Management Console GUI, or through the command line. To use the Management Console, refer to Setting system parameters on source and target datastores. To use the command line, refer to dmset - Set CDC Replication system parameter

The refresh time stamp is the value of COMMIT_POINT minus the value of DELAY_OFFSET. After updates to the SYSTOOLS.REPL_MQT_LATENCY table are committed, the Db2 server expects that the committed transactions on the source table before the refresh time stamp were applied and committed in the shadow tables. During query processing, this refresh time stamp is compared to the system time stamp (UTC) and the value of the CURRENT REFRESH AGE special register to determine whether the query should be routed to the shadow tables. Issue the following query to show the last refresh timestamp:

SELECT
( timestamp('1970-01-01')
+ (COMMIT_POINT - DELAY_OFFSET) seconds
+ CURRENT TIMEZONE
) as LAST_REFRESH_TIMESTAMP
FROM
SYSTOOLS.REPL_MQT_LATENCY;

The following text is a sample of the output return by the previous query:

LAST_REFRESH_TIMESTAMP
--------------------------
2014-07-21-11.13.59.000000

1 record(s) selected.

To create this table, call the SYSPROC.SYSINSTALLOBJECTS procedure. For more information, see Creating the SYSTOOLS.REPL_MQT_LATENCY table.