PI26870: DP/PM ASN0589 - CAPTURE DECODING ERROR ON DB2 V11 ZOS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DP/PM ASN0589 - Capture decoding error on DB2 V11 zOS
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Q or SQL Capture users                    *
  *                 2- Q and SQL Capture users                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- Q or SQL Capture log reader          *
  *                      components commit every 30 seconds.     *
  *                      For users performing certain DDL        *
  *                      operations, if Q/SQL Capture is         *
  *                      running and their DDL resource timeout  *
  *                      limit is less than 30 seconds, they     *
  *                      might experience -904.                  *
  *                      2- The logreader program does not       *
  *                      track changes to SYSTABLES log record   *
  *                      formats.                                *
  *                      3- Processing of a transaction
  *                      containing a DROP TABLE operation on a
  *                      unsubscribed table followed by a
  *                      column alteration (anything that
  *                      causes an update to SYSIBM.SYSCOLUMNS)
  *                      causes SQL and Q Capture to stop.
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- A new command line parm LOG_COMMIT_INTERVAL=<value in
  seconds> is provided for the operator to specify a lower
  interval between commits.
  2- Any time the SYSTABLES log record layout differed from the
  latest version in the DB2 system catalog, the capture program
  exhibited decoding or memory errors when trying to decode the
  SYSTABLES log record.  This occurred particularly when reading
  records from CATMAINT jobs.
  3- The processing of changes to the system catalog decodes the
  DELETE to SYSIBM.SYSTABLES as a DROP table operation if the
  table unsubscribed. This is incorrect and causes a bad state
  for the DDL processing for that transaction. Any following
  UPDATE to SYSIBM.SYSCOLUMNS triggers the error. Known ways such
  an update is triggered include alteration to any (subscribed or
  unsubscribed) column, creating a table including defining a
  primary key and altering any (subscribed or unsubscribed) table
  to define a primary key. This list is not exhaustive and other
  DDL operations may cause an update operation to occur on
  SYSIBM.SYSCOLUMNS.
  

Problem conclusion

• 1- Adjust LOG_COMMIT_INTERVAL to be less than DDL resource
  timeout limits if -904 is seen on DDL.
  2- The logreader now dynamically determines the version of the
  SYSTABLES record as it is read, and decodes appropriately.
  SYSTABLES records can be decoded correctly for v9 CM right
  through to v11 NFM.
  3- The DDL processing logic was changed to only detect DROP
  TABLEs for subscribed tables.
  

Temporary fix

Comments

• *********
  * HIPER *
  *********
  Conversion from Db2 10 to Db2 11 CM or Db2 11 CM to NFM
  can result in SQL or Q Capture issuing ASN0691E -
  Error decoding log records for table SYSIBM.SYSTABLE.
  ALSO:
  ASN0589I "Capture" :  "ASN" :  "WorkerThread" The program
  received return code "801" from routine "decodeImagebefore".
  ************************************************************
  Locking issues with SQL or Q Capture.
  DB2 APAR PI12599 (RSU1406) changed the behavior of IFCID 306
  for DB2 10 and DB2 11 Z/OS.  PI12599 added S DBD lock for
  IFCID 306.  After applying this DB2 APAR you could see
  locking issues with user DDL operation for compressed tables
  (like alter column, create trigger, add column etc) and
  holder of the resource will be SQL or Q Capture.
  .
  For example:
  DSNT376I  -D3P5 PLAN=APTBC77 WITH  380
          CORRELATION-ID=CDCD100
          CONNECTION-ID=DB2CALL
          LUW-ID=BBNETCEN.BDB2P05.CDB5D54AEAD1=174520
          THREAD-INFO=BRP:DB2CALL:BRP:CDCD100:DYNAMIC:1:*:*
          IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS
  PLAN=ASNQC101
  WITH
          CORRELATION-ID=WSI301CNLogr
          CONNECTION-ID=RRSAF
          LUW-ID=BBNETCEN.BDB2PV.CDB2FFE748CA=109481
          THREAD-INFO=WSIUSR:RRSAF:LogrdThread:QASN:*:*  :*:*
          ON MEMBER D3PV
  .
  You will see that the task WSI301CNLogr, planname=ASNQC101
  is doing lock status S (in this example).
  .
  By default, Q or SQL Capture log reader thread commits every
  30 seconds.  If you see that user DDL operations time out,
  because Capture is holding a lock on the resource, you need
  to get the PTF for APAR PI26870.  It introduces a new SQL or
  Q Capture run time parm, LOG_COMMIT_INTERVAL=<value in
  seconds> allowing for a lower commit interval for the
  log reader thread.  You can adjust LOG_COMMIT_INTERVAL
  if deadlock is seen on DDL operation and holder of the
  thread is the Capture plan.
  *********************************************************
  

APAR Information

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

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

  UI22229 UI22230 UI22231 UI22232

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 ASNQPROF ASNQP2PA ASNQP2PI ASNQSPC  ASNQSPIL
  ASNQSPS  ASNQSQLZ ASNQXFMT ASNQZV10 ASNQ1001 ASNQ1021 ASNRBASE
  ASNRURU  ASNSFALL ASNSMZ10 ASNSQLCZ ASNSVSE  ASNS1001 ASNS1021
  ASNTDIFF ASNTDSP  ASNTDSQL ASNTRC   ASNUOW   ASNV1001 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 UI22229

     UP14/10/24 P F410 Ž

• RA24 PSY UI22230

     UP14/10/24 P F410 Ž

• RA25 PSY UI22231

     UP14/10/24 P F410 Ž

• RA26 PSY UI22232

     UP14/10/24 P F410 Ž

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.