PM73086: CAPTURE - IMPROVED HANDLING OF ALTER DATA TYPE COMMANDS FOR COLUMNS.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Sql and Q capture, improve handling of alter column
  set datatype commands and prevent ASN0651E.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: 1- Qapply                                    *
  *                 2- parallel ASNTDIFF (z/OS only)             *
  *                 3- non-parallel ASNTDIFF                     *
  *                 4- no data                                   *
  *                 5- no data                                   *
  *                 6- parallel ASNTDIFF (z/OS only)             *
  *                 7- SQL Capture                               *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1-  user issues reint to change         *
  *                          NUM_APPLY_AGENTS to 1               *
  *
  *                      2-  When the users have the DECIMAL     *
  *                      OPTION COMMA in DB2, users will get     *
  *                      the SQLCODE -104 from asntdiff that     *
  *                      indicates the sql statement is          *
  *                      incorrect.                              *
  *                      3-  When the spill file (2GB at most)   *
  *                      is not large enough, the customer will  *
  *                      get the following message: "asntdiff    *
  *                      fseek errno: : EDC5027I The position    *
  *                      specified to fseek() was invalid.       *
  *                      (errno2=0xC03D0020)".                   *
  *                      4- no data                              *
  *                      5- no data                              *
  *                      6-  When multiple indexes on the same   *
  *                      table are defined using the same index  *
  *                      name but different index creator        *
  *                      names, ASNTDIFF is unable to find the   *
  *                      index correctly and thus set NUMBLOCKS  *
  *                      to one.                                 *
  *                      7- Data loss when retention limit       *
  *                      pruning on a high volume system.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1- REINIT to change NUM_APPLY_AGENTS to 1 causes qapply hang
  2- In the preprocessing stage, parallel ASNTDIFF automatically
  removes all the whitespace characters after the comma of both
  SOURCE_SELECT and TARGET_SELECT. However, if the comma is the
  decimal point, a comma intended as a separator must be followed
  by a space.
  3- Non-parallel ASNTDIFF requires a spill file for storing key
  values, but the spill file (2GB at most) might be not large
  enough to hold all the key values.
  4- no data
  5- no data
  6- Before issuing the queries, ASNTDIFF is always checking if
  the eligible indexes are available at both sides. If the index
  does not exist at either side, ASNTDIFF will not partition the
  table. ASNTDIFF is unable to find the eligible indexes when
  multiple indexes have the same index name but different creator
  names. This defect might negatively affect the performance of
  parallel ASNTDIFF.
  7- Retry logic incorrectly moves up the SYNCHPOINT when lock
  timeout occurs with retention limit pruning.
  

Problem conclusion

• 1- Race condition during REINITQ that causes browser to hang
  2- ASNTDIFF does not remove the space following the comma.
  3- An ASN message ASN4076E is issued to indicate that the users
  should split and compare the source and target tables.
  4- no data
  5- no data
  6- ASNTDIFF changes the SQL statement for index finding.
  7- Capture will not update the SYNCHPOINT in it's retry logic
  for lock timeout.
  

Temporary fix

Comments

• **************************************
  HIPER Information
  On very rare occasions, during rentention prune by SQL
  capture, data loss may happen.
  
  During a retention prune all of the eligible rows in the
  CD or UOW table are deleted at one time.  This delete
  could cause a tablespace escalation lock.
  SQL capture reads transaction rows from the Db2 log and writes
  those rows to the CD and UOW table.  Capture then updates the
  register table synchpoint where global_record = Y. It also
  updates the cd_new_synchpoint of the replication source table
  that was changed.
  
  
  
  If a CD or UOW table insert fails capture will rollback all of
  the changes that were made including the update to the register
  table.  Capture then comes back and retries the update to the
  register table but the internal memory synchpoint has advanced
  from the value that was orginally updated.  That's because more
  rows have been read from the log.  The old synchpoint of '100'
  has been advanced to '150'.  Capture then commits everything to
  the register,CD and UOW tables.
  If apply comes in during the retry between the update to
  register and the commit, apply would see the advanced register
  synchpoint (150) but not have the rows from the CD and UOW table
  to fetch.  Apply's read of the register is UR and would see that
  150 synchpoint and apply would treat 150 as the new starting
  point on its next subscription cycle.
  The fix is to remove the update of the register table during
  retry logic and update it normally at the next commit interval.
  That way register synchpoint is never ahead of the CD and UOW
  tables.
  *********************************************
  

