SQLCODE -803 — duplicate key on insert or update

What happened

A load or update program stops part-way through with SQLCODE -803. Rows before the failing one may already be committed.

DSNT408I SQLCODE = -803, ERROR:  AN INSERTED OR UPDATED VALUE IS
         INVALID BECAUSE THE INDEX IN INDEX SPACE XCUST01 CONSTRAINS
         COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE
         VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X'0000001A05'.
DSNT418I SQLSTATE   = 23505 SQLSTATE RETURN CODE

What it means

A unique index (primary key or other unique constraint) already contains the value your statement tried to write. Db2 rejected that one statement; the SQLSTATE is 23505. Earlier statements in the same unit of work are not undone automatically — the program decides whether to roll back.

The SQLERRMC tokens name the index space and the RID of the existing row. Find the index and its columns by querying SYSIBM.SYSINDEXES where INDEXSPACE matches, then SYSIBM.SYSKEYS.

Typical causes

• An input file processed twice, or a job rerun from the top after a partial commit.
• Duplicates inside the input file itself.
• A key generator (counter table, sequence, timestamp) producing a value already in use.
• An UPDATE that changes a key column to a value another row already has.
• Test or converted data loaded into the table outside normal processing.

Symptoms

The error is data-driven: the same input fails at the same record each time. That separates it from -911, which is timing-driven. Do not confuse it with -811, which is about reading too many rows, not writing a duplicate.

Where to look

• The program's DSNTIAR or GET DIAGNOSTICS output and any record counts it logged.
• The Db2 catalog for the index definition.
• The table itself, using SPUFI or a query tool, to see the existing row.
• The scheduler history: was this job, or its input, already run today?

How to diagnose

1. Record SQLCODE, index space and the key value from the program's own log.
2. Identify the unique index and its key columns from the catalog.
3. Query the table for that key and look at the existing row: when and by what was it created?
4. Check whether the input file or GDG generation was already processed.
5. Count duplicates within the input file with a sort or query.
6. If a key generator is involved, compare its current value with the highest key in the table.

How to fix

If the input was already processed, the job may only need to resume after the last committed record, not rerun. If the input has genuine duplicates, get the business owner to decide which wins. If the generator is behind, the DBA or application team resets it under change control.

Programs that legitimately meet existing rows can test for -803 and switch to UPDATE, or use MERGE where your Db2 version supports it. To turn the raw SQLCA into readable text, call DSNTIAR (DSNTIAC in CICS) with the SQLCA and a message buffer, or use GET DIAGNOSTICS to fetch MESSAGE_TEXT, RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE. Either way, write the full message to the job log before the program ends.

How to prevent

• Make loads idempotent: track processed files or use checkpoint keys.
• Validate input for duplicates before the update step.
• Use Db2 sequences or identity columns rather than home-grown counters where possible.
• Commit with a restart point so partial runs resume cleanly.

Production considerations

Interview question

A nightly insert job fails with SQLCODE -803 half-way through. How do you handle it?