PI99238: 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 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 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.