PH43905: add counters for search_condition evaluation

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• add counters for search_condition evaluation
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- All Capture users                         *
  *                 2- All Capture users                         *
  *                 3- All Q Capture users                       *
  *                 4- All Q Capture users                       *
  *                 5- All Capture users using trans_batch_sz    *
  *                 6- All QCapture users                        *
  *                 7- customers creating/upgrading control      *
  *                 tables                                       *
  *                 8- All Users                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- Need to add DROPTAB_ACTION support   *
  *                      for SQL Capture. The same parameter is  *
  *                      already supported by Q Capture.         *
  *                      2- A multi column ALTER TABLE..ALTER    *
  *                      COLUMN..SET DATATYPE CHAR(...) that     *
  *                      modifies multiple char columns to a     *
  *                      length value of a larger size fails     *
  *                      with multiple ASN7621E error messages   *
  *                      in Apply when attempting to replicate   *
  *                      the alters at the target.               *
  *                      3- Need to analyze performance issues   *
  *                      related to Capture's handling of XML    *
  *                      columns                                 *
  *                      4- add counters for search_condition    *
  *                      evaluation                              *
  *                      5- Capture sends a corrupted            *
  *                      transaction message: the first row may  *
  *                      show an invalid sub_id.                 *
  *                      6- Capture failed -206 when inserting   *
  *                      to IBMQREP_CAPMON table                 *
  *                      7- ASNCATM incorrectly handles          *
  *                      MQ_BYTES in CAPQMON and APPLYMON.       *
  *                      8- Update z/OS create control table     *
  *                      samples with new columns                *
  ****************************************************************
  1- When a subscribed table is dropped, the user needs to tell
  Capture how to handle the subscription.
  2- Capture cannot handle DDL statement that involves multiple
  operations that appear in the Db2 log.
  3- Collect more information about LOBXML processing
  4- In order to provide a clear explanation of where Capture
  spends its time for search condition evaluation, new counters
  are added to the CAPQMON table.
  5- When trans_batch_sz is used, particularly when there is a
  mix of very small (OLTP) transactions followed by very large
  transactions and the buffer used for writing the transactions
  to MQ is full, having reached MAX_MESSAGE_SIZE; and there is
  already fewer than trans_batch_sz transactions written into the
  buffer; Capture may incorrectly copy the rows accumulated for
  the transaction not yet committed and not part of the batch,
  causing a message corruption.
  6- Capture failed -206 when inserting to IBMQREP_CAPMON because
  the ROWS_SPILLED & SPILL_COUNT columns are not defined
  7- If CAPQMON/APPLYMON are defined with MQ_BYTES as type
  BIGINT, ASNCATM on upgrade will try to alter the column to type
  INT.
  8- Need to add new columns to the create control table samples.
  

Problem conclusion

• 1- Add support for "DROPTAB_ACTION" parameter for SQL Capture.
  2- Capture is changed to handle multiple operations that appear
  in the Db2 log for a single DDL statement.
  3- Add two new columns, LOBXML_READ_TIME and NUM_LOBXML_READ,
  to the CAPQMON table
  4- Added columns NUM_SEARCH_COND_EVAL and SEARCH_COND_EVAL_TIME
  to the CAPQMON table
  5- Code is fixed to correctly copy the rows into the
  transaction message for the rows following the batched
  transactions.
  6- Changed the ROWS_SPILLED & SPILL_COUNT columns as optional
  columns in CAPMON table.
  7- ASNCATM will only alter MQ_BYTES to BIGINT, if it is
  currently INT, or do nothing, if it is currently BIGINT.
  8- Samples ASNCATM, ASNQCTLZ, ASNCTLZD, and ASNVSQL have been
  updated. Please run ASNVSQL for SQL replication. This will
  generate the scripts for missing columns.
  ASNCLP was not updated.
  Function Level: 1140.103
  

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:

  UI79721 UI79722 UI79723 UI79724

Modules/Macros

• ASN2BASE ASNACMD  ASNADMSP ASNAPPLY ASNCAP   ASNCATM  ASNCCMD
  ASNCDINS ASNCTLZD ASNMCMD  ASNMON   ASNQACMD ASNQAPP  ASNQCAP
  ASNQCCMD ASNQCTLZ ASNQDEP  ASNQEXRP ASNQMFMT ASNQXFMT ASNRBASE
  ASNTDIFF ASNTDSP  ASNTRC   ASNVSQL
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RB33 PSY UI79721

     UP22/03/16 P F203  

• RB34 PSY UI79722

     UP22/04/01 P F203  

• RB35 PSY UI79723

     UP22/04/01 P F203  

• RB36 PSY UI79724

     UP22/03/17 P F203  

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.