MainframeKit

-803

SQLSTATE 23505NEGATIVE SQLCODE

An INSERT or UPDATE would create two rows with the same value in a unique index. Usually a reprocessed file or a key-generation problem.

MOST LIKELY

An input file processed twice, or a job rerun from the top after a partial commit.

FIRST CHECK

Record SQLCODE, index space and the key value from the program's own log.

TRY IT ▸

Block size and space calculator

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.

How to fix it

  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.

Example

Illustrative only; names and values are invented.

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