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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM73086

  • Reported component name

    WS REPLICATION

  • Reported component ID

    5655L8800

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-09-17

  • Closed date

    2012-10-25

  • Last modified date

    2013-11-13

  • 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.



Rate this page:

(0 users)Average rating

Document information


More support for:

InfoSphere Replication Server

Software version:

A10

Reference #:

PM73086

Modified date:

2013-11-13

Translate my page

Machine Translation

Content navigation