Start of change

-20385   THE STATEMENT CANNOT BE PROCESSED BECAUSE THERE ARE PENDING DEFINITION CHANGES FOR OBJECT object-name OF TYPE object- type (REASON reason-code)

Explanation

The statement could not be processed because definition changes are pending for the indicated object.

object-name
The name of the object that has pending definition changes.
object-type
The type of object that object-name is.
reason-code
A numeric indicator that provides a more detailed explanation for why the statement was not processed.
1
An ALTER statement cannot contain a mixture of immediate and pending options.
2
An ALTER TABLE statement with immediate options is not allowed if any definition changes are pending for the table space or any objects within the table space.
3
A CREATE TABLE statement is not allowed if any definition changes are pending for the table space.
4
A DROP TABLE statement is not allowed if the table space was explicitly created and any definition changes are pending for the table space.
5
An ALTER TABLESPACE statement to change from a DB2®-managed data set to a user-managed data set is not allowed if any definition changes are pending for the table space unless the table space uses partition-by-growth organization.
6
An ALTER INDEX statement to change from a DB2-managed data set to a user-managed data set is not allowed if any definition changes are pending for the index.
7
A pending option is not allowed if the table space is in an incomplete state.
8
A pending option is not allowed if the table contained in the table space or associated with the index is in an incomplete state.
9
An ALTER INDEX statement to change from COMPRESS NO to COMPRESS YES is not allowed if any definition changes are pending for the index.
10
An ALTER INDEX statement to change PIECESIZE is not allowed if any definition changes are pending for the index or the table space.
11
A RENAME INDEX statement is not allowed if any definition changes are pending for the index.
13
An ALTER INDEX REGENERATE statement is not allowed if any definition changes are pending for the table space or objects within the table space.
14
An ALTER INDEX ADD COLUMN statement is not allowed if any definition changes are pending for the table space or objects within the table space.
15
An ALTER TABLESPACE statement to change FREEPAGE is not allowed if any definition changes are pending for the table space.
16
An ALTER TABLESPACE statement to change CCSID is not allowed if any definition changes are pending for the table space.
17
A CREATE INDEX statement is not allowed if any definition changes are pending for the table space or any objects within the table space.
18
A DROP INDEX statement is not allowed when all of the following conditions are true:
  • The index is a unique index defined on a ROWID column that is defined as GENERATED BY DEFAULT
  • Definition changes are pending for the table space or any objects within the table space.
  • The table space was explicitly created.
19
A DROP INDEX statement is not allowed when both of the following conditions are true:
  • The index is an empty index on an auxiliary table that resides in an explicitly-created LOB table space.
  • Definition changes are pending for the base table space or any objects within the base table space.
20
A DROP TABLE statement is not allowed when both of the following conditions are true:
  • The table is an empty auxiliary table.
  • Definition changes are pending for the base table space or any objects within the base table space.

System action

The statement cannot be processed.

User response

Take the action that corresponds to the reason-code value:

1
Separate the options so that all of the immediate options are in one ALTER statement that is processed first, and all of the pending options are in a separate ALTER statement that is issued afterward.
2
  1. Use REORG TABLESPACE with SHRLEVEL CHANGE or SHRLEVEL REFERENCE to apply all pending definition changes for the table space and its objects.
  2. Issue the ALTER TABLE statement with immediate options for the table.
3
Specify a different table space that does not have any pending definition changes and process the statement again.
4
  1. Use ALTER TABLESPACE DROP PENDING CHANGES to drop the pending definition changes for the table space.
  2. Issue the DROP TABLE statement for the table.
6, 9, or 11
  1. Apply all pending definition changes to the index or the table space and index, as appropriate:
    • If no pending changes exist for the table space, use REORG INDEX with SHRLEVEL CHANGE or SHRLEVEL REFERENCE to apply all pending definition changes for the index.
    • If pending changes exist for the table space, use REORG TABLESPACE with SHRLEVEL CHANGE or SHRLEVEL REFERENCE to apply all pending definition changes for the index and table space.
  2. Process the statement again.
7
Check the status of the table space definition, which is indicated in the STATUS column of the SYSIBM.SYSTABLESPACE table. Complete or correct the table space definition and process the statement again.
8
Check the status of the table definition, which is indicated in the TABLESTATUS column of the SYSIBM.SYSTABLES table. Complete or correct the table definition and process the statement again.
All other values
  1. Run REORG TABLESPACE with SHRLEVEL CHANGE or SHRLEVEL REFERENCE to apply all pending definition changes.
  2. Process the statement again.

SQLSTATE

57007

End of change