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

Obtain the fix for this APAR.

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