PM81075: SQL CAPTURE ISSUES ASN0071E AFTER ALTER COLUMN OF REPLICATION SOURCE AND CD TABLE.

APAR status

• Closed as documentation error.

Error description

• SQL capture issues ASN0071E after alter column of replication
  source and CD table.
  

Local fix

Problem summary

• If a replication source table and cd table
  have columns altered capture can issue an error.  Example:
  ASN0071E CAPTURE "ASNJIM":  "WorkerThread".  The data type
  attribute of the column "COL4" in the CD table
  "STEWART"."CDTABLEDEC1" is not compatible with the data type
  attribute of the corresponding source column.  Typically
  ASN0071E will happen if the source and cd table columns are
  altered when capture is stopped.
  

Problem conclusion

• The SQL Replication Guide and Reference,
  publication SC19-3194-00, chapter 12, will be changed to say
  that a CD column should not be altered.  Capture will
  automatically make the change when the source column is altered.
  

Temporary fix

Comments

• To recover from this error:
  1) Reorg the tablespace of the source table using the IBM Db2
  reorg utility
  2) Run these two SQL statements to clear the entries in the
  COLVERSION and TABVERSION tables.
    DELETE FROM
    !CSH.IBMQREP_COLVERSION COL
     WHERE COL.TABLEID1 =
      (SELECT TAB.TABLEID1
         FROM !CSH.IBMQREP_TABVERSION TAB
          WHERE TAB.SOURCE_OWNER = 'X_OWNER'
           AND TAB.SOURCE_NAME = 'X_NAME')
           AND COL.TABLEID2 =
             (SELECT TAB.TABLEID2
               FROM !CSH.IBMQREP_TABVERSION TAB
               WHERE TAB.SOURCE_OWNER = 'X_OWNER'
                AND TAB.SOURCE_NAME = 'X_NAME');
  ---
  DELETE FROM !CSH.IBMQREP_TABVERSION
   WHERE
    SOURCE_OWNER = 'X_OWNER'  AND
    SOURCE_NAME  = 'X_NAME';
  --
  (change !CSH to your capture_schema.
  Change x_owner and x_name to the source table owner
  and name for the CD table in the ASN0071E error.)
  Example:
  DELETE FROM
    ASNJIM.IBMQREP_COLVERSION COL
     WHERE COL.TABLEID1 =
      (SELECT TAB.TABLEID1
         FROM ASNJIM.IBMQREP_TABVERSION TAB
          WHERE TAB.SOURCE_OWNER = 'STEWART'
           AND TAB.SOURCE_NAME = 'TABLEDEC1')
           AND COL.TABLEID2 =
             (SELECT TAB.TABLEID2
               FROM ASNJIM.IBMQREP_TABVERSION TAB
               WHERE TAB.SOURCE_OWNER = 'STEWART'
                AND TAB.SOURCE_NAME = 'TABLEDEC1');
  DELETE FROM ASNJIM.IBMQREP_TABVERSION
   WHERE
    SOURCE_OWNER = 'STEWART'  AND
    SOURCE_NAME  = 'TABLDEC1';
  

APAR Information

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

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

Publications Referenced

SC193194

Fix information

Applicable component levels