How to Manually Activate a Q Replication Subscription on the Target

Posted by Frank Fillmore on March 23, 2011 under InfoSphere, Q-Replication. Tags: .

It’s rare I begin a blog post which will explain how to do something with “DON’T DO IT!”, but I’ll make an exception this time.  Read what follows, but don’t do it unless extreme circumstances warrant.

A colleague, Jim Herrmann, and I have been working on a Q Replication implementation at a large financial institution.  Through a procedural error, we cold started a Q Replication environment, then deleted the schema messages from the WebSphere MQ receive queue that Q Replication had sent from source to target to activate subscriptions.  Ooops…  We had begun a performance test so there were almost 700k messages added to the receive queue.  When we started Q Apply, it complained about missing messages (the schema messages we had deleted).  We could have deleted all of the messages and executed another cold start, but that would have required that we re-run the performance test.  Due to scheduling constraints, rerunning the test wasn’t an option.  We could have activated the subscriptions by re-issuing the CAPSTART command into the IBMQREP_SIGNAL table, but Q Apply wouldn’t see those messages until after the 700k messages were processed.  A classic chicken-and-egg problem.  What to do?

With Q Capture and Q Apply down we manually updated the IBMQREP_TARGETS table.  First, we UPDATEd the STATE column to ‘A’ for each subscription.  Then, for each subscription we matched the SUB_ID in the target to the source.  For example, for SUBNAME MY_TAB_0001, we updated the SUB_ID in IBMQREP_TARGETS to the value found in IBMQREP_SUBS.

Next we had to modify the IBMQREP_TRG_COLS table.  This update relied on the fact that we had a QA environment that almost matched the performance environment.  Jim drafted some nifty SQL to create the necessary DML code.

SELECT ‘UPDATE ASN.IBMQREP_TRG_COLS SET MSG_COL_TYPE=’|| MSG_COL_TYPE ||’, MSG_COL_LENGTH=’|| MSG_COL_LENGTH ||’, SRC_COL_MAP =”’|| SRC_COL_MAP ||”’ where RECVQ=”’|| RECVQ ||”’ AND SUBNAME=”’|| SUBNAME||”’ AND TARGET_COLNAME=”’|| TARGET_COLNAME||”’;’ FROM ASN.IBMQREP_TRG_COLS;

Then we ran the resulting UPDATE statements against the target Q Replication Control Table database.

Finally, Q Apply relies on WebSphere MQ for assured delivery of messages – including the schema messages we had inadvertently obliterated.  The following message was posted in both the Q Replication log and the IBMQREP_APPLYTRACE table.

ASN7551E  “Q Apply” : “ASN” : “BR00000” : The Q Apply program detected a gap in message numbers on receive queue “SERVER1.CUSTDB.RECEIVE.DATAQ” (replication queue map “DB2S_TO_DB2T”).  It read message ID “515245504D875D2C00000000000000000000000000000019”, but expected to find message ID “515245504D875D2C00000000000000000000000000000001”.  The Q Apply program cannot process any messages until it finds the expected message.

We had to tell Q Apply to ignore the missing messages.  To do this, we added a message to the IBMQREP_DONEMSG table.

INSERT INTO ASN.IBMQREP_DONEMSG (RECVQ, MQMSGID) VALUES(‘SERVER1.CUSTDB.RECEIVE.DATAQ’, x’515245504D875D2C00000000000000000000000000000018′)

Then we started Q Apply.

All of the usual caveats are relevant:

  • Don’t try this at home.
  • Professional driver, closed course.
  • Your mileage may vary.

This was a work-around in a tight spot.  Not business as usual.

4 Comments so far

  1. Jim Herrmann March 23, 2011 3:08 pm

    An important note is that the IBMQREP_DONEMSG row needed to be one less than the Apply program was looking for. i.e. it was looking for “19” and we posted “18”. This is what you have, but just thought I would add that bit for clarity.

    Also the updates to the IBMQREP_TARGET table can be generated from the IBMQREP_SUBS table with this query:

    Select ‘update ASN.IBMQREP_TARGETS set sub_id=’|| SUB_ID ||’ where SUBNAME = ‘|| SUBNAME
    from ASN.IBMQREP_SUBS

    That beats trying to match them up manually. 🙂

  2. Jim Herrmann March 23, 2011 3:21 pm

    Forgot to add a semicolon to the generate update statement:

    Select ‘update ASN.IBMQREP_TARGETS set sub_id=’|| SUB_ID ||’ where SUBNAME = ‘|| SUBNAME ||’;’
    from ASN.IBMQREP_SUBS

  3. Lynne April 7, 2011 3:15 pm

    Thanks! This is now book-marked but will only be used after much consultation. Neat work-around…

  4. Jim Herrmann March 20, 2014 1:09 pm

    If you have to insert a done message into an Oracle table, such as you might do with Oracle Native Apply, the syntax is a bit different. Because the IBMQREP_DONEMSG table MQMSGID is a RAW column, you just specify the value without the “x” in front of the string. This was a head scratcher, so I thought I would post that little update here for future reference, since I ran into a similar problem at a different client, and found the solution here. What goes around, comes around.

    INSERT INTO QREP_APPLY.IBMQREP_DONEMSG (RECVQ, MQMSGID) VALUES(‘SERVER1.CUSTDB.RECEIVE.DATAQ’, ‘51524550532222E4000000000000000000000000000AF186’);

Leave a Comment

Name

Email

Website

Comments

More