Coordinating a source table change with the Capture program

You can use a Capture CMD type STOP subtype signal to shut down a Capture program and to coordinate source table changes.

1. Create a Capture CMD type STOP subtype signal by inserting a row into the IBMSNAP_SIGNAL table with the following SQL statement:

   INSERT INTO Schema.IBMSNAP_SIGNAL  
               (signal_type, 
                signal_subtype,  
                signal_state)  
          VALUES('CMD',
                 'STOP',
                 'P');

   You should insert this row when the database application event occurs, after the source table activity has been quiesced but prior to the activity that causes problematic log record changes.

   The Capture program acts on this signal table log record after the Capture program finds this record on the database recovery log and only when the Capture program finds the corresponding commit record for this insert, verifying that this event was committed.

   The Capture program shuts down all Capture threads in an orderly manner after committing all captured data from the transactions on the log that are prior to the commit log record for the DB2® unit of work that contains this inserted IBMSNAP_SIGNAL row. Before terminating, the Capture program also updates the following values in the IBMSNAP_SIGNAL table row that corresponds to the insert log record being processed:

   • SIGNAL_STATE = 'R' (received by the Capture program)
   • SIGNAL_LSN = the log sequence number from the commit log record for the DB2 unit of work that contains this signal row insert

   All log records for the changing source table are processed by the Capture program when it terminates.

2. Depending on your scenario, drop and recreate your source table, or reorganize and compress your source table without setting the KEEPDICTIONARY option to YES.
3. If you dropped or altered replicated columns, you should now alter the corresponding registrations and subscription sets that you created for this source table. Such changes, if necessary, can be further coordinated with the Apply program by waiting for the affected subscription sets to catch up to the currently stopped Capture program. A subscription set is in synch with the Capture program when the SYNCHPOINT column value in the IBMSNAP_SUBS_SET table is equal to the MAX_COMMITSEQ column value in the Schema.IBMSNAP_RESTART table.