JR48057: ADAPTIVE APPLY DOES NOT DETECT VALUES OF UNIQUE INDEXES CHANGE RESULTING IN DUPLICATE KEY ERRORS

Fix list for IBM InfoSphere Change Data Capture

APAR status

• Closed as program error.

Error description

• During adaptive apply, CDC does not detect if the values of
  unique indexes change. It only detects if primary key changes.
  Normally if the key value changes, CDC should attempt a DELETE
  and then INSERT for an UPDATE operation. Since CDC doesn't
  detect the unique index changes, it attempts an UPDATE directly
  resulting in a unique constraint violation.
  
  Database operation failed.
  A SQL exception has occurred. The SQL error code is '-803'. The
  SQL state is: 23505. The error message is:
  DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505,
  SQLERRMC=1;UDBQA.sa#scriptCode2, DRIVER=3.59.81
  

Local fix

• Define a primary key instead of unique index.
  

Problem summary

• IIDR 10.2 fails with a contraint violation database error when
  replicating using adaptive apply and the target table does not
  have a primary key defined but a unique index.
  

Problem conclusion

• This issue is fixed by applying the following interim fixes
  depending on the database flavor:
  - IIDR 10.2 Interim Fix 3 for DB2 LUW; or
  - IIDR 10.2 Interim Fix 2 for Sybase.
  

Temporary fix

• Define a primary key in the target table instead of a unique
  index.
  

Comments

APAR Information

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

  JR47766

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

Fix information

• Fixed component name

  IS DATA REPLICA

• Fixed component ID

  5725E3000

Applicable component levels

• RA20 PSY

     UP