SQL error code -803

[Vinay1234]

Hello everyone!

could you please tell me the resolution steps for -803(Duplicate key on insert or update.)?

[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.

[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

[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'
             .
             .