Page 1 of 1
SQL error code -803
Posted: Thu Sep 03, 2015 9:24 pm
by Vinay1234
Hello everyone!
could you please tell me the resolution steps for -803(Duplicate key on insert or update.)?
Re: SQL error code -803
Posted: Fri Sep 04, 2015 4:44 am
by Robert Sample
What is the business logic for a duplicate key? Depending upon the application, you could ignore the duplicate record, update fields with data from the duplicate, replace the entire record, or change the key and write it (there can be other possibilities as well). Which of these applies depends upon your site, its applications, and what the business wants. As such, nobody -- on this forum or elsewhere -- can provide you a definitive answer; you MUST work with your site personnel to identify the proper solution and implement it.
Re: SQL error code -803
Posted: Sat Sep 05, 2015 5:41 pm
by zprogrammer
Sqlcode -803 occurs due to duplicate key during insert / Update .
It could be either due due duplicate data or due to bad programming/Design. So you as Robert says you need to understand what is causing the duplicate scenario and try to resolve it by cordinating with the stakeholders
Re: SQL error code -803
Posted: Sat Sep 05, 2015 7:00 pm
by Anuj Dhawan
As has been stated, SQLCODE -803 comes due to duplication of rows in a table which has a unique constraint defined on it. There are three condition when a DB2 program can issue SQLCODE -803:
Delete: Application program tries to delete an existing record in a table which cascades the delete, set to "Null", to a
child table. While the child table has a constrain to have unique records. This can also cause duplicates the child table.
Insert: The application program tries to insert a duplicate record in a table which is constrained to have unique records.
Update: You/application programs are trying to update an existing record in a table (which has a constrained to have unique records)
that will cause the duplication of record in the table.
Below a pseudo code for reference, however it all depicts a way to handle the duplicate which essentially depends on your business need and logic:
Code: Select all
.
.
PROCESS-ADD-AUTH.
PERFORM MOVE-FIELDS-TO-DCLGEN
PERFORM SQL-FOR-ADD
EVALUATE TRUE
WHEN SQLCODE = 0 DISPLAY 'SUCCESSFUL ADD'
WHEN SQLCODE = -803
DISPLAY 'CANNOT ADD '
DISPLAY 'DUPLICATE -803'
WHEN SQLCODE > 0 OR SQLWARN0 = 'W'
PERFORM WARNING-PARA
WHEN SQLCODE < 0 GO TO ERROR-EXIT
END-EVALUATE.
PROCESS-CHANGE-AUTH.
PERFORM MOVE-FIELDS-TO-DCLGEN
PERFORM SQL-FOR-CHANGE
EVALUATE TRUE
WHEN SQLCODE = 0
DISPLAY 'SUCCESSFUL CHANGE'
WHEN SQLCODE = -803
DISPLAY 'CANNOT CHANGE'
DISPLAY 'DUPLICATE -803'
.
.