DROP

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

To drop the following objects, the privilege set must include at least one of the listed authorities or privileges:

Table, table space, or index:

• Ownership of the object (for an index, the owner is the owner of the table or index)
• DBADM authority
• SYSADM or SYSCTRL authority
• System DBADM

If the table space is in a database that is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

Database:

• The DROP privilege on the database
• DBADM or DBCTRL authority for the database
• SYSADM or SYSCTRL authority
• System DBADM

If the database is implicitly created, the privileges must be on the implicit database or on DSNDB04.

Alias, storage group, or view:

• Ownership of the object
• SYSADM or SYSCTRL authority
• System DBADM

Package:

• Ownership of the package
• The BINDAGENT privilege granted from the package owner
• PACKADM authority for the collection or for all collections
• SYSADM or SYSCTRL authority

Synonym:

Ownership of the synonym

Role or trusted context:

• Ownership of the object
• SYSADM or SYSCTRL authority
• SECADM

If the installation parameter SEPARATE SECURITY is NO, SYSADM authority has implicit SECADM and SYSCTRL authority and can drop a role or trusted context.

Row permission or column mask:

At least SECADM authority

Distinct type, sequence, stored procedure, trigger, or user-defined function:

• Ownership of the object ¹
• The DROPIN privilege on the schema
• SYSADM or SYSCTRL authority
• System DBADM

The authorization ID that matches the schema name implicitly has the DROPIN privilege on the schema.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is the union of the privilege sets that are held by each authorization ID of the process. If running in a trusted context with a role, the privilege set also includes those privileges that are held by the role that is associated with the primary authorization ID. However, the implicit schema match does not apply to the role when determining if DROPIN schema privilege is held.

Description

ALIAS alias-name

Identifies the alias to drop. The name must identify an alias that exists at the current server. Dropping an alias has no effect on any view, materialized query table, or synonym that was defined using the alias.

If the alias is referenced in the definition of a row permission or a column mask, it cannot be dropped.

DATABASE database-name

Identifies the database to drop. The name must identify a database that exists at the current server. DSNDB04 or DSNDB06 must not be specified. The privilege set must include SYSADM authority.

Whenever a database is dropped, all of its table spaces, tables, index spaces, and indexes are also dropped. Any pending changes to the definitions of the table spaces and indexes in the database are also dropped.

You can drop a database that contains a history table only if the database also contains the associated system-period temporal table. You can drop a database that contains a system-period temporal table when the associated history table is in another database. In this case, the action cascades to drop the history table in the other database.

The database cannot be dropped if it is associated with an accelerator-only table.

FUNCTION or SPECIFIC FUNCTION

Identifies the function to drop. The function must exist at the current server, and it must have been defined with the CREATE FUNCTION statement. The particular function can be identified by its name, function signature, or specific name.

Functions that are implicitly generated by the CREATE TYPE statement cannot be dropped using the DROP statement. They are implicitly dropped when the distinct type is dropped.

As indicated by the default keyword RESTRICT, the function is not dropped if any of the following dependencies exist:

• Another function is sourced on the function.
• A view uses the function.
• A trigger package uses the function.
• The definition of a materialized query table uses the function.
• The definition of a row permission or a column mask uses the function.

When a function is dropped, all privileges on the function are also dropped. Any packages that are dependent on the function dropped are made inoperative.

FUNCTION function-name

Identifies the function by its name. The function-name must identify exactly one function. The function can have any number of parameters defined for it. If there is more than one function of the specified name in the specified or implicit schema, an error is returned.

FUNCTION function-name (parameter-type,...)

Identifies the function by its function signature, which uniquely identifies the function. The function-name (parameter-type, ...) must identify a function with the specified function signature. The specified parameters must match the data types in the corresponding position that were specified when the function was created. The number of data types, and the logical concatenation of the data types is used to identify the specific function instance which is to be dropped. Synonyms for data types are considered a match.

