A Workaround for LOADDONE in Q Replication

Posted by Frank Fillmore on May 25, 2011 under InfoSphere, Q-Replication. Tags: , , .

Here is another technique to use only when standard practices won’t work.  Let’s say you are performing a manual load in Q Replication (either InfoSphere Replication Server or DB2 Homogeneous Replication Feature).  Ordinarily you would INSERT a LOADDONE into the IBMQREP_SIGNAL table.  The INSERT into the Q Replication Control Table is logged and Q Capture will see it.

BUT, what if Q Capture is way behind?  Various operational problems could cause this.  The manual load has actually completed, but the WebSphere MQ spill queues are growing because Q Apply doesn’t know that.  It might take hours for Q Capture to get to the point in the log where the LOADDONE is posted.

Here are the steps to bypass LOADDONE and initiate the draining of the spill queues by Q Apply:

  1. Stop Q Capture.
  2. Wait for the Receive queue to empty.  The reason for this is that Q Capture sets an indicator in the message placed on the Send queue specifying that a subscription is still being manually loaded.
  3. Stop Q Apply.
  4. For each subscription where the LOAD has completed: change the STATE value in the IBMQREP_TARGETS table from ‘E’ to ‘F’ and change the STATE value in the IBMQREP_SUBS table from ‘L’ to ‘A’.
  5. Start Q Apply.
  6. Start Q Capture.

Thanks to Christian Zentgraf and Anupama Mahajan of IBM and my TFG colleague, Jim Herrmann, for developing this procedure.

1 Comment so far

  1. Evan December 5, 2020 5:05 am

    I want to start Q capture from a known point in db2 log and I follow this link: https://www.ibm.com/support/knowledgecenter/SSTRGZ_11.4.0/com.ibm.swg.im.iis.repl.qrepl.doc/topics/iiyrqcaptrestart.html

    I find the value of maxcmtseq from the log of the Q capture at the time it is stopped and set the value of LSN using db2flsn and the log file that I want to start from, but it does not work for me and it reloads the target table with the latest data from the source.

    Is there any suggestion?

    I have another question, if it is the first time that I am starting the Q capture and I want to start it from a time before the current Db2 end of log, how should I set the value of maxcmtseq? our db2 version is 10.5 on linux, and mq version is 8

Leave a Comment