DROP statement

Invocation for DROP

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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for DROP

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
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

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
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

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

Storage group:

• Ownership of the object
• SYSADM or SYSCTRL authority
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

View:

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

Alias for a table or view:

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

Alias for a sequence:

• Ownership of the object
• The DROPIN privilege on the schema
• 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

User-defined type, global variable, sequence, stored procedure, trigger, or user-defined function:

• Ownership of the object ¹
• The DROPIN privilege on the schema
• SYSADM or SYSCTRL authority
• System DBADM
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

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 for DROP

alias-designator

PUBLIC

Specifies that the alias to be dropped is a public alias. The alias-name must identify an alias that exists in the SYSPUBLIC schema.

If the PUBLIC keyword is specified, alias-name must identify a public alias that exists at the current server.

ALIAS alias-name

Identifies the alias to be dropped. The alias-name must identify an alias that exists at the current server.

Dropping an alias for a table or view 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.

Dropping an alias for a sequence has no effect on any view or materialized query table that was defined using the alias. If the alias is referenced in the definition of an inline SQL function, it cannot be dropped. When an alias for a sequence is dropped, all packages that refer to the sequence alias are invalidated.

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

FOR TABLE

Specifies that the alias to be dropped is for a table or view. Dropping an alias for a table has no effect on any view, materialized query table, or synonym that was defined using the alias.

FOR SEQUENCE

Specifies that the alias to be dropped is for a sequence. Dropping an alias for a sequence has no effect on any view, or materialized query table that was defined using the alias.

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.

You can drop a database that contains an archive table only if the database also contains the associated archive-enabled table. You can drop a database that contains an archive-enabled table when the associated archive table is contained in another database. In this case, the action cascades to drop the archive 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. The specified function definition is dropped from the schema.

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. All package copies are also marked as VALID='NO'. If the function is a compiled SQL scalar function, the package associated with the function is also dropped.

All versions of a compiled SQL scalar function are dropped. To drop a specific version of a compiled SQL scalar function, use an ALTER FUNCTION statement (compiled SQL scalar function) statement with the DROP VERSION clause.

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 statement.

If the index is used for the foreign key of a temporal referential constraint, the referential constraint must be dropped before the index can be dropped.

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 Creating a package version.

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 was defined with the CREATE PROCEDURE statement at the current server. The specified procedure definition is dropped from the schema. All privileges on the procedure are also dropped, and any packages that are dependent on the procedure are marked invalid. If the procedure is an SQL procedure, the package that is associated with the procedure is also dropped.

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

• A trigger definition contains a CALL statement that identifies the procedure.
• An SQL routine definition contains a CALL statement that identifies the procedure.

All versions of a native SQL procedure are dropped. To drop a specific version of a native SQL procedure, use an ALTER PROCEDURE statement (SQL - native procedure) statement with the DROP VERSION clause.

Use a DROP PACKAGE statement to drop a package for a version of a native SQL 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:

  Object	Object
  Alias Array type Column mask Database Distinct type Global variable 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 invalidates dependent packages and the dynamic statement cache. Dropping a synonym has no effect on any view, materialized query table, or alias that was defined using the synonym.

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 any of the following types of tables:

• A catalog table
• A directory table
• FL 506 A table in a partitioned (non-UTS) table space
• A table that is implicitly created for an XML column
• A populated auxiliary table

A table in a partitioned (non-UTS) 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

FL 506 If the table space for the table is a universal table space, a LOB table space, or 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. FL 506 Db2 also drops the LOB table spaces that contain the auxiliary tables, regardless of whether the LOB table spaces were implicitly or explicitly created.

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.

If an archive-enabled table is dropped, the archive table and any indexes that are defined on the archive table are also dropped. To drop an archive-enabled table, the privilege set must also contain the authorization that is required to drop the archive table. An archive table cannot be explicitly dropped by using the DROP statement.

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, an archive 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.

For an advanced trigger, all versions of the trigger are dropped. Use an ALTER TRIGGER statement with the DROP VERSION clause to drop a specific version of a trigger.

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 type-name

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

• The definition of a column of a table uses the user-defined type.
• The definition of an input or result parameter of a user-defined function uses the user-defined type.
• The definition of a parameter of a stored procedure uses the user-defined type.
• The definition of an extended index uses a cast function that is implicitly generated for the user-defined type.
• The definition of an SQL variable in a procedure or function uses the user-defined type.
• The definition of a row permission or a column mask uses the user-defined type.
• A sequence exists for which the data type of the sequence is the user-defined type.
• One of the following dependencies exists on one of the cast functions that are generated for the user-defined 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 user-defined type is dropped, all privileges on the distinct type are also dropped. In addition, the cast functions that were generated when the user-defined type was created and the privileges on those cast functions are also dropped.

