SQLCODE -803

What SQLCODE -803 means

• -803 means a duplicate key violation on INSERT or UPDATE.
• The SQLSTATE is 23505.
• DB2 rejects the row because a unique index or primary key already holds that key value.

Why -803 happens

• An INSERT uses a primary key value that already exists in the table.
• An INSERT uses a unique-index column value that already exists.
• An UPDATE changes a unique column to a value held by another row.
• Two programs insert the same key at the same time.

Preventing and handling -803

• Check existence first with a SELECT, or design the program to generate unique keys.
• Catch -803 in the error logic with a clear message instead of abending.
• Consider MERGE when 'insert if new, update if exists' is the real requirement.
• Example:
  
  EXEC SQL INSERT INTO EMP (EMPNO, EMPNAME, DEPTNO) VALUES (:WS-EMPNO, :WS-EMPNAME, :WS-DEPTNO) END-EXEC EVALUATE SQLCODE WHEN 0 DISPLAY 'Row inserted' WHEN -803 DISPLAY 'Duplicate key: employee ' WS-EMPNO ' already exists' WHEN OTHER DISPLAY 'Insert failed. SQLCODE = ' SQLCODE STOP RUN END-EVALUATE.