If the function was defined with a table parameter (the LIKE TABLE name AS LOCATOR clause was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), the function signature cannot be used to uniquely identify the function. Instead, use one of the other syntax variations to identify the function with its function name, if unique, or its specific name.

If function-name () is specified, the function identified must have zero parameters.

function-name

Identifies the name of the function.

(parameter-type,...)

Identifies the parameters of the function.

If an unqualified distinct type name is specified, DB2 searches the SQL path to resolve the schema name for the distinct type.

For data types that have a length, precision, or scale attribute, use one of the following:

• Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a function defined with a data type of DEC(7,2). Similarly DECFLOAT() will be considered a match for DECFLOAT(16) or DECFLOAT(34). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
• If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
• If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

For data types with a subtype or encoding scheme attribute, specifying the FOR subtype DATA clause or the CCSID clause is optional. Omission of either clause indicates that DB2 ignores the attribute when determining whether the data types match. If you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.

AS LOCATOR

Specifies that the function is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or a distinct type based on a LOB.

SPECIFIC FUNCTION specific-name

Identifies the function by its specific name. The specific-name must identify a specific function that exists at the current server.

INDEX index-name

Identifies the index to drop. The name must identify a user-defined index that exists at the current server but must not identify a populated index on an auxiliary table or an index that was implicitly created for a table that contains an XML column. (For details on dropping user-defined indexes on catalog tables, see SQL statements allowed on the catalog.) A populated index on an auxiliary table can only be dropped by dropping the base table. The name must not identify an auxiliary table for an object that is involved in a clone relationship.

If the index that is dropped was created by specifying the ENDING AT clause to define partition boundaries, the table is converted to use table-controlled partitioning. The high limit key for the last partition is set to the highest possible value for ascending key columns or the lowest possible value for descending key columns.

Whenever an index is directly or indirectly dropped, its index space is also dropped. The name of a dropped index space cannot be reused until a commit operation is performed. Any pending changes to the definitions of the index is also dropped.

If the index is a unique index used to enforce a unique constraint (primary or unique key), the unique constraint must be dropped before the index can be dropped. In addition, if a unique constraint supports a referential constraint, the index cannot be dropped unless the referential constraint is dropped.

However, a unique index (for a unique key only) can be dropped without first dropping the unique key constraint if the unique key was created in a release of DB2 before Version 7 and if the unique key constraint has no associated referential constraints. For information about dropping constraints, see ALTER TABLE.

If the table space is explicitly created and a unique index is dropped and that index was defined on a ROWID column that is defined as GENERATED BY DEFAULT, the table can still be used, but rows cannot be inserted into that table.

If the table space is implicitly created, the index cannot be dropped if it is defined on a ROWID column that is defined as GENERATED BY DEFAULT.

If an empty index on an auxiliary table is dropped, the base table is marked incomplete. If the base table space is implicitly created, the index on an auxiliary table cannot be dropped.

Drop index will result in the deletion of rows in the SYSCOLDIST and SYSCOLDISTATS catalog tables if no other indexes on the table have the same column group in their key sequence prefix.

MASK mask-name

Identifies the column mask to drop. The name must identify a column mask that exists at the current server.

PACKAGE collection-id.package-name

Identifies the package version to drop. The name plus the implicitly or explicitly specified version-id must identify a package version that exists at the current server. Omission of the version-id is an implicit specification of the null version.

The name must not identify a trigger package or a package that is associated with an SQL routine. A trigger package can only be dropped by dropping the associated trigger or subject table. A package that is associated with a native SQL procedure can only be dropped with an ALTER PROCEDURE statement with a DROP VERSION clause that specifies the particular version that is to be dropped, or with a DROP PROCEDURE statement if it is the only version that is defined for the procedure.

Specify this clause to drop a package that is created as the result of a BIND COPY command used to deploy a version of a native SQL procedure.

If a package has current, previous, and original copies, the DROP statement will drop all copies.

VERSION version-id

