Replicating temporal tables

The CDC Replication Engine for Db2® Database supports replication of the three types of temporal tables: application-period temporal tables, system-period temporal tables and bitemporal tables.

There are no special considerations required when mapping the columns associated with an application-period. When capturing changes from an application-period table CDC Replication will capture the row level changes done by DB2®. If the application makes a change to the source table that require DB2 to add or change rows so as to ensure there are no overlaps in application-periods CDC Replication will capture all the changes done to the source table.

When replicating to a system-period table (which includes bitemporal tables) you can choose whether the table should maintain a history of what occurred on the source system or the target system. If you choose to have the table contain the history of what occurred on the target system then it will behave the same as any other system-period table which is not the target of replication. You can perform temporal queries on that table to determine what it contained at any point in the past. For example, you could answer the question: If I had run a query against my local (target) database at 1 P.M. what would the answer have been?

If you choose to have the table contain the history of what occurred on the source system then it will let you answer a different sort of question: If I had telephoned my colleague at 1 P.M. and had them run a query against the source database, what would the answer have been?

It is important to note that when the table contains the history of what occurred on the source system then any latency that exists in replication can affect what questions can usefully be asked. For example, if you have chosen not to run your subscription for the last 4 hours then the target table will not yet contain the history of what occurred on the source during that time and thus any queries about the state of the table in the last 4 hours will return invalid results.

Here is an example to illustrate the events that will occur:

  1. The subscription is stopped at 1 P.M.
  2. On the source system a new customer row is added at 1:05 P.M.
  3. On the target system a query is done to check if that new customer existed at 1:06 P.M. The target will indicate that it did not exist.
  4. The subscription is restarted at 1:30 P.M. It quickly catches up.
  5. On the target system a query is again done to check if that new customer existed at 1:06 P.M. Now the target indicates that it did exist.

You can only have the target table contain the source history when the source table is also a system-period table. In this situation the system-period columns are mapped automatically and that mapping cannot be changed.

When mapping to a target system-period table where it will contain the target history the system-period columns will be maintained by the database and aren't available for mapping in CDC Replication.

The history tables associated with a system-period table cannot be individually mapped. Any mapping involving the base table will affect the data in the history table as required.