IBM Support

PI99238: Q APPLY NEEDS TO SUPPORT THE DB2 SYSPROC.DSNUTILU STORED PROCEDURE.

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • The Q Apply load function calls the Db2 SYSPROC.DSNUTILS stored
    procedure to run Db2 utilites (LOAD, CHECK DATA, REPAIR).
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: 1- Q Apply Monster processing.               *
    *                 2- All Capture customers.                    *
    *                 3- Q Apply with system period temporal       *
    *                 table targets                                *
    *                 4- All Q Apply customers.                    *
    *                 5- Q Apply COMMIT_COUNT Users                *
    *                 6- All Q Capture customers.                  *
    *                 7- Q Apply for System Temporal Table         *
    *                 targets                                      *
    *                 8- Q Capture with subscriptions using        *
    *                 SEARCH_CONDITION                             *
    *                 9- All customers running Q Capture in proxy  *
    *                 mode using proxy Db2 12.                     *
    ****************************************************************
    * PROBLEM DESCRIPTION: 1- Q Apply may try to read entire       *
    *                      monster transaction into memory         *
    *                      instead of limiting memory usage        *
    *                      within MEMORY_LIMIT settings. This      *
    *                      could lead to memory exhaustion with    *
    *                      ASN0543E reported.                      *
    *                      2- Capture cannot advance its restart   *
    *                      LSN while it processes a global         *
    *                      transaction that does not commit or     *
    *                      rollback.                               *
    *                      3- Q Replication cannot support system  *
    *                      period temporal tables that contain     *
    *                      generated expression columns. DB2 V12   *
    *                      APPCOMPAT503 now provides ability to    *
    *                      replicate to such tables with new       *
    *                      global variable                         *
    *                      SYSIBMADM.REPLICATE_OVERRIDE='Y'        *
    *                      4- The Q Apply load function calls the  *
    *                      Db2 SYSPROC.DSNUTILS stored procedure   *
    *                      to run Db2 utilities (LOAD, CHECK       *
    *                      DATA, REPAIR).                          *
    *                      5- 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.     *
    *                      6- The Q Capture IBMQREP_CAPMON         *
    *                      MAX_TRANS_SIZE column value is invalid  *
    *                      if the maximum transaction size is      *
    *                      larger than 2,147,483,647 bytes.        *
    *                      7- Q Apply may not correctly replicate  *
    *                      UPDATE row operations for system        *
    *                      temporal table having columns that use  *
    *                      generated always expressions on         *
    *                      special register values (like           *
    *                      SESSION_USER).                          *
    *                      8- Q  Capture may fail to activate a    *
    *                      subscription with a valid               *
    *                      SEARCH_CONDITION and reports ASN7200E   *
    *                      stating that the SEARCH_CONDITION is    *
    *                      using $OPERATION variable even though   *
    *                      it does not.                            *
    *                      9- Q Capture running in proxy mode      *
    *                      using proxy Db2 12 displays Db2 reason  *
    *                      code X'00E60870'.                       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    1- Q Apply monster processing may use excessive memory during
    monster processing that may lead to memory exhaustion and
    ASN0543E errors.
    2- Capture users need a command that can prompt capture to
    advance the restart LSN beyond the start of a global
    transaction.
    3- Q Replication should support replication to system period
    temporal tables that contain generated expression columns.
    4- The Q Apply load function needs to be changed to call either
    the Db2 SYSPROC.DSNUTILU stored procedure or the Db2
    SYSPROC.DSNUTILS stored procedure to run Db2 utilities (LOAD,
    CHECK DATA, REPAIR).
    5- Clients need a way to control whether COMMIT_COUNT commits
    by transactions or commits by rows.
    6- The Q Capture IBMQREP_CAPMON MAX_TRANS_SIZE column is
    defined with data type INTEGER and can hold up to 2,147,483,647
    bytes.
    7- Q Apply may not correctly replicate UPDATE row operations
    for system temporal table having columns that use generated
    always expressions on special register values like
    SESSION_USER. Specifically, if the generated expression special
    register value does not change on source in the UPDATE
    operation, Q Apply replays the UPDATE without the column in the
    SET clause which results in target DB2 using the local special
    register value for the row which may have been different from
    source.
    8- Q  Capture may fail to activate a subscription with a valid
    SEARCH_CONDITION. The spurious failure is reported as ASN7200E
    message stating that the variable $OPERATION is not valid in
    the SEARCH_CONDITION column of the IBMQREP_SUBS table.
    9- Q Capture set the IFCID 0306 READS WQALLOPT field to X'03'
    (convert data format and decompress data).
    

