IBM Support

Unique constraint violation error when updating a record.

Troubleshooting


Problem

Unique constraint violation error when updating a record.

Cause

This has been identified as a product defect under APAR PI89398

Environment

When using an insert/update operation on the output card of an IBM Transformation Extender (ITX)/WebSphere Transformation Extender (WTX) map, it may be noted that the update operation fails to find a matching primary key (even though the value DOES exist) and then the insert operation fails because the data to update then fails that same primary key.

Please note that this issue only occurs when 'NLS_LENGTH_SEMANTICS' is set to CHAR instead of BYTE.

Diagnosing The Problem

The ITX/WTX Database log file (.dbl) contains the following error.
Error returned by OCIStmtExecute, errcode=1ORA-00001: unique constraint (TableName) violated

Resolving The Problem

Obtain patch for APAR PI89398.

As a workaround the tables can be explicitly created with BYTE length:
COLOUR CHAR(45 BYTE) NOT NULL
instead of
COLOUR CHAR(45) NOT NULL

[{"Product":{"code":"SSVSD8","label":"IBM Transformation Extender"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Database Adapter","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF035","label":"z\/OS"}],"Version":"9.0.0.2;9.0.0.1;9.0.0.0;8.4.1.5;8.4.1.4;8.4.1.3;8.4.1.2;8.4.1.1;8.4.1.0;8.4.0.5;8.4.0.4;8.4.0.3;8.4.0.2;8.4.0.1;8.4.0.0","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
16 June 2018

UID

swg22010102

Page Feedback