PH11468: BACK OUT PH04272 FIX FOR "SQL CAPTURE HANGS READING A 974 MEG TRANSACTION" BECAUSE IT CAUSES EXCESSIVE SPILL FILES.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Remove the fix in APAR PH04272 for the "SQL Capture hangs
  reading 974 Meg transaction" issue because it causes execessive
  spill files.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- SQL Apply users                           *
  *                 2- Q Replication user                        *
  *                 3- QApply users                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- The user cannot identify the UTILID  *
  *                      used in ASNLOAD for a specific SQL      *
  *                      Apply instance.                         *
  *                      2- Remove the fix in APAR PH04272 for   *
  *                      the "SQL Capture hangs reading 974 Meg  *
  *                      transaction" issue because it causes    *
  *                      excessive spill files.                  *
  *                      3- New function: three new parameters   *
  *                      have been added to the Q Apply          *
  *                      IBMQREP_APPLYPARMS table                *
  *                      (WARNTXLATENCY, WARNTXEVTS, and         *
  *                      WARNTXRESET). Q Apply will use these    *
  *                      columns to make the browser thread      *
  *                      perform checks on committed and         *
  *                      in-flight transactions to identify the  *
  *                      transactions that are causing Q Apply   *
  *                      to exceed latency.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- It is difficult to associate an UTILID used in ASNLOAD user
  exit, especially when multiple SQL Apply instances are running.
  2- When max memory limit is reached, Q Capture spills inflight
  transactions without sleeping even when there are committed
  transactions that are waiting to be published.
  3- You can now set up apply latency warnings for Q Apply to
  help pinpoint individual transactions that are taking longer
  than desired to be applied to target tables. The WARNTXLATENCY
  parameter sets a threshold for apply latency (the time between
  getting transactions from the receive queue and applying them
  at the target). When the threshold is exceeded, Q Apply issues
  warning messages that help you determine which transactions are
  contributing to latency. On z/OSB., these messages go to the
  console. The WARNTXEVTS parameter sets a limit on the number of
  warnings and WARNTXRESET specifies a reset period. A message
  that identifies each table that is modified by the transaction
  is also issued. Q Apply also issues summary messages after each
  reset period to help with latency tracking.
  

Problem conclusion

• 1- Changed ASNLOAD to include the Apply Qualifier as part of
  the UTILID when invoking the DB2 cross loader.
  2- With the removal of the fix (introduced in APAR PH04272),
  Capture will not spill unnecessarily when max memory limit is
  reached and there are committed transactions. Instead, Capture
  will sleep and wait until committed transactions are published
  and freed from memory.
  3- Users can use these three parameters to monitor long running
  transactions and ultimately find the target tables causing the
  issue.
  Note: ASNCLP has been updated from db2_v105fp11 s190414.
  

Temporary fix

Comments

APAR Information

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

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

  UI62856 UI62857 UI62858 UI62859

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 UI62856

     UP19/05/15 P F905

• RA24 PSY UI62857

     UP19/05/15 P F905

• RA25 PSY UI62858

     UP19/05/15 P F905

• RA26 PSY UI62859

     UP19/05/15 P F905

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.