version-id is the version identifier that was assigned to the package's DBRM when the DBRM was created. If version-id is not specified, a null version is used as the version identifier.

Delimit the version identifier when it:

• Is generated by the VERSION(AUTO) precompiler option
• Begins with a digit
• Contains lowercase or mixed-case letters

For more on version identifiers, see the information on preparing an application program for execution in DB2 Application Programming and SQL Guide.

PERMISSION permission-name

Identifies the row permission to drop. The name must identify a row permission that exists at the current server. The name must not identify the default row permission that was created implicitly by DB2.

PROCEDURE procedure-name

Identifies the stored procedure to drop. The name must identify a stored procedure that has been defined with the CREATE PROCEDURE statement at the current server.

All versions of the native SQL procedure are dropped; all privileges on the procedure are also dropped. In addition, any packages that are dependent on the procedure are marked invalid.

If the procedure is a native SQL procedure, use an ALTER PROCEDURE statement with the DROP VERSION clause to drop a specific version of a procedure. Use a DROP PACKAGE statement to drop a package for a version of the procedure that is created using the BIND COPY command.

ROLE role-name

Identifies the role to drop. role-name must identify a role that exists at the current server.

When a role is dropped, all privileges and authorities that have been previously granted to that role are revoked. If the role that is dropped is the owner of statements in the dynamic statement cache, the cached statements are invalidated.

The role is not dropped if any REVOKE restrictions are encountered. REVOKE restrictions include the following:

• Restrictions that are encountered when dependent privileges are included when the privileges of a role are revoked.
• The role is the grantor of any privilege or authority that used ACCESSCTRL or SECADM authority to perform the grant.

If RESTRICT is specified, the role is not dropped is any of the following dependencies exist:

• The role is associated with any trusted context or any user in a trusted context.
• The role is associated with a currently running thread.
• The role is the owner of any of the following objects:

  Alias Column mask Database Distinct type Index JAR file Materialized query table Package Role

  Row permission Sequence Storage group Stored procedure Table Table space Trigger Trusted context User-defined function View

SEQUENCE sequence-name

Identifies the sequence to drop. The name must identify an existing sequence at the current server.

sequence-name must not be the name of an internal sequence object that is used by DB2 (including an implicitly generated sequence for a DB2_GENERATED_DOCID_FOR_XML column). Sequences that are generated by the system for identity columns or implicitly created databases cannot be dropped by using the DROP SEQUENCE statement. A sequence object for an identity column is implicitly dropped when the table that contains the identify column is dropped.

The default keyword RESTRICT indicates that the sequence is not dropped if any of the following dependencies exist:

• A trigger that uses the sequence in a NEXT VALUE or PREVIOUS VALUE expression exists.
• An inline SQL function that uses the sequences in a NEXT VALUE or PREVIOUS VALUE expression exists.

Whenever a sequence is dropped, all privileges on the sequence are also dropped, and the packages that refer to the sequence are invalidated. Dropping a sequence, even if the drop process is rolled back, results in the loss of the still-unassigned cache values for the sequence.

STOGROUP stogroup-name

Identifies the storage group to drop. The name must identify a storage group that exists at the current server but not a storage group that is used by any table space or index space.

For information on the effect of dropping the default storage group of a database, see Dropping a default storage group.

SYNONYM synonym

Identifies the synonym to drop. In a static DROP SYNONYM statement, the name must identify a synonym that is owned by the owner of the plan or package. In a dynamic DROP SYNONYM statement, the name must identify a synonym that is owned by the SQL authorization ID. Thus, using interactive SQL, a user with SYSADM authority can drop any synonym by first setting CURRENT SQLID to the owner of the synonym.

Dropping a synonym has no effect on any view, materialized query table, or alias that was defined using the synonym, nor does it invalidate any packages that use such views, materialized query tables, or aliases.

If the synonym is referenced in the definition of a row permission or a column mask, it cannot be dropped.

TABLE table-name or alias-name

