PH55478: Q Capture should reuse fetched LOB/XML values for a table with multiple subscriptions

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• QCapture performance for table with XML data types is
  unacceptable when replicated to multiple targets
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Any replication user with MQ issues that  *
  *                 may cause Q Capture to fail a batch MQPUT    *
  *                 or MQCMIT                                    *
  *                 2- All QCapture users                        *
  *                 3- All Q Capture and SQL capture users       *
  *                 4- asnmon customer                           *
  *                 5- All user for Q capture and SQL capture    *
  *                 6- Asnmon users                              *
  *                 7- All Q Capture users                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- Q Capture stops with RC 6080 from    *
  *                      waitForAsyncResult                      *
  *                      2- QCapture performance for table with  *
  *                      XML data types is unacceptable when     *
  *                      replicated to multiple targets          *
  *                      3- TRANS_COMMIT_MODE is not correctly   *
  *                      set which leads to potential issues     *
  *                      4- Customer has ZPARM                   *
  *                      BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR,   *
  *                      when asnmon use CHAR( ) to do query,    *
  *                      it caused the IFCID376.                 *
  *                      5- prune sql statements from ASNDB2SQ   *
  *                      in the dynamic statement cache which    *
  *                      are not coded with parameter markers    *
  *                      which has an impact to the performance  *
  *                      of SQL statement caching                *
  *                      6- Asnmon report error message          *
  *                      ASN5154W when the alert on              *
  *                      QCAPTURE_MEMORY uses a value greater    *
  *                      than 2047. When the threshold in        *
  *                      megabytes value is set to greater than  *
  *                      2047 for asnmon QCAPTURE_MEMORY         *
  *                      condition, A calculation error will be  *
  *                      occurred. Now this problem was fixed.   *
  *                      7- When trans_batch_size is used, Q     *
  *                      Capture automatically increases         *
  *                      max_trans so that more MQ messages can  *
  *                      be published by commit_interval,        *
  *                      unless max_trans is supplied            *
  *                      explicitly as a startup parameter, but  *
  *                      no message is issued in the log file    *
  *                      to reflect this adjustment.             *
  ****************************************************************
  1- Q Capture stops with RC 6080 from waitForAsyncResult on a
  batch MQPUT or MQCMIT, but does not print any information about
  affected queues or MQ operation.
  2- Q Capture fetches XML document once for each target
  registered
  3- The problem is when a DDL operation such as ALTER TABLE
  ALTER COLUMN is done on a different member than the member
  where Q Capture is running and the fact that Capture selects
  the table information from the Db2 catalog when processing an
  ALTER (to get the default value for an ADD column for example),
  by processing the transaction on COMMIT1, the select from the
  Db2 catalog can return the old table definition if the COMMIT2
  has not been processed yet.
  4- ASNMON causes Db2 SMF data flooded with IFCD 376 using char
  () function
  5- Missing parameter marker for prune table sql statement
  6- Threshold in megabytes value can not be set to greater than
  2047 for asnmon QCAPTURE_MEMORY condition, When the threshold
  in megabytes value is set to greater than 2047 for asnmon
  QCAPTURE_MEMORY condition, A calculation error will be occurred.
  7- Issue message on Q Capture startup when trans_batch_sz is
  greater than 1 and Q Capture automatically bumps up MAX_TRANS.
  This message will be issued ONLY if MAX_TRANS is the default.
  Note that if the user specifies max_trans in CAPPARMS or from
  command-line, we will not issue the message.  The advantage is
  that any client who currently benefits from
  transparent/automatic adjustment of max_trans will not be
  affected.
  

Problem conclusion

• 1- Q Capture now prints messages with more information about
  potential transactions that may have caused the batch MQPUT or
  MQCMIT to fail.
  2- QCapture is changed to fetch LOB/XML columns only once for
  tables with subscriptions to multiple targets.
  3- The default value for TRANS_COMMIT_MODE is changed to 2 to
  avoid issues with DDL replication and initial load, except for
  proxy mode where it remains 1 to guarantee no data loss in case
  of a disaster between commit1 and commit2
  4- Won't bring IFCID376 after using CHAR9(...) in place of
  CHAR(...) even though ZPARM
  BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR.
  5- use parameter markers for signal_time since it is different
  on every call and causes issue with the statement cache
  6- Customer can't set the threshold in megabytes value to
  greater than 2047 for asnmon QCAPTURE_MEMORY condition,
  Otherwise calculation errors will be occurred. Now this problem
  was fixed.
  7- Q Capture issues asn7358 diagnostic message at startup when
  trans_batch_sz is greater than 1 MAX_TRANS is automatically
  increased.
  
  This PTF adds new columns. Use SASNSAMP(ASNVSQL) for SQL
  replication and ASNCATM for Q replication to add the new columns
  

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:

  UI93187 UI93188 UI93189 UI93190

Modules/Macros

• ASN2BASE ASN2SQLZ ASNACMD  ASNADMSP ASNAPPLY ASNCAP   ASNCATM
  ASNCCMD  ASNCCPWK ASNCDINS ASNCLPAP ASNCLPCL ASNCLPCM ASNCLPCO
  ASNCLPMS ASNCLPQA ASNCTLZD ASNMCMD  ASNMON   ASNMONIT ASNPRUNE
  ASNQACMD ASNQAHKT ASNQAPAG ASNQAPP  ASNQAROW ASNQASUB ASNQBRWZ
  ASNQCAP  ASNQCCMD ASNQCTLZ ASNQDEP  ASNQEXRP ASNQLODZ ASNQMFMT
  ASNQP2PA ASNQP2PI ASNQSPIL ASNQSQLZ ASNQXFMT ASNRBASE ASNSQLCZ
  ASNTDIFF ASNTDSP  ASNTRC   ASNV1140 ASNVSQL
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RB33 PSY UI93187

     UP23/09/01 P F308

• RB34 PSY UI93188

     UP23/09/01 P F308

• RB35 PSY UI93189

     UP23/09/01 P F308

• RB36 PSY UI93190

     UP23/09/01 P F308

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.