Problem conclusion

Temporary fix

Comments

  • 1- Q Apply is fixed to keep memory usage within memory_limit
    settings even when processing monster transactions.
    2- The IGNORETRANS command has been changed to advance the
    restart LSN beyond the start of a global transaction.
    3- Q Replication now sets new DB2 global variable
    SYSIBMADM.REPLICATE_OVERRIDE=Y to support replication to system
    period temporal table with generated expression columns when
    DB2 V12 is at APPCOMPAT 503.
    4- The Q Apply load function has been changed to call either
    the SYSPROC.DSNUTILS or SYSPROC.DSNUTILU stored procedure to
    run Db2 utilities (LOAD, CHECK DATA, REPAIR). Q Apply will
    accept IBMQREP_TARGETS LOAD_TYPE options 6 (DSNUTILU LOAD
    REPLACE) and 106 (DSNUTILU LOAD RESUME).
    5- 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.
    6- Q Capture has been changed to support a IBMQREP_CAPMON
    MAX_TRANS_SIZE column with data type BIGINT or data type
    INTEGER.  ALTER the IBMQREP_CAPMON table to change the
    MAX_TRANS_SIZE column data type to BIGINT if Q Capture
    publishes transactions larger than 2,147,483,647.
    7- Q Apply is fixed to always specify the system temporal table
    columns that use generated always expression on special
    register in the UPDATE operations SET clause. If the column did
    not change on the source, Q Apply lets the UPDATE specify that
    the column be updated with its current value in the row so that
    it does not get overwritten by Q Apply's local special register
    value.
    8- Q Capture SEARCH_CONDITION validation during subscription
    activation is fixed.
    9- Q Capture running in proxy mode using proxy Db2 12 has been
    changed to set the IFCID 0306 READS WQALLOPT field to X'01'
    (decompress data).
    Note: ASNCLP has been updated from db2_v105fp11 n180819.
    

APAR Information

  • APAR number

    PI99238

  • 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-06-15

  • Closed date

    2018-09-27

  • Last modified date

    2018-11-01

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

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

    UI58789 UI58790 UI58791 UI58792

Modules/Macros

  •    ASNACMD  ASNADMSP ASNAPPLY ASNBNDDR ASNBNDSR
    ASNCAP   ASNCATM  ASNCCMD  ASNCCPWK ASNCLPAP ASNCLPB  ASNCLPCL
    ASNCLPCM ASNCLPCO ASNCLPMS ASNCLPQA ASNCTLZD ASNDBCON ASNLOAD
    ASNMCMD  ASNMON   ASNQACMD ASNQAHKT ASNQAPAG ASNQAPP  ASNQAROW
    ASNQASUB ASNQBNDL ASNQBNDR ASNQBRWZ ASNQCAP  ASNQCCMD ASNQCTLZ
    ASNQDEP  ASNQEXRP ASNQLODZ ASNQMFMT ASNQSPIL ASNQXFMT ASNQ1021
    ASNQ1140 ASNRBASE ASNSQLCZ ASNS1021 ASNTDIFF ASNTDSP  ASNTRC
    ASNVSQL  ASNV1021 ASNV1140 ASN2BASE ASN2DBCN ASN2SQLZ
    

Fix information

  • Fixed component name

    WS REPLICATION

  • Fixed component ID

    5655L8800

Applicable component levels

  • RB33 PSY UI58789

       UP18/10/06 P F810

  • RB34 PSY UI58790

       UP18/10/09 P F810

  • RB35 PSY UI58791

       UP18/10/06 P F810

  • RB36 PSY UI58792

       UP18/10/06 P F810

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.



Document information

More support for: InfoSphere Replication Server

Software version: B33

Reference #: PI99238

Modified date: 01 November 2018