IBM Support

[SQL0803] Duplicate key value specified.

Troubleshooting


Problem

[SQL0803] Duplicate key value specified.

Symptom

When running an ODBC (Open Database Connectivity) translation map "Duplicate primary Key Insert error" is obtained some times. However when BP (Business Process) is restarted, the insert works without any problems. Error Message

Duplicate primary Key Insert error

Resolving The Problem

The map used is an ODBC(Open Database Connectivity) map which inserts the value into the database.There is an extended rule in the map which does the following

1) It concats the value of a particular field which is mapped to a primary key and WorkFlowId which is obtained from ProcessData. 2) If the length of concatenated data is less than 14 characters then it adds "XXX" to make it into 14 characters.Otherwise it extracts the left 14 characters. 3) After that it tries to insert the data into database table.

So there is a chance of getting duplicate value for that field.As this field is mapped to a primary key it throws "Duplicate Primary Key Insert Error".

For Example we assume the name of the field which is mapped to a primary key is ABC.

If the WorkFlowId id that comes from ProcessData is WF1234 and value of ABC is AH123 then it will concatenate WF1234 and AH123.So the result will be AH123WF1234.So it will be filled with XXXX.So the value will become XXXXAH123WF1234.

Suppose if the workflow id that comes from ProcessData is WF1234 and value of ABC is AH1234567 then it will concatenate WF1234 and AH12345678.So the result will be AH1234567WF1234. As per the extended rule it will take only left 14 characters that is AH1234567WF123.

Again when the BP (Business Process) is executed second time and workflow id from ProcessData is WF1235 and value of ABC is AH1234567 then it will concatenate WF1235 and AH1234567.So the result will be AH1234567WF1235.As per the extended rule it will take only left 14 characters that is AH1234567WF123.In this case when it tries to insert the same value again it results in ?Duplicate Primary key Insert Error?.

This problem will not occur if the BP (Business Process) is restarted.When it is restarted it will fetch a new WorkFlowId.So when it is concatenated with the value mapped to primary key the value will not be a duplicate value.So this error will not occur when the Business Process is restarted.

[{"Product":{"code":"SS3JSW","label":"IBM Sterling B2B Integrator"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Historical Number

NFX5081

Product Synonym

[<p><b>]Severity[</b><p>];Normal

Document Information

Modified date:
31 July 2023

UID

swg21553036