SQLCODE -540: Table definition incomplete, missing index

What it means

On Db2 for z/OS, coding PRIMARY KEY or UNIQUE in CREATE TABLE doesn't always build the index that enforces it. When the table goes into a table space you created yourself, Db2 creates the table and marks its definition as incomplete. It stays incomplete until someone creates the unique index the key needs. When Db2 creates the table space implicitly, it builds those indexes for you.

While the definition is incomplete, Db2 rejects statements that use the table with -540. That covers SELECT, INSERT, UPDATE, DELETE and MERGE, and also DDL that defines a foreign key or a clone table on it.

A primary key isn't the only thing that needs an index. Each unique constraint needs its own unique index. A ROWID column defined GENERATED BY DEFAULT needs an index on that column. The foreign key of a temporal referential constraint needs an index too.

Db2 for LUW builds the primary key index automatically as part of CREATE TABLE, so -540 is mostly something people meet on z/OS.

Common causes

• A DDL job ran CREATE TABLE with a PRIMARY KEY but the CREATE UNIQUE INDEX step was left out, failed, or is still waiting in a later job.
• The table and index DDL were generated into separate members and only the table member was run.
• A unique constraint was defined without creating its index.
• The table has a ROWID column defined GENERATED BY DEFAULT and no index on it.
• A program or a later DDL statement ran against the table before the index step finished.

How to find the problem

1. Check the table's status with SELECT CREATOR, NAME, STATUS, TABLESTATUS FROM SYSIBM.SYSTABLES WHERE CREATOR = 'PROD' AND NAME = 'ORDER_HDR'. STATUS I means the definition is incomplete, and TABLESTATUS shows which kind of index is missing.
2. List the indexes the table already has in SYSIBM.SYSINDEXES, with their UNIQUERULE.
3. Find the primary key columns (KEYSEQ greater than zero in SYSIBM.SYSCOLUMNS) and any unique constraints in SYSIBM.SYSTABCONST, and compare them with the existing indexes.
4. Read the output of the DDL job. A failed or missing CREATE INDEX step is usually there.

How to fix it

• Create the missing unique index on exactly the columns of the primary key or unique constraint. Once it exists, Db2 marks the table definition complete.
• For a ROWID column defined GENERATED BY DEFAULT, create a unique index on that column.
• Keep the CREATE TABLE and its CREATE UNIQUE INDEX statements in the same DDL job, and check the return code of both.
• Rerun the program or DDL that failed once the index is in place.

What it looks like

DSNT408I SQLCODE = -540, ERROR:  THE DEFINITION OF TABLE PROD.ORDER_HDR IS INCOMPLETE BECAUSE IT LACKS A PRIMARY INDEX OR A REQUIRED UNIQUE INDEX
DSNT418I SQLSTATE   = 57001 SQLSTATE RETURN CODE

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.