Manually loading a target table

When you specify a manual load for a Q subscription, you load the target table using a utility of your choice, and then notify the replication programs when the table is loaded.

Before you begin

Before you begin

Ensure the Q subscription was created with the manual load option (HAS_LOADPHASE value of E in the IBMQREP_SUBS table) and that the Q subscription is inactive (STATE value of I or N in IBMQREP_SUBS).
Ensure that no applications are updating the target table that you plan to load. Data in the tables will be inconsistent while the replication programs synchronize the source and target tables after the loading process. The Q Apply program drops referential integrity constraints until the target table and any other related target tables are loaded.

About this task

About this task

While the target table is being loaded, the Q Capture program continues to send transactions from the source table. The Q Apply program puts these transactions in a temporary spill queue, and applies them after the load is complete. The Q Apply program waits until any dependent Q subscriptions have completed their load phase before putting referential integrity constraints back on the target table.

Recommendation: Load target tables during a time of relative inactivity at the source.

Procedure

Procedure

Start the Q subscription.
The Q Capture program starts capturing changes from the source table.
Quiesce activity at the source table.
This step ensures that the buffers are externalized before data is fetched from the source table.
Optional: On Db2® for z/OS®, you can use the UNLOAD utility with the SHRLEVEL(CHANGE) ISOLATION CS option to ensure that no uncommitted data is moved into the load file.
Verify that the Q Apply program is waiting for the target table to be loaded.
You can issue a SELECT statement for the IBMQREP_TARGETS table at the Q Apply server and verify that the STATE column value for the Q subscription is E.
Load the target table with your chosen utility.

Notify the Q Capture program when the load is complete. Use one of the following methods:

Method Description

Method	Description
Q Apply command table (IBMQREP_APPLYCMD)	Insert the loaddonesub command to prompt the Q Apply program to immediately begin applying source changes from the spill queue and to put the loaddone command on the administration queue to notify the Q Capture program that the load is completed: `INSERT INTO !ASH.IBMQREP_APPLYCMD (CMD_TEXT) VALUES ('LOADDONESUB=QREP.DATAQ:T20001');` Where QREP.DATAQ is the receive queue name and T20001 is the Q subscription name.
SQL	Insert a LOADDONE signal into the IBMQREP_SIGNAL table at the Q Capture server, as follows: `insert into schema.IBMQREP_SIGNAL( SIGNAL_TIME, SIGNAL_TYPE, SIGNAL_SUBTYPE, SIGNAL_INPUT_IN, SIGNAL_STATE ) values ( CURRENT TIMESTAMP, 'CMD', 'LOADDONE', 'subname', 'P');` Where `schema` identifies the Q Capture program that you want to signal, and `subname` is the name of the Q subscription for which you are performing a manual load.

Q Apply command table
(IBMQREP_APPLYCMD)

Insert the loaddonesub command to prompt the Q Apply program to immediately begin applying source changes from the spill queue and to put the loaddone command on the administration queue to notify the Q Capture program that the load is completed:

INSERT INTO !ASH.IBMQREP_APPLYCMD (CMD_TEXT) VALUES
('LOADDONESUB=QREP.DATAQ:T20001');

Where QREP.DATAQ is the receive queue name and T20001 is the Q subscription name.

SQL

Insert a LOADDONE signal into the IBMQREP_SIGNAL table at the Q Capture server, as follows:

insert into schema.IBMQREP_SIGNAL(
    SIGNAL_TIME,
    SIGNAL_TYPE,
    SIGNAL_SUBTYPE,
    SIGNAL_INPUT_IN,
    SIGNAL_STATE
) values (
     CURRENT TIMESTAMP,
    'CMD',
    'LOADDONE',
    'subname',
    'P');

Where schema identifies the Q Capture program that you want to signal, and subname is the name of the Q subscription for which you are performing a manual load.

Notes:

While it applies changes from the spill queue, Q Apply uses the conflict action of force (F in the CONFLICT_ACTION column of the IBMQREP_TARGETS table). It reworks duplicates and ignores not found errors. No data is written to the IBMQREP_EXCEPTIONS table. Q Replication reworks any updates that happened since the start of the Q subscription and puts the objects in synch. When spill queue processing is done, Q Apply begins processing from the receive queue.
To prevent full queues during the spilling process, ensure that the MAXMSGL and MaxQDepth settings for the send queue, receive queue, and model queue that is used for spill queues are large enough to accommodate the spilling.
You might need mechanism in place to keep the channels alive.

Verify that applications can safely use the target table again by checking to see if the Q Apply program makes the following changes to the IBMQREP_TARGETS table:
- Sets the STATE column to A (active).
- Sets the STATE_INFO column to ASN7606I, which means that any referential integrity constraints on the target table are restored.