-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 is not allowed when either of the following conditions is true:
  • The ALTER TABLE statement is issued with immediate options and definition changes are pending for the table space or any objects within the table space.
  • The ALTER TABLE statement is issued with any option, immediate or pending, and a MOVE TABLE operation is pending for the table.
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 containing table space is not being implicitly dropped 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 or table space.
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.

An ALTER TABLE statement with a DROP COLUMN clause is not allowed if the table definition 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 TABLE or RENAME INDEX statement is not allowed if any definition changes are pending for the object.
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 or table.
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.
21
A CREATE VIEW statement is not allowed if any pending definition changes exist for the column.
22
A CREATE TRIGGER statement that creates an INSTEAD OF trigger is not allowed if the view is dependent on any tables with pending definition changes.
23
A CREATE TRIGGER statement is not allowed if the triggering table has pending definition changes.
24
A CREATE PERMISSION statement is not allowed on a table if the table or any referenced tables have pending definition changes.
25
A CREATE MASK statement is not allowed on a table if the table or any referenced tables have pending definition changes.
26
A CREATE FUNCTION statement that creates an SQL table function is not allowed if any referenced tables have pending definition changes.
27
A CREATE TABLE statement or an ALTER TABLE statement that defines a materialized query table is not allowed if any referenced tables have pending definition changes.
28
After RECOVER is run on the specified table space to a point in time before materialization of pending definition changes, and before REORG is run on the table space to complete the recovery, none of the following statements are allowed:
  • An ALTER TABLESPACE statement with the DROP PENDING CHANGES clause.

The following statements are not allowed on objects in the table space:

  • CREATE TABLE
  • ALTER TABLE
  • RENAME
  • DROP TABLE

The following statements are not allowed on indexes on tables in the table space:

  • CREATE INDEX
  • ALTER INDEX

The following statements are not allowed on auxiliary objects that are associated with the table space:

  • CREATE AUXILIARY TABLE
  • ALTER TABLE
  • DROP TABLE
29
There are pending definition changes for the associated table.
30
An ALTER TABLESPACE statement to change DSSIZE at the partition level is not allowed if any definition changes are pending for the table space.
31
An ALTER TABLE statement to alter limit key is not allowed if there are pending changes due to the insertion of new partitions.
32
An ALTER INDEX statement that changes an index to NOT PADDED is not allowed if the index references a column that has pending definition changes.
33
A CREATE TABLE or ALTER TABLE statement is not allowed if a FOREIGN KEY clause references a parent column that has pending definition changes.
35
An ALTER INDEX statement to change DSSIZE at the index level is not allowed if there is any pending definition change for the index or table space.
36
ALTER TABLE ADD PARTITION ENDING AT x or ALTER TABLE ADD PARTITION ENDING AT x ALTER PARTITION n ENDING at y is not allowed on a partition-by-range table space that has unmaterialized limit key changes at the last logical partition.
37
A data definition statement for an object is not allowed if the object references a table space that has a pending MOVE TABLE operation, or if the object references a table or index in a table space that has a pending MOVE TABLE operation.
38
An ALTER TABLESPACE statement that results in a pending definition change for a LOB table space is not allowed if the table space contains an auxiliary table that has an index with data sets that are not yet defined.
Start of change39End of change
Start of changeStart of changeAn ALTER TABLE statement with the ALTER PARTITIONING clause is not allowed if the object references a table space that has a pending MAXPARTITIONS change.End of changeEnd of change
Start of change40End of change
Start of changeStart of changeAn ALTER TABLE statement with the ADD PARTITION or ALTER PARTITION clause is not allowed if the object references a table space that has a pending change of the partitioning scheme from PBG to PBR.End of changeEnd of change
Start of change41End of change
Start of changeAn ALTER TABLE ADD CLONE statement is not allowed on the base table if an associated LOB or XML table space has pending definition changes.End of change

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 table, 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.
28
Run REORG TABLESPACE with SHRLEVEL REFERENCE on the entire table space to remove the REORG-pending (REORP) restrictive state, and to complete the point-in-time recovery process. Then execute the SQL statement that caused this error again.
29
Apply all pending definition changes to the associated table, and process the statement again.
38

Re-create the auxiliary index with DEFINE YES or re-create the LOB table space as DEFINE NO.

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