IBM Support

PH29264: SUPPORT OF Q AND SQL CAPTURE PARAMETER CAPSTART_REORGCHECK

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Capture always checks the Db2 catalog to determine if the table
    is altered and needs to be REORG when activating a subscription.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: 1- All Q and SQL Capture users               *
    *                 2- users using Partition sub and Proxy mode  *
    *                 feature                                      *
    *                 3- Q capture server alert latency warning    *
    *                 4- All SQL Apply users                       *
    *                 5- All Qrep EP users                         *
    *                 6- All Capture users                         *
    *                 7- Q Apply with CCD target CONDENSED=N       *
    ****************************************************************
    * PROBLEM DESCRIPTION: 1- Q and SQL Capture needs to support   *
    *                      Db2 ALTER TABLESPACE MOVE TABLE         *
    *                      2- warmstart proxyMode Q capture        *
    *                      failed if remote Db is not available    *
    *                      and ON_SOURCE_DB_UNAVAIL is R           *
    *                      3- When Capture starts it might take    *
    *                      couple of minutes to initialize and     *
    *                      the CURRENT_LOG_TIME will display       *
    *                      1900-01-01-00.00.00.000000 (zero),      *
    *                      which can not be fixed in engine.       *
    *                      ASNMON generates alerts when it reads   *
    *                      this value. What this customer wants    *
    *                      is for ASNMON to realize this issue     *
    *                      and not send the alerts until           *
    *                      CURRENT_LOG_TIME goes to a non zero     *
    *                      value.                                  *
    *                      4- SQL Apply high CPU consumption in    *
    *                      ASNAISO after migration to DB2 V12      *
    *                      5- inconsistent timestamp for Qrep EP   *
    *                      6- Capture always checks the Db2        *
    *                      catalog to determine if the table is    *
    *                      altered and needs to be REORG when      *
    *                      activating a subscription.              *
    *                      7- Q Apply may fail to resolve          *
    *                      duplicate -803 errors for               *
    *                      non-condensed CCD target after Q        *
    *                      Capture is restarted from older LSN     *
    *                      point                                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    1- Q and SQL Capture will detect the SYSIBM.SYSTABLE update log
    record for ALTER TABLESPACE MOVE TABLE operation and update
    the IFI log filter array with the new PSID for the source table.
    2- Q capture queries system table in remote db to get partition
    key information. Q capture goes down if the query fails because
    remote DB is unavailable.
    3- The requirement is to ignore the alert that qcap latency
    warning when the current log time is 1900-
    4- The CPU costs increased from 3 b , to 300 b , per day in our
    apply-job after migration to DB2 V12.
    5- Due to a logical operation error, the MQ header column
    b
    For most of the messages, the timestamp is 26 bytes.
    6- It is not always possible to determine if REORG is required
    by querying the Db2 catalog. Only the user knows if the change
    was safe.
    7- Q Apply may reports ASN0569E failures when encountering
    duplicates for non-condensed CCD after Q Capture is restarted
    from older LSN point
    

Problem conclusion

  • 1- Q and SQL Capture should continue to work even after a
    source table is moved to a different tablespace.
    2- With this fix, capture stores partition key information into
    two new columns, PART_KEY_COLNO and PART_KEY_ORDERING of
    IBMQREP_SRC_COLS, if these two columns exist. When capture
    warmstart, if remote DB is not available and
    ON_SOURCE_DB_UNAVAIL is R, capture queries IBMQREP_SRC_COLS to
    get partition key information.
    3- Ignore the alert that qcap latency warning when the current
    log time is 1900-
    4- Rewrote some subqueries to improve query performance.
    5- Correct the code defect so that EP always generates a
    correct 'commit_time' for the MQ header column
    6- Introduce a new Q Capture startup parameter
    CAPSTART_REORGCHECK (default is Y for Db2 V11 and N for Db2
    V12) to turn on/off checking for REORG.
    7- Q Apply is fixed to correctly handle duplicates for
    non-condensed CCD targets after Q Capture is restarted from
    older LSN point
    ASNCLP was not updated.
    Function Level: 1140.103
    

Temporary fix

Comments

APAR Information

  • APAR number

    PH29264

  • Reported component name

    WS REPLICATION

  • Reported component ID

    5655L8800

  • Reported release

    B33

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-09-08

  • Closed date

    2020-10-28

  • Last modified date

    2020-12-01

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

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

    UI72310 UI72311 UI72312 UI72313

Modules/Macros

  •    ASNAAPP  ASNACMD  ASNACMP  ASNADMSP ASNAFET
    ASNAISO  ASNAMAN  ASNAPPLY ASNAPRS  ASNAWPN  ASNCAP   ASNCATM
    ASNCCMD  ASNCDINS ASNCLPB  ASNCTLZD ASNMCMD  ASNMON   ASNMONIT
    ASNQACMD ASNQAPP  ASNQAROW ASNQCAP  ASNQCCMD ASNQCTLZ ASNQDEP
    ASNQEXRP ASNQMFMT ASNQXFMT ASNQ1140 ASNRBASE ASNTDIFF ASNTDSP
    ASNTRC   ASNVSQL  ASN2BASE
    

Fix information

  • Fixed component name

    WS REPLICATION

  • Fixed component ID

    5655L8800

Applicable component levels

  • RB33 PSY UI72310

       UP20/11/07 P F011

  • RB34 PSY UI72311

       UP20/11/10 P F011

  • RB35 PSY UI72312

       UP20/11/10 P F011

  • RB36 PSY UI72313

       UP20/11/07 P F011

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.

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

Document Information

Modified date:
02 December 2020