Identifies the table to drop. The name must identify a table that exists at the current server. It must not identify a catalog table, a directory table, a table in a partitioned table space, a table that is implicitly created for an XML column, or a populated auxiliary table. A table in a partitioned table space can be dropped only by dropping the table space. A populated auxiliary table or a table that is implicitly created for an XML column can be dropped only by dropping the associated base table.

If alias-name is specified, the actual table is dropped as if table-name were specified. However, the alias is not dropped. It can be dropped by using the DROP ALIAS statement.

When a table is directly or indirectly dropped, the following items are also dropped:

• All privileges on the table
• All referential constraints in which the table is a parent or dependent
• All synonyms, views, and indexes that are defined on the table
• All row permissions (including the default row permission)
• All column masks that are created for the table

If the table space for the table was implicitly created, it is also dropped. However, if the containing database was implicitly created, it is not dropped. Any pending changes to the definitions of the dropped table space and indexes are also dropped.

For more information, see Dropping an implicitly created database.

When a table is directly or indirectly dropped, all materialized query tables that are defined on the table are also dropped. When a materialized query table is directly or indirectly dropped, the following items are also dropped:

• All privileges on the materialized query table
• All synonyms, views, and indexes that are defined on the materialized query table

Any alias that is defined on the materialized query table is not dropped. Any packages that are dependent on the dropped materialized query table are marked invalid.

You cannot use DROP TABLE to drop a clone table. You must use the ALTER TABLE statement with the DROP CLONE clause to drop a clone table. If a base table that is involved in a clone relationship is dropped, the associated clone table is also dropped. You cannot drop an auxiliary table for an object that is involved in a clone relationship.

The table cannot be dropped if it is defined as a history table for a system-period temporal table.

The table cannot be dropped if it is referenced in the definition of a row permission or a column mask.

To drop a system-period temporal table, the privilege set must also contain the authorization that is required to drop the history table. The history table is dropped when a system-period temporal table is dropped.

If a table with LOB columns is dropped, the auxiliary tables that are associated with the table and the indexes on the auxiliary tables are also dropped. Any LOB table spaces that were implicitly created for the auxiliary tables are also dropped.

If a table with XML columns is dropped, all implicitly created objects for all XML columns are also dropped.

If an empty auxiliary table is dropped, the definition of the base table is marked incomplete. If the base table space is implicitly created, the auxiliary table cannot be dropped.

If the table has a security label column, the primary authorization ID of the DROP statement must have a valid security label, and the RACF® SECLABEL class must be active.

If a table that uses hash organization is dropped, all catalog entries for the hash organization are cleaned up.

TABLESPACE database-name.table-space-name

Identifies the table space to drop. The name must identify a table space that exists at the current server. The database name must not be DSNDB01 or DSNDB06. Omission of the database name is an implicit specification of DSNDB04. table-space-name must not identify a table space that is implicitly created for an XML column.

Whenever a table space is directly or indirectly dropped, all the tables in the table space are also dropped. The name of a dropped table space cannot be reused until a commit operation is performed. Any pending changes to the definitions of the table space and its indexes are also dropped.

A LOB table space can be dropped only if it does not contain an auxiliary table. If the LOB table space is implicitly created, it cannot be dropped.

Whenever a base table space that contains tables with LOB columns is dropped, all the auxiliary tables and indexes on those auxiliary tables that are associated with the base table space are also dropped.

Whenever a base table space that contains tables with XML columns is dropped, all implicitly created objects for all XML columns are also dropped.

The table space cannot be dropped if it contains a history table or is associated with an accelerator-only table.

TRIGGER trigger-name

Identifies the trigger to drop. The name must identify a trigger that exists at the current server.

Whenever a trigger is directly or indirectly dropped, all privileges on the trigger are also dropped and the associated trigger package is freed. The name of that trigger package is the same as the trigger name and the collection ID is the schema name.

When an INSTEAD OF trigger is dropped, the associated privilege is revoked from anyone that possesses the privilege as a result of an implicit grant that occurred when the trigger is created.

