PI94529: Q APPLY PERFORMANCE DEGRADATION AND HIGH CPU USAGE WHEN PROCESS-ING FUNC_LEVEL_MSG MESSAGES INTERSPERSED WITH TRANS MESSAGES

Obtain the fix for this APAR.

APAR status

• Closed as unreproducible in next release.

Error description

• Q Apply may experience high CPU usage and performance
  degradation when processing FUNC_LEVEL_MSG messages sent by Q
  Capture during program restart or when Send Queues are stopped
  and restarted.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Q Replication customers                   *
  *                 2- Q Apply COMMIT_COUNT Users                *
  *                 3- Q Apply                                   *
  *                 4- All Q Capture customers who use change    *
  *                 conditions.                                  *
  *                 5- SQL Apply users                           *
  *                 6- ASNTDIFF users                            *
  *                 7- All Capture customers.                    *
  *                 8- All Q Capture customers.                  *
  *                 9- All Q Capture customers who use change    *
  *                 conditions.                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- Customer asked to provide both the   *
  *                      LSN and the timestamp of the oldest     *
  *                      transaction actually applied.           *
  *                      2- Batch and online processing are      *
  *                      often running at the same time for      *
  *                      some customers. A COMMIT_COUNT value    *
  *                      that works well for online processing   *
  *                      does not work for batch processing.     *
  *                      3- Q Apply may experience high CPU      *
  *                      usage and performance degradation when  *
  *                      processing FUNC_LEVEL_MSG messages      *
  *                      sent by Q Capture during program        *
  *                      restart or when Send Queues are         *
  *                      stopped and restarted.                  *
  *                      4- Q Capture abends with S0C4 at        *
  *                      PostFixElement::compareString() +       *
  *                      0000073C after it processes a STARTQ    *
  *                      or REINIT command.                      *
  *                      5- SQL Apply / ASNLOAD failed with      *
  *                      SORTKEYS error                          *
  *                      6- ASNTDIFF error when code page of     *
  *                      the primary key is 1208                 *
  *                      7- Capture loses data when it           *
  *                      replicates a rollback to a savepoint    *
  *                      if the savepoint is the LRSN/RBA of a   *
  *                      spilled row in the first spilled data   *
  *                      set and the row is not the first row    *
  *                      of the transaction.                     *
  *                      8- IBMQREP_CAPMON NUM_LOGREAD_ERRORS    *
  *                      column is not initialized.              *
  *                      9- Q Capture activates subscriptions    *
  *                      that are defined with change            *
  *                      conditions only if the database code    *
  *                      page HEX value for the '$' character    *
  *                      is x'5B'.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- A new parameter named OLDEST_COMMIT_TIME is introduced to
  IBMQREP_APPLYMON table, which is the commit time of the latest
  applied transaction which is indicated by OLDEST_COMMIT_LSN.
  OLDEST_COMMIT_TIME only gets updated whenever OLDEST_COMMIT_LSN
  is updated. OLDEST_COMMIT_TIME doesn't take heartbeats into
  consideration. OLDEST_COMMIT_TIME is a formatted timestamp.
  
  2- Clients need a way to control whether COMMIT_COUNT commits
  by transactions or commits by rows.
  3- Q Apply FUNC_LEVEL_MSG message processing when there are
  many in-flight transactions in memory nay cause Q Apply browser
  to use excessive CPU leading to Q Apply performance
  degradation.
  4- Capture abends if it uses a change condition variable
  attribute that it has not initialized.
  5- When SQL Apply performs a fullrefresh using ASNLOAD exit and
  the sortKeysTotal value exceeds 2147483648 (2**31), DB2 utility
  will fail with INVALID OPERAND FOR KEYWORD 'SORTKEYS' because
  of buffer overflow.
  6- ASNTDIFF goes through unnecessary code path when code page
  of primary key is 1208, which causes a sql error -330
  7- Capture should read the first spill data set when it
  replicates a rollback to a savepoint if the savepoint is the
  LRSN/RBA of a spilled row in the first spill data set and the
  row is not the first row of the transaction.
  8- IBMQREP_CAPMON NUM_LOGREAD_ERRORS column contains a nonzero
  value when the log read API does not return errors.
  9- Q Capture displays 'ASN7033E, An invalid search condition
  was detected for publication or Q subscription <subscription
  name>', if a change condition variable does not start with HEX
  value x'5B'.
  

Problem conclusion

Temporary fix

Comments

• 1- With this fix, we provide both the LSN and the timestamp of
  the oldest transaction actually applied.
  2- The user can set COMMIT_COUNT_UNIT to T to commit by
  transactions which matches COMMIT_COUNT previous behavior or
  set COMMIT_COUNT_UNIT to R which will allow apply agents to
  commit by the number of rows.
  3- Q Apply FUNC_LEVEL_MSG processing is fixed to avoid any
  excessive CPU usage.
  4- Q Capture has been changed to initialize all change
  condition variable attributes.
  5- Changed the attribute of the variable NUM_ROWS to long long.
  This will ensure the result from calculation for sortKeysTotal
  is in the correct attribute.
  6- With this fix, ASNTDIFF will not go through the code path
  that causes sql error -330
  7- Capture will read the first spill data set when it
  replicates a rollback to a savepoint if the savepoint is the
  LRSN/RBA of a spilled row in the first spill data set and the
  row is not the first row of the transaction.
  8- Q Capture has been changed to initialize its
  NUM_LOGREAD_ERRORS internal counter.
  9- Q Capture has been changed to use database code page
  information to parse subscription change conditions.
  Note: ASNCLP has been updated from db2_v105fp10 n180421.
  

APAR Information

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

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

  UI55579 UI55580 UI55582 UI55581

Modules/Macros

•    ASNAAPP  ASNACMD  ASNACMP  ASNADMSP ASNAFET
  ASNAISO  ASNAMAN  ASNAPPLY ASNAWPN  ASNCAP   ASNCATM  ASNCCMD
  ASNCLPAP ASNCLPCL ASNCLPCM ASNCLPCO ASNCLPMS ASNCLPQA ASNLOAD
  ASNMCMD  ASNMON   ASNQACMD ASNQAHKT ASNQAPAG ASNQAPP  ASNQAROW
  ASNQASUB ASNQBRWZ ASNQCAP  ASNQCCMD ASNQCTLZ ASNQDEP  ASNQEXRP
  ASNQMFMT ASNQXFMT ASNQ1021 ASNQ1140 ASNRBASE ASNTDIFF ASNTDSP
  ASNTRC   ASNV1140 ASN2BASE
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RB33 PSY UI55579

     UP18/05/09 P F805

• RB34 PSY UI55580

     UP18/05/10 P F805

• RB35 PSY UI55581

     UP18/05/09 P F805

• RB36 PSY UI55582

     UP18/05/09 P F805

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.