MainframeKit

-603

SQLSTATE 23515NEGATIVE SQLCODE

CREATE UNIQUE INDEX failed because the table already contains rows with duplicate values in the index columns.

MOST LIKELY

Duplicate rows created by a load or a rerun of a job.

FIRST CHECK

Find the duplicates with a GROUP BY on the index columns HAVING COUNT(*) > 1.

TRY IT ▸

MainframeKit assistant

What it means

You asked Db2 to build a unique index, but some existing rows share the same key values (or overlapping periods, for a period-based index). A unique index cannot be built over duplicates, so the statement is not processed.

You need to decide whether the duplicates are real data problems or whether the index should not be unique.

Typical causes

  • Duplicate rows created by a load or a rerun of a job.
  • A business rule that was never enforced before the index was added.
  • The wrong columns chosen for the unique index.

How to fix it

  1. Find the duplicates with a GROUP BY on the index columns HAVING COUNT(*) > 1.
  2. Remove or correct the duplicate rows if they are errors, then create the index again.
  3. If duplicates are valid, create a non-unique index instead.

Example

Illustrative only; names and values are invented.

SELECT EMPNO, COUNT(*) FROM MYSCHEMA.EMP GROUP BY EMPNO HAVING COUNT(*) > 1