Dropping triggers causes certain packages to be marked invalid. For example, if trigger-name specifies an INSTEAD OF trigger on a view V, another trigger might depend on trigger-name through an update to the view V, and that trigger package is invalidated.

If a trigger has current, previous, and original copies, the DROP statement will drop all copies.

TRUSTED CONTEXT context-name

Identifies the trusted context to drop. The context-name must identify a trusted context that exists at the current server. When a trusted context is dropped, all associations to attributes (IP addresses, job names) and associations to users of the trusted context are dropped. If the trusted context is dropped while trusted connections for the context are active, the connections remain active until they terminate or the next attempt at reuse is made.

TYPE distinct-type-name

Identifies the distinct type to drop. The name must identify a distinct type that exists at the current server. The default keyword RESTRICT indicates that the distinct type is not dropped if any of the following dependencies exist:

• The definition of a column of a table uses the distinct type.
• The definition of an input or result parameter of a user-defined function uses the distinct type.
• The definition of a parameter of a stored procedure uses the distinct type.
• The definition of an extended index uses a cast function that is implicitly generated for the distinct type.
• The definition of an SQL variable in a procedure or function uses the distinct type.
• The definition of a row permission or a column mask uses the distinct type.
• A sequence exists for which the data type of the sequence is the distinct type.
• One of the following dependencies exists on one of the cast functions that are generated for the distinct type:
  • Another function is sourced from one of the cast functions
  • A view uses one of the cast functions
  • A trigger package uses one of the cast functions
  • The definition of a materialized query table uses one of the cast functions

Whenever a distinct type is dropped, all privileges on the distinct type are also dropped. In addition, the cast functions that were generated when the distinct type was created and the privileges on those cast functions are also dropped.

VIEW view-name or alias-name

Identifies the view to drop. The name must identify a view that exists at the current server.

Whenever a view is directly or indirectly dropped, all privileges on the view and all synonyms and views that are defined on the view are also dropped. Whenever a view is directly or indirectly dropped, all materialized query tables defined on the view are also dropped.

If alias-name is specified, the actual view will be dropped as if view-name were specified. However, the alias is not dropped and can be dropped using the DROP ALIAS statement.

If the view is referenced in the definition of a row permission or a column mask, it cannot be dropped.

Notes

Restrictions on DROP:

DROP is subject to these restrictions:

• DROP DATABASE cannot be performed while a DB2 utility has control of any part of the database.
• DROP INDEX cannot be performed while a DB2 utility has control of the index or its associated table space.
• DROP INDEX cannot be performed if the index is a unique index that is defined on a ROWID column that is defined as GENERATED BY DEFAULT and there are pending changes to the definition of the table space or to any objects within the table space that are explicitly created.
• DROP INDEX cannot be performed if the index is an empty index on an auxiliary table that resides in an explicitly created LOB table space and there are pending changes to the definition of the base table space or to any objects within the base table space.
• DROP INDEX cannot be performed if the index is the hash overflow index for a table that uses hash organization.
• DROP TABLE cannot be performed while a DB2 utility has control of the table space that contains the table.
• DROP TABLE cannot be performed if the table space was explicitly created and there are pending changes to the definition of the table space.
• DROP TABLE cannot be performed if the table is an empty auxiliary table and there are any pending changes to the definition of the base table space or to any objects within the base table space.
• DROP TABLESPACE cannot be performed while a DB2 utility has control of the table space.

In a data sharing environment, the following restrictions also apply:

• If any member has an active resource limit specification table (RLST) you cannot drop the database or table space that contains the table, the table itself, or any index on the table.
• If the member executing the drop cannot access the DB2-managed data sets, only the catalog and directory entries for those data sets are removed.

Objects that have certain dependencies cannot be dropped. For information on these restrictions, see Table 3.

Recreating objects:

