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.

Before you begin

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.

About this task

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.

Procedure

To complete the definition of a table, use one of the following actions:
  • Create a primary index or alter the table to drop the primary key.
  • Create a unique index on the unique key or alter the table to drop the unique key.
  • Defining a unique index on the ROWID column.
  • Create the necessary LOB objects.

Example

To create the primary index for the project activity table, issue the following SQL statement: 
CREATE UNIQUE INDEX XPROJAC1
  ON DSN8C10.PROJACT (PROJNO, ACTNO, ACSTDATE);