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

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• The Q Apply load function calls the Db2 SYSPROC.DSNUTILS stored
  procedure to run Db2 utilities (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.                  *
  ****************************************************************
  * 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.        *
  ****************************************************************
  * 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.
  

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.
  Note: ASNCLP has been updated from db2_v105fp10 s180615.
  

APAR Information

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

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

  UI56835 UI56836 UI56837 UI56838

Modules/Macros

•    ASNAAPP  ASNACMD  ASNACMP  ASNADMSP ASNAFET
  ASNAISO  ASNAMAN  ASNAPP   ASNAPPLY ASNAPRS  ASNAWPN  ASNBNDDR
  ASNBNDSR ASNBPXB  ASNCAP   ASNCCDA  ASNCCDD  ASNCCMD  ASNCCPWK
  ASNCDA   ASNCDD   ASNCDINS ASNCLP   ASNCLPAP ASNCLPB  ASNCLPCL
  ASNCLPCM ASNCLPCO ASNCLPCT ASNCLPEV ASNCLPMS ASNCLPQA ASNCLPRM
  ASNCMPLD ASNCSCZ  ASNCTLZD ASNDADK  ASNDBCON ASNDB2CN ASNDB2SQ
  ASNDEDE  ASNDONE  ASNENUP  ASNENUS  ASNESES  ASNFIFI  ASNFRFR
  ASNISLKM ASNISMKD ASNITIT  ASNJAJP  ASNKOKR  ASNLKMSG ASNLOAD
  ASNMCMD  ASNMDATA ASNMKDIR ASNMON   ASNMONIT ASNMPROC ASNMUPDT
  ASNNONO  ASNPLPL  ASNPRUNE ASNPTBR  ASNQACMD ASNQAHKT ASNQAPAG
  ASNQAPP  ASNQAROW ASNQASUB ASNQBNDE ASNQBNDL ASNQBNDR ASNQBNDT
  ASNQBRWZ ASNQCAP  ASNQCCDA ASNQCCDD ASNQCCMD ASNQCTLZ ASNQDEFQ
  ASNQDEP  ASNQEXRP ASNQFALL ASNQJ001 ASNQJ002 ASNQLODZ ASNQMFMT
  ASNQMSTP ASNQMZ10 ASNQNMIH ASNQPROF ASNQP2PA ASNQP2PI ASNQSPC
  ASNQSPIL ASNQSPS  ASNQSQLZ ASNQXFMT ASNQZV10 ASNQ1001 ASNQ1021
  ASNRBASE ASNRURU  ASNSFALL ASNSMZ10 ASNSQLCZ ASNSVSE  ASNS1001
  ASNS1021 ASNTDIFF ASNTDSP  ASNTDSQL ASNTRC   ASNUOW   ASNVCD
  ASNVMON  ASNVREOR ASNVSQL  ASNV1001 ASNV1021 ASNZHCN  ASNZHTW
  ASNZOSCN ASN2BASE ASN2DBCN ASN2DB2C ASN2DB2Q ASN2SQLZ ASN2ZOSC
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RA21 PSY UI56835

     UP18/07/04 P F807

• RA24 PSY UI56836

     UP18/07/04 P F807

• RA25 PSY UI56837

     UP18/07/04 P F807

• RA26 PSY UI56838

     UP18/07/04 P F807

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.