Fixing tables with incomplete definitions
If a table has an incomplete definition, you cannot load the table, insert data, retrieve data, update data, or delete data. You can however drop the table, create the primary index, and drop or create other indexes.
About this task
To check if a table has an incomplete definition, look at the STATUS column in SYSIBM.SYSTABLES. The value I indicates that the definition is incomplete.
A table definition is incomplete
in any of the following circumstances:
- If the table is defined with a primary or unique key and
all of the following conditions are true:
- The table space for the table was explicitly created.
- The statement is not being run with schema processor.
- The table does not have a primary or unique index for the defined primary or unique key.
- If the table has a ROWID column that is defined as generated
by default and all of the following conditions are true:
- The table space for the table was explicitly created.
- The SET CURRENT RULES special register is not set to STD.
- No unique index is defined on the ROWID column.
- If the table has a LOB column and all of the following
conditions are true:
- The table space for the table was explicitly created.
- The SET CURRENT RULES special register is not set to STD.
- No all auxiliary LOB objects are defined for the LOB column.
You can complete the table definition by performing one
of the following actions, depending on why the table definition was
incomplete:
- Creating a primary index or altering the table to drop the primary key.
- Creating a unique index on the unique key or altering the table to drop the unique key.
- Defining a unique index on the ROWID column.
- Creating the necessary LOB objects.
Example of creating a primary index: To create the primary index for the project activity
table, issue the following SQL statement:
CREATE UNIQUE INDEX XPROJAC1
ON DSN8A10.PROJACT (PROJNO, ACTNO, ACSTDATE);