IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

 

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 number

    PI94529

  • Reported component name

    WS REPLICATION

  • Reported component ID

    5655L8800

  • Reported release

    B33

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-03-01

  • Closed date

    2018-05-01

  • Last modified date

    2018-06-04

  • 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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSDP5R","label":"InfoSphere Replication Server"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B33","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 June 2018