PM62476: QCAPTURE DISPLAYS ASN0060E BUFFER OVERLAID AND THE STOPS WITHOUT BACKING OUT UNCOMMITTED MQ MESSAGES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Qcapture displays ASN0060E buffer overlaid and the stops without
  backing out uncommitted MQ messages
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Q Apply                                   *
  *                 2- Q Capture. Q Apply                        *
  *                 3- Q Capture users adding columns to a Q     *
  *                 subscription via ADDCOL signal               *
  *                 4- Capture                                   *
  *                 5- Q Capture uses with a restart queue       *
  *                 defined as a QALIAS                          *
  *                 6- Q Apply when using DB2 V10                *
  *                 7- SQL replication                           *
  *                 8- SQL Capture, Q Capture                    *
  *                 9- Replication Center customers who use the  *
  *                 ASN.ADMINIF stored procedure to display a    *
  *                 list of Websphere MQ queues                  *
  *                 10- SQL Capture, Q Capture customers         *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- Q Apply does not log SQL statement   *
  *                      TEXT in IBMQREP_EXCEPTIONS table if     *
  *                      the cause of the exception is SQL       *
  *                      error while doing PREPARE of the SQL    *
  *                      statement for the row operation.        *
  *                      2- When source is DB2 V10 z/OS then     *
  *                      the ADD COLUMN message for TIMESTAMP    *
  *                      cannot be processed by Q Apply for LUW  *
  *                      (V9.7 or V10.1) or Q Apply z/OS (V10).  *
  *                      There is no impact going from Q         *
  *                      Capture V9 to Q Apply V9.               *
  *                      3- Q Capture enforced a limit of 20     *
  *                      ADDCOL signals per commit interval.     *
  *                      4- Capture encounters error decoding    *
  *                      updates to a compressed table.          *
  *                      5- A check to inquire MQIA_MAX_Q_DEPTH  *
  *                      on the RESTARTQ returned mqrc 2068      *
  *                      6- Q Apply Admin thread on startup      *
  *                      does not commit after a query causing   *
  *                      DSNB1PCK warnings on DB2 V10 for long   *
  *                      running reader.                         *
  *                      7- SQL Apply unnecessarily checks       *
  *                      ibmsnap_restart table on source server  *
  *                      8-  Q Capture started specifying        *
  *                      TERM=N terminates after it log reader   *
  *                      receives DB2 reason code 00F30805.      *
  *                      9-  The ASN.ADMINIF stored procedure    *
  *                      does not display a list of Websphere    *
  *                      MQ queues if the queue manager          *
  *                      subsystem recognition character is      *
  *                      '('.                                    *
  *                      10-  Capture writes spill data to the   *
  *                      capture user home directory after it    *
  *                      displays ASN0999E Error condition       *
  *                      "9700".  Error condition "9700" means:  *
  *                      Severe storage management subsystem     *
  *                      (SMS) IDAX, ACS and Construct Access    *
  *                      Services error.                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- If a row operation fails due to SQL error while doing
  PREPARE of the SQL statement pertaining to row operation, Q
  Apply logs an exception in IBMQREP_EXCEPTIONS table and follows
  error_action , but does not include the SQL TEXT in the
  exceptions entry.
  2- ADD COLUMN message for TIMESTAMP type (when Q Capture is
  running DB2 V10) cannot be processed by different version of Q
  Apply other than V9 for z/OS.
  3- The limit of 20 columns is needed per Q subscription, not
  globally across all subscriptions.  In addition, the variable
  nature of a commit interval makes it hard for customers to
  deterministically ensure this limit is not reached.
  4- Capture was not decoding DB2 variation 3 log records
  properly when the source table was compressed and the row
  lengths were large.
  5- The MQINQ for MQIA_MAX_Q_DEPTH of the RESTARTQ was
  incorrectly done on the QALIAS as opposed to the BASEQ.
  6- DB2 V10 reports long running reader for Q Apply Admin
  thread. DSNB260I -HT1K DSNB1PCK WARNING - A READER HAS BEEN
  RUNNING FOR xxxx MINUTES
  7- Apply should not check ibmsnap_restart table for Capture
  status
  8-  Q Capture started specifying TERM=N terminated after the
  DB2 IRLM address space was cancelled.
  9-  The ASN.ADMINIF stored procedure parses the 'DISPLAY
  QUEUE(*)' command output incorrectly.
  10-  Capture continues to write spill data to the capture user
  home directory after it is unable to allocate a spill data set.
  

Problem conclusion

• 1- Q Apply is fixed to correctly log SQL TEXT in
  IBMQREP_EXCEPTIONS table even for failures while doing  PREPARE
  of the SQL statement pertaining to row operation.
  2- The problem has been fixed. Both programs have been fixed to
  generate or process the ADD COLUMN message for TIMESTAMP that
  works for all versions. Both programs must be upgraded to this
  (or later) level (for V9) if one of the programs is upgraded
  and the source is DB2 V10 and TIMESTAMP columns are added.
  3- The new limit is 20 added columns ADDCOL signals per Q
  Subscription per database transaction. Any columns that are not
  added to the subscription because the 20 column limit is hit
  for a Q subscription (ASN7099) can be re-added by reissuing the
  failed ADDCOL signal(s).
  4- Capture will properly handle DB2 variation 3 log records.
  5- The problem no longer occurs.
  6- Problem has been fixed. Q Apply Admin thread will commit
  appropriately avoiding long running reader warnings.
  7- remove the checking of ibmsnap_restart table from sql apply
  8-  Q Capture started specifying TERM=N will wait for DB2 to
  start after its log reader receives DB2 reason codes 00F30011,
  00F30018 or 00F30805.
  9-  Modify the ASN.ADMINIF stored procedure to display a list
  of Websphere MQ queues when the queue manager subsystem
  recognition character is '('.
  10-  Capture will stop after it is unable to allocate a spill
  data set.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK79510 UK79511 UK79512 UK79513

Modules/Macros

•    ASNACMD  ASNADMSP ASNAPPLY ASNAPRS  ASNCAP
  ASNCCMD  ASNDB2SQ ASNMCMD  ASNMIG8  ASNMON   ASNPLXFY ASNQACMD
  ASNQAHKT ASNQAPAG ASNQAPP  ASNQAROW ASNQASUB ASNQCAP  ASNQCCMD
  ASNQDEP  ASNQMFMT ASNQXFMT ASNRBASE ASNREG   ASNTDIFF ASNTRC
  ASN2BASE ASN2DB2Q
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• R910 PSY UK79510

     UP12/06/21 P F206

• R911 PSY UK79511

     UP12/06/21 P F206

• R912 PSY UK79512

     UP12/06/21 P F206

• R913 PSY UK79513

     UP12/06/21 P F206

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.