PI80639: SQL APPLY DOES NOT DELETE THE LOG FILES CREATED IN APPLY CYCLE.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQL Apply does not clean up the LOB files correctly at end of
  cycle.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- ASNMON: Suspension not work after ASNMON  *
  *                 restart.                                     *
  *                 2- All Q Capture customers.                  *
  *                 3- Agricultural Bank of China                *
  *                 4- Q Apply                                   *
  *                 5- SQL Apply users                           *
  *                 6- Q Apply                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- The customer has a suspend time      *
  *                      period defined, but the ASNMON gets     *
  *                      recycled as part of the "Saturday       *
  *                      night process". Upon restart, it        *
  *                      starts sending alerts even though the   *
  *                      "Suspend" time period is still in       *
  *                      affect.                                 *
  *                      2- Q Capture ignores the not inlined    *
  *                      LOB and XML column indicators in the    *
  *                      DB2 log records. Q Capture              *
  *                      unnecessarily fetches not inlined LOB   *
  *                      and XML columns from the DB2 catalog    *
  *                      and sends the columns when it           *
  *                      publishes an updated row.               *
  *                      3- Customer stops Qapply while it is    *
  *                      replicating loaddummy. After Qapply is  *
  *                      restarted, it tries to do internal      *
  *                      load instead of loaddummy. Internal     *
  *                      load will connect back to source side.  *
  *                      If customer didn't config CDB for       *
  *                      internal load, Qapply will report -950  *
  *                      error                                   *
  *                      4- Q Apply may not add back all         *
  *                      referential integrity constraints when  *
  *                      activating a child table subscription,  *
  *                      if the child table has RI relationship  *
  *                      with multiple parent tables, and only   *
  *                      some of the parent tables have active   *
  *                      subscriptions.                          *
  *                      5- SQL Apply does not clean up the LOB  *
  *                      files correctly at end of cycle.        *
  *                      6- Q Apply reports ASN0569E internal    *
  *                      error when applying DELETE for          *
  *                      CONDENSED COMPLETE CCDs using           *
  *                      CHANGED_COLS_ONLY=Y and                 *
  *                      CONFLICT_ACTION=I.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- The algorithm used to calculate suspension period did not
  consider the case when suspension spreads two days.
  2- Q Capture fetches not inlined LOB and XML columns from the
  DB2 catalog when it publishes an updated row even if the
  subscription IBMQREP_SUBS CHANGED_COLS_ONLY=Y and the columns
  have not changed.
  3- While checking if sub was doing loaddummy during load subs,
  Apply checks the loaddummy flag outside the Fetch loop. It ends
  up remembering loaddummy flag only for last sub during load
  subs.
  4- Q Apply may delete SAVERI entries of a child table
  subscription with multiple parent tables, without adding back
  all the dropped referential integrity constraints back, if some
  of its parent table are not yet subscribed.
  5- When SPILLFILE=DISK option is used, SQL Apply does not
  delete the LOB files created by DB2 during replication
  processing when the Apply cycle ends.
  6- During load (spill queue processing) Q Apply does not handle
  DELETEs for CONDENSED COMPLETE CCDs correctly when
  CHANGED_COLS_ONLY=Y and CONFLICT_ACTION=I. DELETEs get replayed
  as UPDATE but if the UPDATE gets sql +100, Q Apply tries to do
  INSERT.
  

Problem conclusion

• 1- Modify the algorithm used to validate whether suspension is
  active.
  2- Q Capture will use not inlined LOB and XML column indicators
  in the DB2 row update log record to determine if it needs to
  send the columns when it publishes an updated row and the
  columns have not changed and the TRANS_COMMIT_MODE is set to 2
  (END PHASE 2)
  3- With this fix, checking loaddummy flag is moved into Fetch
  loop.
  4- Q Apply is fixed to retain the referential integrity
  constraint in the SAVERI table, so that it can be added back
  once all the related tables are subscribed and activated.
  5- Apply will delete all the LOB files created by DB2 when the
  current cycle ends.
  6- Q Apply spill queue processing is fixed to ignore DELETEs
  that get sql +100 for CONDENSED COMPLETE CCDs using
  CHANGED_COLS_ONLY=Y and CONFLICT_ACTION=I. B
  Note: ASNCLP has been updated from db2_v105fp9 n170614.
  

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:

  UI48182 UI48183 UI48184 UI48185

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   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 UI48182

     UP17/06/29 P F706

• RA24 PSY UI48183

     UP17/06/29 P F706

• RA25 PSY UI48184

     UP17/06/29 P F706

• RA26 PSY UI48185

     UP17/06/29 P F706

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.