After an index or table space is dropped, a commit must be performed before the object can be re-created with the same name. If a table that was created without an IN clause (thereby causing a table space to be implicitly created) is dropped, a table cannot be re-created with the same name until a commit is performed.

Dropping a parent table:

DROP is not DELETE and therefore does not involve delete rules.

Dropping a default storage group:

If you drop the default storage group of a database, the database no longer has a legitimate default. You must then specify USING in any statement that creates a table space or index in the database. You must do this until you either:

• Create another storage group with the same name using the CREATE STOGROUP statement, or
• Designate another default storage group for the database using the ALTER DATABASE statement.

Dropping an accelerator-only table:

• When an accelerator-only table is dropped and the accelerator is not active, use the SYSACCEL_DROP_TABLE procedure to drop the table in the accelerator.
• A DROP TABLE statement that identifies an accelerator-only table should be issued in a separate unit of work from other SQL statements.

Dropping an implicitly created database:

When a table that resides in an implicitly created table space is dropped, the implicitly created table space and related objects are dropped. However, the implicitly created database is not dropped. This can result in a large number of empty databases in a system. These databases might be eventually reused for newly created implicit table spaces. These implicitly created databases can be dropped using DROP DATABASE.

Dropping a table space or index:

To drop a table space or index, the size of the buffer pool associated with the table space or index must not be zero.

Dropping a LOB table space:

If the base table space is explicitly created, both explicitly created LOB table spaces and implicitly created LOB table spaces can be dropped if it does not contain any auxiliary tables. If the LOB table space is implicitly created, it will be dropped automatically when the auxiliary table is dropped. If the LOB table space is explicitly created, it is not dropped when the auxiliary table is dropped, and can be explicitly dropped later.

If the base table space is implicitly created, the LOB table space cannot be dropped. If the LOB table space is explicitly created, it can be dropped when the auxiliary table is dropped. The following table shows the relationship between the base table space, the LOB table space, and the use of DROP for the LOB table space and base table space:

Table 1. Use of DROP for LOB table space

How base table was created	How LOB table space was created	Whether DROP can be used on LOB table space	State of LOB table space if base table space is dropped
Explicitly	Explicitly	Yes	LOB table space remains
Explicitly	Implicitly	Yes	LOB table space is dropped
Implicitly	Explicitly	Yes	LOB table space remains
Implicitly	Implicitly	No	N/A

Dropping a database when data sets for DB2 objects have already been deleted:

When some of the data sets for DB2 objects that associated with the database have already been deleted, DROP DATABASE will perform in the following manner:

For DB2-managed objects:

The DROP DATABASE statement will delete the underlying data sets if they exist. If the data sets do not exist, DROP DATABASE will delete only the catalog entries for those data sets.

For user-managed objects:

The DROP DATABASE statement will delete only the catalog entries for the data sets. The underlying data sets will need to be manually deleted after the DROP DATABASE statement is complete.

Dropping a table space in a work file database:

If one member of a data sharing group drops a table space in a work file database, or an entire work file database, that belongs to another member, DB2-managed data sets that the executing member cannot access are not dropped. However, the catalog and directory entries for those data sets are removed.

Dropping resource limit facility (governor) indexes, tables, and table spaces:

While the RLST is active, you cannot issue a DROP DATABASE, DROP INDEX, DROP TABLE, or DROP TABLESPACE statement for an object associated with an RLST that is active on any member of a data sharing group. See Resource limit facility implications for data sharing for details.

Dropping a temporary table:

To drop a created temporary table or a declared temporary table, use the DROP TABLE statement.

Dropping a materialized query table:

To drop a materialized query table, use the DROP TABLE statement.

Dropping an alias:

Dropping a table or view does not drop its aliases. However, if you use the DROP TABLE statement and specify an alias for a table or view, the table or view will be dropped. To drop an alias, use the DROP ALIAS statement.

Dropping a table from an implicitly created table space:

If you drop a table from an implicitly created table space, the following related objects are also dropped:

