Loading target tables in a data consolidation scenario

When multiple source tables are replicated to a single target table in Q Replication, you use a special procedure to ensure that all of the data from multiple loads is preserved in the target table.

About this task

This procedure allows you to incrementally load the target from multiple sources and ensure that none of the loads overwrite data from previous loads.

When you create Q subscriptions for the source tables, you specify a load type that uses the REPLACE option for one Q subscription, and a load type that uses the RESUME option (z/OS) or INSERT option (Linux, UNIX, and Windows) for all of the other Q subscriptions. Then you start the Q subscription with the REPLACE load type first, and start the other Q subscriptions after the first load is finished.

The first load replaces any data in the target table, and the subsequent loads add data from the other source tables without affecting the data from the first load.

Table 1 shows the load types that you should use in combination for this procedure. The load type information is stored in the IBMQREP_TARGETS table.

Table 1. Load type combinations for data consolidation

Load type for first load (with REPLACE option)	Load type for subsequent loads (with RESUME or INSERT option)
1 (LOAD from CURSOR)	101
2 (EXPORT/IMPORT)	102
3 (EXPORT/LOAD)	103

Figure 1 shows how the procedure works for one of these combinations, LOAD from CURSOR with the REPLACE option for the first load and LOAD from CURSOR with the RESUME option for the subsequent loads.

Figure 1. Example of load procedure for a data consolidation scenario that uses LOAD from CURSOR

Procedure

1. When you create Q subscriptions for the consolidated target table, set LOAD_TYPE to 1, 2, or 3 for one Q subscription in the group and LOAD_TYPE to 101, 102, or 103 for all remaining Q subscriptions in the group, following the combinations shown in Table 1.
   Note: Specify that the Q subscriptions with LOAD_TYPE of 101, 102, or 103 are created in inactive (I) state. You can do this in the ASNCLP command-line program by using the START AUTOMATICALLY NO keywords. In the Replication Center, on the Loading the Target Tables page of the Create Q Subscriptions wizard, clear the Start all Q subscriptions automatically checkbox. The Q subscription with LOAD_TYPE of 1, 2, or 3 starts automatically.

   Also, do not set the conflict action and error action for the Q subscriptions to D (stop the Q subscription).

2. After loading completes for the first Q subscription, start the remaining Q subscriptions.

   The Q Apply program sets the state of the Q subscription to A (active) when a load completes. You can use the Q Replication Dashboard or the Manage Q Subscriptions window in the Replication Center to verify the Q subscription state.