Load options for temporal tables

When the replication target is a system-period temporal table, you need to be aware of potential issues with the append type of load and you need to take extra steps if you choose to load the target table yourself.

All of the Q Replication load options are available except for those that use the IMPORT utility; DB2 does not support IMPORT with system-period temporal tables or bitemporal tables. Note the following considerations however:

Append-type loads

When loading a system-period temporal table that already contains data, you must consider potential data conflicts if you use a load type that appends new data to the table rather than replacing all data in the table. The conflicts can occur because of duplicate values in the three timestamp columns that are part of system-period temporal tables. If you choose to use an append-type load (LOAD_TYPE 101, 102, 103, 104, or 105 in the IBMQREP_TARGETS table), conflicting rows might be rejected by DB2® during the loading process, and you might need to take appropriate action.

Note for z/OS: On z/OS®, if you choose to perform a load outside of replication (a manual or external load), an append-type load is the only option because DB2 does not support truncate or load append options for system-period temporal tables even with the stored procedure that is part of APAR PM61811.

Loading the target outside of replication

If you specify a manual (also known as external) load for a system-period temporal table, you need to take extra steps to ensure a consistent loading process. These steps differ depending on whether the target database is on z/OS or Linux, UNIX, and Windows. If you choose to let the Q Apply program load the target table, it performs these same steps.

z/OS

Follow these steps:

1. Delete all the rows from the system-period temporal table and the history table.
2. Call the DB2 stored procedure SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY to stop recording changes in the history table at the target. This stored procedure is built into APAR PM31315.
3. Use a load utility that appends rows from the source to the temporal table and history table (because no rows exist after Step 1, you are "appending" to an empty table, but loads that use a replace option are not supported for system-period temporal tables, even with APAR PM61811). You must use the options PERIODOVERRIDE and TRANSIDOVERRIDE with the load command.
4. Notify the Q Apply program (as with all external loads) that the target is loaded so that replication can begin.

Linux, UNIX, Windows

Follow these steps:

1. Call the DB2 stored procedure SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY to stop recording changes in the history table and to enable replacement of records in the system-period temporal table at the target. This stored procedure is built into DB2 for Linux, UNIX, and Windows Version 10.1 Fix Pack 2.
2. Use a load utility that replaces all existing data in the target temporal table and history table with data from the source. You must use the options PERIODOVERRIDE and TRANSIDOVERRIDE with the load command.
3. Notify the Q Apply program that the target is loaded so that replication can begin.