Q Replication Automatic Load and the Things That Can Break

Posted by Frank Fillmore on January 22, 2009 under DB2 for z/OS, Q-Replication. Tags: , , .

Choosing a particular parameter value when creating a Q Replication subscription can sometimes set off a complicated chain reaction.  An example is the “Automatic Load” option.  When you create a new Q Replication subscription, tables in the source DB2 environment frequently are already populated with data.  One way to get the existing table data from the source to the target is to use the Automatic Load feature.

In this particular case study (from a real customer environment), we are replicating from one DB2 for z/OS subsystem to another using unidirectional replication.  The source DB2 subsystem is DB2S and the target is DB2T and they are on separate System z servers.

When Q Capture is started with a new subscription defined as having an Automatic Load or when a CAPSTART command is issued to the SIGNAL table for such a subscription, two things happen:

1. Q Apply on DB2T invokes the DSNUTILS DB2 Stored Procedure on DB2T running in Work Load Manager (WLM).

2. DSNUTILS initiates a DB2 cursor based load using three-part-names from DB2T acting as the Application Requestor (AR) across a Distributed Relational Database Architecture (DRDA) connection to the source tables on DB2S acting as the Application Server (AS).

If everything is set up correctly, existing table data from the DB2S is loaded into the corresponding table in DB2T.  Unfortunately, things are not always set up correctly.  All of components referenced here and their installation/configuration are well documented elsewhere, so I won’t repeat all of that here.  What I want to provide is a simple checklist and point out the things that might be preventing an Automatic Load from working.

1. DSNUTILS is not set up or not set up correctly on DB2T. (http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/wareh/t0005992.htm)

2. In order to run DSNUTILS in DB2T, the WLM NUMTCB parameter must be set. (http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/admin/cqrld003.htm)

3. The DRDA communications database (CDB) in DB2 for z/OS is not configured correctly.  Check out the examples on pages 98 and 99 in “WebSphere Information Integrator Q Replication: Fast Track Implementation Scenarios” (SG24-6487).  This manual can be found at http://www.redbooks.ibm.com

4. The userid that DSNUTILS passes to DB2S doesn’t have the authority to SELECT from the source table (SQLCODE -551).  When a Q Replication Automatic Load is attempted, a z/OS dataset log file will be generated.  Look in the log file for errors and correct them.  Depending on how you configured Q Replication on System z, the Q Apply log itself might be a z/OS dataset or a Unix Systems Services (USS) file.

5. The userid passed to DB2T will need to have sufficient authority to INSERT rows into the target tables by virtue of the cursor based load.  Look in the Automatic Load and/or Q Apply logs.

6. If there is Data Manipulation Language (DML) INSERT/UPDATE/DELETE activity on DB2S source tables, Q Apply will spawn spill queues while waiting for the Automatic Load to complete.  Look for z/OS datasets with high level qualifiers of IBMQREP.SPILL.MODELQ.

1 Comment so far

  1. emaitraxbix February 9, 2009 9:54 pm

    Hi, cool site, good writing 😉

Leave a Comment