• The enforcing primary and unique key indexes
• Any LOB table spaces, auxiliary tables, and auxiliary indexes
• The ROWID index (if the ROWID column is defined as GENERATED BY DEFAULT)

If any LOB columns are defined on the table, the LOB table space is dropped if it was implicitly created. You can use the DROP statement to drop a LOB table space only if one of the following conditions it true:

• The base table space is explicitly created
• The base table space is implicitly created but the LOB table space is explicitly created

You cannot use the DROP statement to drop a LOB table space if both the base table space and the LOB table space are implicitly created.

Dropping an index on an auxiliary table and an auxiliary table:

You can explicitly drop an empty index on an auxiliary table with the DROP INDEX statement, unless the base table space is implicitly created. An empty or populated index on an auxiliary table is implicitly dropped when:

• The auxiliary table is empty and it is explicitly dropped (empty indexes only).
• The associated base table for the auxiliary table is dropped.
• The base table space that contains the associated base table is dropped.

You can explicitly drop an empty auxiliary table with the DROP TABLE statement, unless the base table space is implicitly created. An empty or populated auxiliary table is implicitly dropped when:

• The associated base table for the auxiliary table is dropped.
• The base table space that contains the associated base table is dropped.

The following table shows which DROP statements implicitly or explicitly cause an auxiliary table and the index on that table to be dropped, as indicated by the 'D' in the column.

Table 2. Effect of various DROP statements on auxiliary tables and indexes that are in explicitly created table spaces

Statement	Auxiliary table		Index on auxiliary table
	Populated	Empty	Populated	Empty
DROP TABLESPACE (base table space)	D	D	D	D
DROP TABLE (base table)	D	D	D	D
DROP TABLE (auxiliary table)		D		D
DROP INDEX (index on auxiliary table)				D
Note: D indicates that the table or index is dropped.

Dropping a migrated index or table space:

Here, "migration" means migrated by the Hierarchical Storage Manager (DFSMShsm). DB2 does not wait for any recall of the migrated data sets. Hence, recall is not a factor in the time it takes to execute the statement.

Dropping a trusted context:

The drop of a trusted context takes effect after the DROP TRUSTED CONTEXT statement is committed. If the DROP TRUSTED CONTEXT statement results in an error or is rolled back, the trusted context is not dropped.

Avoiding DROP failure due to excessive locking

Dropping a table space, database, or index with the COPY YES attribute deletes all corresponding records in the SYSCOPY and SYSLGRNX catalog statistics tables. The DROP fails if the lock structure size cannot accommodate the number of locks obtained during DROP processing. DROP failure is more likely if the SYSCOPY, SYSLGRNX, or other catalog statistics tables contain many entries, especially if the object you are dropping was created long ago or contains many partitions. DROP failure is also more likely if objects are copied frequently while the MODIFY RECOVERY and MODIFY STATISTICS utilities are run relatively infrequently.

To avoid DROP failure, run the MODIFY RECOVERY and MODIFY STATISTICS utilities on objects before dropping them. If you drop a clone table, you need to specify the CLONE keyword to delete recovery and statistics information for the clone objects from the catalog and directory. You can Specify AGE(*) or DATE(*) to remove all recovery and statistics information regardless of past update, copy, or cleanup frequency. Be aware that running the MODIFY utility with AGE(*) or DATE(*) will leave objects unrecoverable after they are dropped unless you make a copy or other form of back-up first.

Also, ensure that your applications commit drops frequently, especially for databases containing multiple table spaces, and table spaces containing multiple tables. You can also increase the size of your lock structures to accommodate the surge in lock requests during this type of activity.

Invalidation of packages and dynamic cached statements after dropping row permissions or column masks:

If row or column access control is currently enforced for the table, dropping the row permission or the column mask invalidates all packages and dynamic cached statements that reference the table. Otherwise no package or dynamic cached statement is invalidated.

Dependencies when dropping objects:

