Putting Q subscriptions into a temporary spill mode for maintenance

You can place Q subscriptions into a temporary spill mode to perform maintenance on target tables without stopping the capture of changes at the source or affecting replication to other tables.

About this task

About this task

When you place a Q subscription in spill mode, changes are not applied to the target table but instead are spilled to a dynamically created spill queue. Changes continue to be captured and applied for other Q subscriptions. When maintenance is complete, you can send a resume command to the Q Apply program

The temporary spill queue is created based on the definition of the model queue that is used for load operations. If you use the Replication Center to create or change the Q subscription and specify that target tables should not be loaded, the Model queue field on the load page is not enabled. In this case you must select Direct data to a spill queue until the problem is resolved on the errors page to enable the Model queue field on the load page.

Ensure that the maximum depth of the model queue is large enough to hold the spilled rows until the rows can be applied after resuming operations.

If you choose to spill replicated data from one table, be aware that any transactions that involve that table and other tables can become inconsistent in spill mode. For example, assume that you wanted to perform maintenance on target table T1, and issued a spillsub command. Consider a source transaction that updates tables T1 and T2:

UPDATE T1;
UPDATE T2;
COMMIT;

The UPDATE T1 operation is spilled, but the UPDATE T2 operation is applied to the target table, and a database commit and IBM® MQ commit are issued. If you look at the target tables, only the update to T2 exists. So, a partial transaction was applied and committed to the database, and transactional consistency was not respected. If you expect transactional consistency at all times, you should not use the spillsub feature.

Recommendation: Do not make DDL changes to a source table when the Q subscription for the table is spilling. When DDL changes are made, the Q Capture program sends a schema messages for the DDL changes, and the Q Apply program resumes the Q subscription to process the schema message and puts the Q subscription into active state even though the spilling process is not complete.

Spill mode is not allowed if the Q subscription involves a table that has referential integrity dependencies. You must drop and restore RI constraints on the target table. Use the same receive queue for all Q subscriptions that involve RI-related tables. If you need to perform maintenance, stop message processing on the receive queue instead of spilling an individual Q subscription.

Procedure

Procedure

  1. Place the Q subscription into spill mode by using the spillsub parameter with the MODIFY command or asnqacmd command: 
    F Q_Apply_job_name,spillsub="receive_queue_name:Q_subscription_name"
  2. To resume normal operations, use the resumesub parameter: 
    F Q_Apply_job_name,resumesub="receive_queue_name:Q_subscription_name"

Results

When all rows in the spill queue are applied, the Q Apply program places the Q subscription back in active (A) state.