VARIABLE variable-name

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

• The definition of a function, trigger, or view is dependent on the global variable

Packages that are dependent on the global variable are marked invalid when the global variable is dropped. If a statement that is in the dynamic statement cache depends on the global variable and the global variable is dropped, the statement in the dynamic statement cache will be invalidated if it is not in use.

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 for DROP

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 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 2.

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:

FL 506 When an auxiliary table that resides in a LOB table space is dropped, the LOB table space is automatically dropped. An explicitly created LOB table space can be dropped if it does not contain an auxiliary table.

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)

FL 506 If any LOB columns are defined on the table, the LOB table space is dropped.

Dropping an index on a base table and 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 1. 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:

This statement might invalidate all packages that depend on target objects, and sometimes other related objects through cascading effects, depending on the clauses and keywords specified and other factors. For more information, see Changes that invalidate packages.

Dependencies when dropping objects:

If other objects depend on the object specified in a DROP statement, the dependent objects might also be dropped, invalidated, or become inoperative, or the DROP statement might fail. These effects also cascade to any objects that depend on the dropped dependent objects. To determine the full indirect effects of a DROP statement, check what happens for any dependent objects, check whether each dependent object has its own dependent objects, and the check the rules for those object types.

The following table indicates the result of DROP statements based on the type of object specified by the DROP statement and the type of dependent object. The letters have the following meanings:

D (cascaded drop)

If any dependent objects of the indicated types exist, they are also dropped. The effect cascades to any objects that depend on the dropped dependent objects.

R (restrict)

If any dependent objects of the indicated types exist, the DROP statement fails. Also, if any dependent object cannot be dropped because of a restrict dependency for its own dependent objects, the DROP statement fails.

V (invalidate)

If dependent packages exist, they become invalidated.

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 also be dropped.

Table 2. Effect of dropping objects that have dependencies

DROP statement	Dependent object type
	Alias	Column mask	Function	Global variable	Index	Procedure	Row permission	Sequence	Stogroup	Synonym	Table	Table space	Trigger	Type	View
DROP ALIAS		R	V			V	R						V
DROP FUNCTION			R 4			R 12							R		R
DROP INDEX 1			V			V							V
DROP PROCEDURE			R 12			R 12							R
DROP ROLE	R	R	R	R	R	R	R	R	R	R	R	R	R	R	R
DROP SEQUENCE			R8			R 12							R
DROP STOGROUP					R5							R5
DROP SYNONYM		R
DROP TABLE 6		R 16	D 14		D	V	R 16			D	D 15		D 7		D
DROP TABLESPACE			V		D	V					D		V
DROP TRIGGER			V			V							V
DROP TYPE		R	R 2		R 9	R 3	R	R			R		V11
DROP VARIABLE			R			V							R		R
DROP VIEW		R	D 14			V	R			D	D 15		D 10		D

Table notes:

1. The index space associated with the index is dropped.
2. If a function is dependent on the user-defined type being dropped, the user-defined type cannot be dropped unless the function is one of the cast functions that was created for the user-defined type.
3. If the definition of a parameter of a stored procedure uses the user-defined type, the user-defined type cannot be dropped.
4. If other user-defined functions are sourced on the user-defined function being dropped, the function cannot be dropped.
5. A storage group cannot be dropped if it is used by any table space or index space.
6. FL 506 If the table resides in an explicitly created universal table space or any implicitly created table space, the table space is also dropped.
7. When a subject table is dropped, any associated triggers and related trigger packages are also dropped.
8. This restriction is only for SQL functions.
9. The index in this case must be an expression-based index.
10. When a subject view is dropped, any associated triggers and related trigger packages are also dropped.
11. Any packages that have a dependency on an INSTEAD OF trigger will be marked invalid.
12. A routine or sequence that is referenced by a native SQL procedure cannot be dropped.
13. An alias or synonym cannot be dropped if there is a dependent inline SQL table function.
14. When a table or view is dropped, any dependent inline SQL table functions are also dropped.
15. When a table or view is dropped, any dependent materialized query tables are also dropped.
16. When a table is dropped, any row permission or column masks defined directly on the table are also dropped. If any row permissions or column masks reference the table in the body, the table cannot be 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
• DROP ALIAS SYSPUBLIC.name can be specified as an alternative to DROP PUBLIC ALIAS SYSPUBLIC.name