Whenever an object is directly or indirectly dropped, other objects that depend on the dropped object might also be dropped. (The catalog stores information about the dependencies of objects on each other.) The following semantics determine what happens to a dependent object when the object that it depends on (the underlying object) is dropped:

Cascade (D)

Dropping the underlying object causes the dependent object to be dropped. However, if the dependent object cannot be dropped because it has a restrict dependency on another object, the drop of the underlying object fails.

Restrict (D)

The underlying object cannot be dropped if a dependent object exists.

Inoperative (O)

Dropping the underlying object causes the dependent object to become inoperative.

Invalidation (V)

Dropping the underlying object causes the dependent object to become invalidated.

For objects that directly depend on others, the following table uses the letter abbreviations above to summarize what happens to a dependent object when its underlying object is specified in a DROP statement. Additional objects can be indirectly affected, too.

To determine the indirect effects of a DROP statement, assess what happens to the dependent object and whether the dependent object has objects that depend on it. For example, assume that view B is defined on table A and view C is defined on view B. In the following table, the 'D' in the VIEW column of the DROP TABLE row indicates that view B is dropped when table A is dropped. Next, because view C is dependent on view B, check the VIEW column for DROP VIEW. The 'D' in the column indicates that view C will be dropped, too.

The letters in the following table have the following meanings:

D

Dependent object is dropped.

O

Dependent object is made inoperative.

V

Dependent object is invalidated.

R

DROP statement fails.

Table 3. Effect of dropping objects that have dependencies

DROP statement	Type of object
	Alias	Database	Function	Index	Package 1	Procedure	Sequence	Stogroup	Synonym	Table	Table space	Trigger	Type	View
DROP ALIAS					V
DROP DATABASE				D2						D	D			D
DROP FUNCTION			R5 17 18		O	R17 18						R		R
DROP INDEX2,6					V							V
DROP PACKAGE7
DROP PROCEDURE			R 17 18		O	R17 18						R
DROP ROLE					V
DROP SEQUENCE			R13		V							R
DROP STOGROUP				R8							R8
DROP SYNONYM
DROP TABLE9,10				D	V				D			D11		D
DROP TABLESPACE12			D	D	V					D
DROP TRIGGER					V16
DROP TYPE			R3	R14		R4	R			R
DROP VIEW					V				D			D15		D
Notes: The PACKAGE column represents packages for user-defined functions, procedures, and triggers, as well as other packages. The PACKAGE column also applies for plans. The index space associated with the index is dropped. If a function is dependent on the distinct type being dropped, the distinct type cannot be dropped unless the function is one of the cast functions that was created for the distinct type. If the definition of a parameter of a stored procedure uses the distinct type, the distinct type cannot be dropped. If other user-defined functions are sourced on the user-defined function being dropped, the function cannot be dropped. An index on an auxiliary table cannot be explicitly dropped. A trigger package cannot be explicitly dropped with DROP PACKAGE. A trigger package is implicitly dropped when the associated trigger or subject table is dropped. A storage group cannot be dropped if it is used by any table space or index space. An auxiliary table cannot be explicitly dropped with DROP TABLE. An auxiliary table is implicitly dropped when the associated base table is dropped. If an implicit table space was created when the table was created, the table space is also dropped. When a subject table is dropped, any associated triggers and related trigger packages are also dropped. A LOB table space cannot be dropped until the base table with the LOB columns is dropped. This restriction is only for SQL functions. The index in this case must be an expression-based index. When a subject view is dropped, any associated triggers and related trigger packages are also dropped. Any packages that have a dependency on an INSTEAD OF trigger will be marked invalid. A routine that is referenced by a non-inline SQL scalar function cannot be dropped. A routine that is referenced by a native SQL procedure cannot be dropped. Only dependent table functions are dropped.

Alternative syntax and synonyms:

To provide compatibility with previous releases of DB2 or other products in the DB2 family, DB2 supports the following keywords:

• DATA TYPE or DISTINCT TYPE as a synonym for TYPE
• PROGRAM as a synonym for PACKAGE