APAR Information

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

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

  UK82989 UK82990 UK82991 UK82992

Modules/Macros

•    ASNAAPP  ASNACMD  ASNACMP  ASNADMIN ASNADMSP
  ASNAFET  ASNAISO  ASNAMAN  ASNAPPLY ASNAPRS  ASNAWPN  ASNBNDDR
  ASNBNDMU ASNBNDSR ASNCAP   ASNCCMD  ASNCCPWK ASNCDINS ASNCLP
  ASNCLPAP ASNCLPB  ASNCLPCL ASNCLPCO ASNCLPCT ASNCLPEV ASNCLPMS
  ASNCLPQA ASNCLPRM ASNCMPLD ASNCSCZ  ASNCTLZD ASNDADK  ASNDBCON
  ASNDB2CN ASNDB2SQ ASNDEDE  ASNDONE  ASNENUP  ASNENUS  ASNESES
  ASNFIFI  ASNFRFR  ASNITIT  ASNJAJP  ASNKOKR  ASNLOAD  ASNMCMD
  ASNMDATA ASNMIGFB ASNMIGZD ASNMIG1D ASNMIG2C ASNMIG2S ASNMIG3C
  ASNMIG3S ASNMIG8  ASNMON   ASNMONIT ASNMPROC ASNMUPDT ASNM2V8
  ASNNONO  ASNPLPL  ASNPLXFY ASNPRUNE ASNPTBR  ASNQACMD ASNQAHKT
  ASNQAPAG ASNQAPP  ASNQAROW ASNQASUB ASNQBNDL ASNQBNDR ASNQBNDT
  ASNQBRWZ ASNQCAP  ASNQCCMD ASNQCTLZ ASNQDEFQ ASNQDEP  ASNQEXRP
  ASNQFALL ASNQFA10 ASNQFA95 ASNQFA97 ASNQJ001 ASNQJ002 ASNQLODZ
  ASNQMFMT ASNQMSTP ASNQMZV9 ASNQMZ10 ASNQMZ95 ASNQMZ97 ASNQM2V8
  ASNQPROF ASNQP2PA ASNQP2PI ASNQSPC  ASNQSPIL ASNQSPS  ASNQSTPA
  ASNQSTPC ASNQSTRA ASNQSTRC ASNQTOFF ASNQTON  ASNQXFMT ASNRBASE
  ASNREG   ASNRURU  ASNSMZV9 ASNSMZ10 ASNSQLCF ASNSQLCZ ASNSTPA
  ASNSTPC  ASNSTPM  ASNSTRA  ASNSTRC  ASNSTRM  ASNSVSE  ASNTDIFF
  ASNTDSP  ASNTDSQL ASNTRC   ASNTRCON ASNTRDMP ASNTRFLW ASNTRFMT
  ASNTROFF ASNUOW   ASNZHCN  ASNZHTW  ASNZOSCN ASN2BASE ASN2DBCN
  ASN2DB2C ASN2DB2Q ASN2SQLF ASN2SQLZ ASN2ZOSC CACINIZ
  

Fix information

• Fixed component name

  WS REPLICATION

• Fixed component ID

  5655L8800

Applicable component levels

• RA10 PSY UK82989

     UP12/11/06 P F211

• RA11 PSY UK82990

     UP12/11/07 P F211

• RA12 PSY UK82991

     UP12/11/07 P F211

• RA13 PSY UK82992

     UP12/11/07 P F211

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.