PM75117: SQL CAPTURE - ASN0552E SQLCODE -4703 AFTER ALTER COLUMN SET DATA TYPE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • ASN0552E SQLCODE -4703 after ALTER COLUMN SET DATA TYPE when
    tables being altered were created prior to Db2 V8.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: 1- Capture                                   *
    *                 2- Q Capture                                 *
    *                 3- Capture                                   *
    *                 4- capture                                   *
    *                 5- Q Capture                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION: 1- SQL4733 or SQL4703 after altering a  *
    *                      CHAR column to greater length           *
    *                      2- ASNMON alerts that Q Capture is      *
    *                      down                                    *
    *                      3- ASN7004E on an ALTER COLUMN          *
    *                      operation after renaming a column.      *
    *                      4- An unexpected return code "1002"     *
    *                      from routine "getColDefault" when       *
    *                      altering the column data type           *
    *                      5- Q Capture may not send values for    *
    *                      subsecond prevision exceeding six       *
    *                      digits for type TIMESTAMP(7) to         *
    *                      TIMESTAMP(12) effectively truncating    *
    *                      the value at six subsecond precision    *
    *                      digits.                                 *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    1- Columns created in DB2 V7 or below have zero in the CCSID
    column. Capture uses zero to indicate FOR BIT DATA and was
    using the wrong data type for the ALTER COLUMN.
    2- Monitoring table rows are not being inserted at regular
    intervals. Sometimes, more than that 2 monitor_intervals pass
    without a monitoring row. This triggers ASNMON into thinking
    that capture is down.
    3- The version table is not in sync nor correct due to the
    RENAME column
    4- Tables created prior to V6 or tables created with the sql
    "LIKE" to a table prior to V6 do not use the CCSID column in
    SYSCOLUMNS. The CCSID column is zero and capture did not handle
    this.
    5- Q Capture may not send subsecond precision exceeding six
    digits leading to data loss if the source column was defined
    with TIMESTAMP(7) - TIMESTAMP (12).
    

Problem conclusion

  • 1- Capture will handle CCSID=0 and use the correct codepage.
    2- This problem was not deterministic and reproduceable. This
    fix will include diagnostic output on the job log if we do not
    insert a monitoring row for two monitor intervals.
    3- Capture will perform additional checks to ensure that the
    table history in the version table matches that of the system
    catalogs. If they do not match, capture will issue an error
    message. In addition, capture will tolerate BRF tables that
    have only had add column by reinitializing the version tables.
    4- Capture will check and handle a zero in the CCSID column
    when detecting alter data type.
    5- Q Capture does not truncate subsecond precision.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM75117

  • Reported component name

    WS REPLICATION

  • Reported component ID

    5655L8800

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-16

  • Closed date

    2012-11-15

  • Last modified date

    2012-12-04

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

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

    UK83559 UK83561 UK83562 UK83563

Modules/Macros

  • ASNACMD  ASNADMSP ASNAPPLY ASNCAP   ASNCCMD
    ASNMCMD  ASNMIG8  ASNMON   ASNPLXFY ASNQACMD ASNQAPP  ASNQCAP
    ASNQCCMD ASNQDEP  ASNQEXRP ASNQMFMT ASNQXFMT ASNRBASE ASNREG
    ASNSQLCZ ASNTDIFF ASNTDSP  ASNTRC   ASN2BASE ASN2SQLZ
    

Fix information

  • Fixed component name

    WS REPLICATION

  • Fixed component ID

    5655L8800

Applicable component levels

  • RA10 PSY UK83559

       UP12/11/27 P F211

  • RA11 PSY UK83561

       UP12/11/28 P F211

  • RA12 PSY UK83562

       UP12/11/27 P F211

  • RA13 PSY UK83563

       UP12/11/27 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 #:

PM75117

Modified date:

2012-12-04

Translate my page

Machine Translation

Content navigation