PM82917: SQL CAPTURE FAILS TO PRUNE UOW TABLE. ALSO UK91008 DID NOT HAVE ++HOLD, SEE ADDITIONAL INFO. 1 13/03/06 PTF PECHANGE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQL capture issues ASN0188I and fails to normally prune the UOW
  table.
  Additional info:
  This PTF moves Z/OS Replication Servers to arch_level
  1001.  In order for LUW Replication Servers to use
  the 1001 arch_level they must be at V9.7 FP9 or
  higher.
  
  PTF UK91008 introduced migration scripts but failed to
  include a ++HOLD alerting customers to the need to run
  ASNS1001 for SQL replication or ASNQ1001 for Q replication.
  The Q replication capture and apply processes are ok to
  use after running the ASNQ1001 migration script in SASNSAMP.
  The program defect in this APAR is for the SQL capture
  program which fails to correctly prune the UOW table.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Q Capture                                 *
  *                 2- SQL Capture                               *
  *                 3- Capture                                   *
  *                 4- capture                                   *
  *                 5- Users of PARALLEL_SENDQS=Y                *
  *                 6- Qapply                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1- When an XML column was added to a    *
  *                      subscriptions using the ADD COLUMN      *
  *                      signal or REPL_ADDCOL was set to 'Y' Q  *
  *                      Apply rejected the resulting schema     *
  *                      with ASN7621E Reason 2 (source column   *
  *                      length 6 target column length 0). Q     *
  *                      Capture sent an incorrect length in     *
  *                      the schema message.                     *
  *                      2- ASN0113W and no normal pruning for   *
  *                      IBMSNAP_UOW.                            *
  *                      3- A BRF table with only ALTER ADD      *
  *                      COLUMN operations performed after       *
  *                      reorg does not successfully activated   *
  *                      because an ALTER DATA TYPE has          *
  *                      occurred in the past.                   *
  *                      4- debug statistics at the end of job   *
  *                      was removed.                            *
  *                      5- In this mode, a GAP message          *
  *                      ASN7551E is triggered when an expected  *
  *                      msgid has not arrived within a timeout  *
  *                      period, but higher msgids are seen on   *
  *                      the queue.                              *
  Q Apply using PRUNE_METHOD=2, on restart may stop pruning
  IBMQREP_DONEMSG table and cause program memory to slowly grow
  resulting in ASN0543E out of memory condition.
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- Q Apply reports ASN7621E Reason 2 after adding an XML column
  to a subscription using the ADD COLUMN signal or REPL_ADDCOL =
  'Y'. The cause is an incorrect XML column length in the schema
  message.
  2- Regular IBMSNAP_UOW pruning does not occur for certain LSN
  ranges. These rows will only be pruned by retention limit
  pruning. Retention limit pruning is unaffected.
  3- Capture forced users to reorg before subscription activate
  if there was any ALTER DATA TYPE performed on the table.
  4- The debug statements were mistakenly removed.
  5- The timeout before issuing a GAP message ASN7551E is
  designed as 60 seconds from the time Q Apply first started
  waiting for a particular msgid, but due to a coding defect,
  this interval was reduced to as little as 2 seconds.
  6- When using PRUNE_METHOD=2 (default), a restart of Q Apply or
  a restart of a receive queue (STOPQ followed by STARTQ) may
  cause pruning of IBMQREP_DONEMSG table to come to a halt.  This
  consequently causes an in-memory list structure to slowly grow
  ultimately resulting in out of memory condition with Q Apply
  terminating with ASN0543E error message.
  

Problem conclusion

• 1- Q Capture has been fixed to include the correct XML length
  in the schema message.
  2- IBMSNAP_UOW pruning will resume.
  3- The new check will only force a reorg for BRF tables that
  have had ALTER DATA TYPE but no subsequent reorg.
  4- Debug statistics are restored at end of job.
  5- The correct time wait interval is restored, and a user will
  not see ASN7551E unless Q Apply has waited for 60 seconds, and
  higher msgids are seen on the queue.
  6 - the problem has been corrected.
  

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:

  UK92656 UK92653 UK92654 UK92655

Modules/Macros

•    ASNAAPP  ASNACMD  ASNACMP  ASNADMSP ASNAFET
  ASNAISO  ASNAMAN  ASNAPPLY ASNAPRS  ASNAWPN  ASNCAP   ASNCCMD
  ASNMCMD  ASNMIG8  ASNMON   ASNQACMD ASNQAPP  ASNQAROW ASNQASUB
  ASNQBRWZ ASNQCAP  ASNQCCMD ASNQDEP  ASNQEXRP ASNQMFMT ASNQXFMT
  ASNRBASE ASNSQLCZ ASNTDIFF ASNTDSP  ASNTDSQL ASNTRC   ASN2BASE
  ASN2SQLZ
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RA10 PSY UK92653

     UP13/04/02 P F304

• RA11 PSY UK92654

     UP13/04/02 P F304

• RA12 PSY UK92655

     UP13/04/02 P F304

• RA13 PSY UK92656

     UP13/04/02 P F304

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.