DROP statement

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

When dropping a table or view hierarchy, the privileges held by the authorization ID of the statement must include one of the previously mentioned privileges for each of the tables or views in the hierarchy.

When dropping an audit policy, the privileges held by the authorization ID of the statement must include SECADM authority.

When dropping a buffer pool, database partition group, storage group, or table space, the privileges held by the authorization ID of the statement must include SYSADM or SYSCTRL authority.

When dropping a data type mapping, function mapping, server definition, or wrapper, the privileges held by the authorization ID of the statement must include DBADM authority.

When dropping an event monitor the privilege held by the authorization ID of the statement must include SQLADM or DBADM authority.

When dropping a role, the privileges held by the authorization ID of the statement must include SECADM authority.

When dropping a row permission or a column mask, the privileges held by the authorization ID of the statement must include SECADM authority.

When dropping a schema, the privileges held by the authorization ID of the statement must include DBADM authority, or be the schema owner, as recorded in the OWNER column of the SYSCAT.SCHEMATA catalog view.

When dropping a security label, a security label component, or a security policy, the privileges held by the authorization ID of the statement must include SECADM authority.

When dropping a service class, work action set, work class set, workload, threshold, or histogram template, the privileges held by the authorization ID of the statement must include WLMADM or DBADM authority.

When dropping a transform, the privileges held by the authorization ID of the statement must include DBADM authority, or must be the owner of type-name.

When dropping a trusted context, the privileges held by the authorization ID of the statement must include SECADM authority.

When dropping an event monitor or usage list the privilege held by the authorization ID of the statement must include SQLADM or DBADM authority.

When dropping a user mapping, the privileges held by the authorization ID of the statement must include DBADM authority, if this authorization ID is different from the federated database authorization name within the mapping. Otherwise, if the authorization ID and the authorization name match, no authorities or privileges are required.

Syntax

>>-DROP--------------------------------------------------------->

>--+-| alias-designator |------------------------------------------+-><
   +-AUDIT POLICY--policy-name-------------------------------------+   
   +-BUFFERPOOL--bufferpool-name-----------------------------------+   
   +-DATABASE PARTITION GROUP--db-partition-group-name-------------+   
   +-EVENT MONITOR--event-monitor-name-----------------------------+   
   +-| function-designator |--+----------+-------------------------+   
   |                          '-RESTRICT-'                         |   
   +-FUNCTION MAPPING--function-mapping-name-----------------------+   
   +-HISTOGRAM TEMPLATE--template-name-----------------------------+   
   |                   (1)                                         |   
   +-INDEX--index-name---------------------------------------------+   
   +-INDEX EXTENSION--index-extension-name--RESTRICT---------------+   
   +-MASK--mask-name-----------------------------------------------+   
   +-| method-designator |--+----------+---------------------------+   
   |                        '-RESTRICT-'                           |   
   +-MODULE--module-name-------------------------------------------+   
   +-NICKNAME--nickname--------------------------------------------+   
   +-PACKAGE--package-name--+-------------------------+------------+   
   |                        | .-VERSION-.             |            |   
   |                        '-+---------+--version-id-'            |   
   +-PERMISSION--permission-name-----------------------------------+   
   +-| procedure-designator |--+----------+------------------------+   
   |                           '-RESTRICT-'                        |   
   +-ROLE--role-name-----------------------------------------------+   
   +-SCHEMA--schema-name--RESTRICT---------------------------------+   
   |                                      .-RESTRICT-.             |   
   +-SECURITY LABEL--security-label-name--+----------+-------------+   
   |                                                .-RESTRICT-.   |   
   +-SECURITY LABEL COMPONENT--sec-label-comp-name--+----------+---+   
   |                                        .-RESTRICT-.           |   
   +-SECURITY POLICY--security-policy-name--+----------+-----------+   
   +-SEQUENCE--sequence-name--+----------+-------------------------+   
   |                          '-RESTRICT-'                         |   
   +-SERVER--server-name-------------------------------------------+   
   |                               .-RESTRICT-.                    |   
   +-| service-class-designator |--+----------+--------------------+   
   |                              .-RESTRICT-.                     |   
   +-STOGROUP--storagegroup-name--+----------+---------------------+   
   +-TABLE--table-name---------------------------------------------+   
   +-TABLE HIERARCHY--root-table-name------------------------------+   
   |                  .-,---------------.                          |   
   |                  V                 |                          |   
   +-+-TABLESPACE--+----tablespace-name-+--------------------------+   
   | '-TABLESPACES-'                                               |   
   +-+-TRANSFORM--+--+-ALL--------+--FOR--type-name----------------+   
   | '-TRANSFORMS-'  '-group-name-'                                |   
   +-THRESHOLD--threshold-name-------------------------------------+   
   +-TRIGGER--trigger-name-----------------------------------------+   
   +-TRUSTED CONTEXT--context-name---------------------------------+   
   +-TYPE--type-name--+----------+---------------------------------+   
   |                  '-RESTRICT-'                                 |   
   +-TYPE MAPPING--type-mapping-name-------------------------------+   
   +-USAGE LIST--usage-list-name-----------------------------------+   
   +-USER MAPPING FOR--+-authorization-name-+--SERVER--server-name-+   
   |                   '-USER---------------'                      |   
   +-VARIABLE--variable-name--+----------+-------------------------+   
   |                          '-RESTRICT-'                         |   
   +-VIEW--view-name-----------------------------------------------+   
   +-VIEW HIERARCHY--root-view-name--------------------------------+   
   +-WORK ACTION SET--work-action-set-name-------------------------+   
   +-WORK CLASS SET--work-class-set-name---------------------------+   
   +-WORKLOAD--workload-name---------------------------------------+   
   +-WRAPPER--wrapper-name-----------------------------------------+   
   '-XSROBJECT--xsrobject-name-------------------------------------'   

alias-designator

                                  .-FOR TABLE----.   
|--+--------+--ALIAS--alias-name--+--------------+--------------|
   '-PUBLIC-'                     +-FOR MODULE---+   
                                  '-FOR SEQUENCE-'   

function-designator

|--+-FUNCTION--function-name--+-------------------------+-+-----|
   |                          '-(--+---------------+--)-' |   
   |                               | .-,---------. |      |   
   |                               | V           | |      |   
   |                               '---data-type-+-'      |   
   '-SPECIFIC FUNCTION--specific-name---------------------'   

method-designator

|--+-METHOD--method-name--+-------------------------+--FOR--type-name-+--|
   |                      '-(--+---------------+--)-'                 |   
   |                           | .-,---------. |                      |   
   |                           | V           | |                      |   
   |                           '---data-type-+-'                      |   
   '-SPECIFIC METHOD--specific-name-----------------------------------'   

procedure-designator

|--+-PROCEDURE--procedure-name--+-------------------------+-+---|
   |                            '-(--+---------------+--)-' |   
   |                                 | .-,---------. |      |   
   |                                 | V           | |      |   
   |                                 '---data-type-+-'      |   
   '-SPECIFIC PROCEDURE--specific-name----------------------'   

service-class-designator

|--SERVICE CLASS--service-class-name--+--------------------------------+--|
                                      '-UNDER--service-superclass-name-'   

1. Index-name can be the name of either an index or an index specification.

Description

alias-designator

ALIAS alias-name

Identifies the alias that is to be dropped. The alias-name must identify an alias that is described in the catalog (SQLSTATE 42704). The specified alias is deleted.

FOR TABLE, FOR MODULE, or FOR SEQUENCE

Specifies the object type for the alias.

FOR TABLE

The alias is for a table, view, or nickname.

FOR MODULE

The alias is for a module.

FOR SEQUENCE

The alias is for a sequence.

All views and triggers that reference the alias are made inoperative. This includes alias references in both the ON clause of the CREATE TRIGGER statement and within the triggered SQL statements. Any materialized query table or staging table that references the alias is dropped.

If PUBLIC is specified, the alias-name must identify a public alias (SQLSTATE 428EK) that exists at the current server (SQLSTATE 42704).

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

AUDIT POLICY policy-name

Identifies the audit policy that is to be dropped. The policy-name must identify an audit policy that exists at the current server (SQLSTATE 42704). The audit policy must not be associated with any database objects (SQLSTATE 42893). The specified audit policy is deleted from the catalog.

BUFFERPOOL bufferpool-name

Identifies the buffer pool that is to be dropped. The bufferpool-name must identify a buffer pool that is described in the catalog (SQLSTATE 42704). There can be no table spaces assigned to the buffer pool (SQLSTATE 42893). The IBMDEFAULTBP buffer pool cannot be dropped (SQLSTATE 42832).

Buffer pool memory is released immediately, to be used by DB2®. Disk storage may not be released until the next connection to the database.

DATABASE PARTITION GROUP db-partition-group-name

Identifies the database partition group that is to be dropped. The db-partition-group-name parameter must identify a database partition group that is described in the catalog (SQLSTATE 42704). This is a one-part name.

Dropping a database partition group drops all table spaces defined in the database partition group. All existing database objects with dependencies on the tables in the table spaces (such as packages, referential constraints, and so on) are dropped or invalidated (as appropriate), and dependent views and triggers are made inoperative.

IBMCATGROUP, IBMDEFAULTGROUP, and IBMTEMPGROUP database partition groups cannot be dropped (SQLSTATE 42832).

If a DROP DATABASE PARTITION GROUP statement is issued against a database partition group that is currently undergoing a data redistribution, the drop database partition group operation fails, and an error is returned (SQLSTATE 55038). However, a partially redistributed database partition group can be dropped. A database partition group can become partially redistributed if a REDISTRIBUTE DATABASE PARTITION GROUP command does not execute to completion. This can happen if it is interrupted by either an error or a FORCE APPLICATION ALL command. (For a partially redistributed database partition group, the REDISTRIBUTE_PMAP_ID in the SYSCAT.DBPARTITIONGROUPS catalog is not -1.)

EVENT MONITOR event-monitor-name

Identifies the event monitor that is to be dropped. The event-monitor-name must identify an event monitor that is described in the catalog (SQLSTATE 42704).

If the identified event monitor is active, an error is returned (SQLSTATE 55034); otherwise, the event monitor is deleted. Note that if an event monitor has been previously activated using the SET EVENT MONITOR STATE statement, and the database has been deactivated and subsequently reactivated, use the SET EVENT MONITOR STATE statement to deactivate the event monitor before issuing the DROP statement.

If there are event files in the target path of a WRITE TO FILE event monitor that is being dropped, the event files are not deleted. However, if a new event monitor that specifies the same target path is created, the event files are deleted.

When dropping WRITE TO TABLE event monitors, table information is removed from the SYSCAT.EVENTTABLES catalog view, but the tables themselves are not dropped.

function-designator

Identifies an instance of a user-defined function (either a complete function or a function template) that is to be dropped. For more information, see Function, method, and procedure designators.

The function instance specified must be a user-defined function described in the catalog. The following functions cannot be dropped:

• A function implicitly generated by a CREATE TYPE statement (SQLSTATE 42917)
• A function that is in the SYSIBM, SYSFUN, SYSIBMADM, or the SYSPROC schema (SQLSTATE 42832)
• A function that is referenced in the definition of a row permission or a column mask (SQLSTATE 42893)
• A function that is referenced in a generated column expression or a check constraint (SQLSTATE 42893)

RESTRICT

The RESTRICT keyword enforces the rule that the function is not to be dropped if any of the following dependencies exists:

• Another function is sourced on the function.
• Another routine uses the function.
• A view uses the function.
• A trigger uses the function.
• A materialized query table uses the function in its definition.

The restrict rule is enforced by default for the same dependencies as in version 9.5 if the auto_reval database configuration parameter is set to disabled.

In this case, the following considerations apply:

• Other objects can be dependent upon a function. All such dependencies must be removed before the function can be dropped, with the exception of packages which are marked inoperative. An attempt to drop a function with such dependencies will result in an error (SQLSTATE 42893). See the "Rules" section for a list of these dependencies. If the function can be dropped, it is dropped.
• Any package dependent on the specific function being dropped is marked as inoperative. Such a package is not implicitly rebound. It must either be rebound by use of the BIND or REBIND command, or it must be re-prepared by use of the PREP command.

FUNCTION MAPPING function-mapping-name

Identifies the function mapping that is to be dropped. The function-mapping-name must identify a user-defined function mapping that is described in the catalog (SQLSTATE 42704). The function mapping is deleted from the database.

Default function mappings cannot be dropped, but can be disabled by using the CREATE FUNCTION MAPPING statement. Dropping a user-defined function mapping that was created to override a default function mapping reinstates the default function mapping.

Packages having a dependency on a dropped function mapping are invalidated.

HISTOGRAM TEMPLATE template-name

Identifies the histogram template that is to be dropped. The template-name must identify a histogram template that exists at the current server (SQLSTATE 42704). The template-name cannot be SYSDEFAULTHISTOGRAM (SQLSTATE 42832). The histogram template cannot be dropped if a service class or a work action is dependent on it (SQLSTATE 42893). The specified histogram template is deleted from the catalog.

INDEX index-name

Identifies the index or index specification that is to be dropped. The index-name must identify an index or index specification that is described in the catalog (SQLSTATE 42704). It cannot be an index that is required by the system for a primary key or unique constraint, for a replicated materialized query table, or for an XML column (SQLSTATE 42917). The specified index or index specification is deleted.

Packages having a dependency on a dropped index or index specification are invalidated.

INDEX EXTENSION index-extension-name RESTRICT

Identifies the index extension that is to be dropped. The index-extension-name must identify an index extension that is described in the catalog (SQLSTATE 42704). The RESTRICT keyword enforces the rule that no index can be defined that depends on this index extension definition (SQLSTATE 42893).

MASK mask-name

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

method-designator

Identifies a method body that is to be dropped. For more information, see Function, method, and procedure designators. The method body specified must be a method described in the catalog (SQLSTATE 42704). Method bodies that are implicitly generated by the CREATE TYPE statement cannot be dropped.

DROP METHOD deletes the body of a method, but the method specification (signature) remains as a part of the definition of the subject type. After dropping the body of a method, the method specification can be removed from the subject type definition by ALTER TYPE DROP METHOD.

RESTRICT

The RESTRICT keyword enforces the rule that the method is not to be dropped if any of the following dependencies exists:

• A function is sourced on the method.
• Another routine uses the method.
• A view uses the method.
• A trigger uses the method.
• A materialized query table uses the method in its definition.

The restrict rule is enforced by default for the same dependencies as in version 9.5 if the auto_reval database configuration parameter is set to disabled.

In this case, the following considerations apply:

• Other objects can be dependent upon a method. All such dependencies must be removed before the method can be dropped, with the exception of packages which will be marked inoperative if the drop is successful. An attempt to drop a method with such dependencies will result in an error (SQLSTATE 42893). If the method can be dropped, it will be dropped.
• Any package dependent on the specific method being dropped is marked as inoperative. Such a package is not implicitly re-bound. Either it must be re-bound by use of the BIND or REBIND command, or it must be re-prepared by use of the PREP command.

If the specific method being dropped overrides another method, all packages dependent on the overridden method - and on methods that override this method in supertypes of the specific method being dropped - are invalidated.

MODULE module-name

Identifies the module that is to be dropped. The module-name must identify a module that exists at the current server (SQLSTATE 42704). The specified name must not be an alias for a module (SQLSTATE 560CT). The specified module is dropped from the schema, including all module objects. All privileges on the module are also dropped.

If the module is referenced in the definition of a row permission or a column mask, the module cannot be dropped (SQLSTATE 42893).

NICKNAME nickname

Identifies the nickname that is to be dropped. The nickname must be listed in the catalog (SQLSTATE 42704). The nickname is deleted from the database.

All information about the columns and indexes associated with the nickname is deleted from the catalog. Any materialized query tables that are dependent on the nickname are dropped. Any index specifications that are dependent on the nickname are dropped. Any views that are dependent on the nickname are marked inoperative. Any packages that are dependent on the dropped index specifications or inoperative views are invalidated. The data source table that the nickname references is not affected.

If an SQL function or method is dependent on a nickname, that nickname cannot be dropped (SQLSTATE 42893).

PACKAGE package-name

Identifies the package that is to be dropped. The package name must identify a package that is described in the catalog (SQLSTATE 42704). The specified package is deleted. If the package being dropped is the only package identified by package-name (that is, there are no other versions), all privileges on the package are also deleted.

VERSION version-id

Identifies which package version is to be dropped. If a value is not specified, the version defaults to the empty string. If multiple packages with the same package name but different versions exist, only one package version can be dropped in one invocation of the DROP statement. Delimit the version identifier with double quotation marks when it:

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

If the statement is invoked from an operating system command prompt, precede each double quotation mark delimiter with a back slash character to ensure that the operating system does not strip the delimiters.

PERMISSION permission-name

Identifies the row permission to drop. The name must identify a row permission that exists at the current server (SQLSTATE 42704). The name must not identify the default row permission that was created implicitly by the database manager (SQLSTATE 42917).

procedure-designator

Identifies an instance of a procedure that is to be dropped. For more information, see Function, method, and procedure designators. The procedure instance specified must be a procedure described in the catalog. It is not possible to drop a procedure that is in the SYSIBM, SYSFUN, SYSIBMADM, or the SYSPROC schema (SQLSTATE 42832).

RESTRICT

The RESTRICT keyword prevents the procedure from being dropped if a trigger definition or an SQL routine definition contains a CALL identifying the procedure.

The restrict rule is enforced by default for the same dependencies as in version 9.5 if the following conditions are met:

• The auto_reval database configuration parameter is set to disabled
• An inlined trigger definition, inlined SQL function definition, or inlined SQL method definition contains a CALL statement identifying the procedure

It is not possible to drop a procedure that is in the SYSIBM, SYSFUN, or the SYSPROC schema (SQLSTATE 42832).

ROLE role-name

Identifies the role that is to be dropped. The role-name must identify a role that already exists at the current server (SQLSTATE 42704). The role-name must not identify a role, or a role that contains role-name, if the role has either EXECUTE privilege on a routine or USAGE privilege on a sequence, and an SQL object other than a package is dependent on the routine or sequence (SQLSTATE 42893). The owner of the SQL object is either authorization-name or any user who is a member of authorization-name, where authorization-name is a role.

A DROP ROLE statement fails (SQLSTATE 42893) if any of the following conditions are true for the role to be dropped:

• A workload exists such that one of the values for the connection attribute SESSION_USER ROLE is role-name
• A trusted context using role-name exists

The specified role is deleted from the catalog.

SCHEMA schema-name RESTRICT

Identifies the particular schema to be dropped. The schema-name must identify a schema that is described in the catalog (SQLSTATE 42704). The RESTRICT keyword enforces the rule that no objects can be defined in the specified schema for the schema to be deleted from the database (SQLSTATE 42893).

SECURITY LABEL security-label-name

Identifies the security label to be dropped. The name must be qualified with a security policy (SQLSTATE 42704) and must identify a security label that exists at the current server (SQLSTATE 42704).

RESTRICT

This option, which is the default, prevents the security label from being dropped if any of the following dependencies exist (SQLSTATE 42893):

• One or more authorization IDs currently hold the security label for read access
• One or more authorization IDs currently hold the security label for write access
• The security label is currently being used to protect one or more columns

SECURITY LABEL COMPONENT sec-label-comp-name

Identifies the security label component to be dropped. The sec-label-comp-name must identify a security label component that is described in the catalog (SQLSTATE 42704).

RESTRICT

This option, which is the default, prevents the security label component from being dropped if any of the following dependencies exist (SQLSTATE 42893):

• One or more security policies that include the security label component are currently defined

SECURITY POLICY security-policy-name

Identifies the security policy to be dropped. The security-policy-name must identify a security policy that exists at the current server (SQLSTATE 42704).

RESTRICT

This option, which is the default, prevents the security policy from being dropped if any of the following dependencies exist (SQLSTATE 42893):

• One or more tables are associated with this security policy
• One or more authorization IDs hold an exemption on one of the rules in this security policy
• One or more security labels are defined for this security policy

SEQUENCE sequence-name

Identifies the particular sequence that is to be dropped. The sequence-name, along with the implicit or explicit schema name, must identify an existing sequence at the current server. If no sequence by this name exists in the explicitly or implicitly specified schema, an error is returned (SQLSTATE 42704).

RESTRICT

The RESTRICT keyword prevents the sequence from being dropped if any of the following dependencies exist:

• A trigger exists such that a NEXT VALUE or PREVIOUS VALUE expression in the trigger body specifies the sequence (SQLSTATE 42893).
• An SQL routine exists such that a NEXT VALUE expression in the routine body specifies the sequence (SQLSTATE 42893).

The restrict rule is enforced by default for the same dependencies as in version 9.5 if the following conditions are met:

• The auto_reval database configuration parameter is set to disabled
• An inlined trigger definition, inlined SQL function definition, or inlined SQL method definition references the sequence

SERVER server-name

Identifies the data source whose definition is to be dropped from the catalog. The server-name must identify a data source that is described in the catalog (SQLSTATE 42704). The definition of the data source is deleted.

All nicknames for tables and views residing at the data source are dropped. Any index specifications dependent on these nicknames are dropped. Any user-defined function mappings, user-defined type mappings, and user mappings that are dependent on the dropped server definition are also dropped. All packages dependent on the dropped server definition, function mappings, nicknames, and index specifications are invalidated. All federated procedures that are dependent on the server definition are also dropped.

service-class-designator

SERVICE CLASS service-class-name

Identifies the service class to be dropped. The service-class-name must identify a service class that is described in the catalog (SQLSTATE 42704). To drop a service subclass, the service-superclass-name must be specified using the UNDER clause.

UNDER service-superclass-name

Specifies the service superclass of the service subclass when dropping a service subclass. The service-superclass-name must identify a service superclass that is described in the catalog (SQLSTATE 42704).

RESTRICT

This keyword enforces the rule that the service class is not to be dropped if any of the following dependencies exists:

• The service class is a service superclass and there is a user defined service subclass under the service class (SQLSTATE 5U031). The service subclass must first be dropped.
• The service class is a service superclass and there is a work action set mapping to the service class (SQLSTATE 5U031). The work action set must first be dropped.
• The service class is a service subclass and there is a work action mapping to the service class (SQLSTATE 5U031). The work action must first be dropped.
• The service class has a workload mapping (SQLSTATE 5U031). The workload mapping must first be removed. Remove the workload mapping by dropping the workload or altering the workload to not map to the service class.
• The service class has an associated threshold (SQLSTATE 5U031). The threshold must first be dropped.
• The service class is the target of a REMAP ACTIVITY action in a threshold (SQLSTATE 5U031). Alter the threshold to set a different service subclass as the target of the REMAP ACTIVITY action or drop the threshold.
• The service class is not disabled (SQLSTATE 5U031). The service class must first be disabled.

RESTRICT is the default behavior.

STOGROUP storagegroup-name

Identifies the storage group that is to be dropped; storagegroup-name must identify a storage group that exists at the current server (SQLSTATE 42704). This is a one-part name.

RESTRICT

The RESTRICT keyword prevents the storage group from being dropped if a table space exists that uses the storage group (SQLSTATE 42893). RESTRICT is the default behavior.

The current default storage group cannot be dropped (SQLSTATE 42893). A new default can be designated using the ALTER STOGROUP statement.

The DROP STOGROUP statement cannot be executed while a database partition server is being added (SQLSTATE 55071).

TABLE table-name

Identifies the base table, created temporary table, or declared temporary table that is to be dropped. The table-name must identify a table that is described in the catalog or, if it is a declared temporary table, the table-name must be qualified by the schema name SESSION and exist in the application (SQLSTATE 42704). The subtables of a typed table are dependent on their supertables. All subtables must be dropped before a supertable can be dropped (SQLSTATE 42893). The table-name must not identify a catalog table (SQLSTATE 42832), or a history table associated with a system-period temporal table (SQLSTATE 42893). The specified table is deleted from the database.

All indexes, primary keys, foreign keys, row permissions (including the default row permission), column masks, check constraints, materialized query tables, and staging tables that are defined on the table are dropped. All views and triggers that reference the table are made inoperative, including both the table referenced in the ON clause of the CREATE TRIGGER statement and all tables referenced within the triggered SQL statements. All packages which depend on any object dropped or marked inoperative will be invalidated. This includes packages dependent on any supertables above the subtable in the hierarchy. Any referenced columns for which the dropped table is defined as the scope of the reference become unscoped.

Packages are not dependent on declared temporary tables, and therefore are not invalidated when such a table is dropped. Packages are, however, dependent on created temporary tables, and are invalidated when such a table is dropped.

In a federated system, a remote table that was created using transparent DDL can be dropped. Dropping a remote table also drops the nickname associated with that table, and invalidates any packages that are dependent on that nickname.

When a subtable is dropped from a table hierarchy, the columns associated with the subtable are no longer accessible although they continue to be considered with respect to limits on the number of columns and size of the row. Dropping a subtable has the effect of deleting all the rows of the subtable from the supertables. This may result in activation of triggers or referential integrity constraints defined on the supertables.

When a created temporary table or declared temporary table is dropped, and its creation preceded the active unit of work or savepoint, then the table will be functionally dropped and the application will not be able to access the table. However, the table will still reserve some space in its table space and will prevent that USER TEMPORARY table space from being dropped or the database partition group of the USER TEMPORARY table space from being redistributed until the unit of work is committed or savepoint is ended. Dropping a created temporary table or declared temporary table causes the data in the table to be destroyed, regardless of whether DROP is committed or rolled back.

If table-name is a system-period temporal table, any associated history table and any indexes defined on the history table are also dropped. To drop a system-period temporal table, the privilege set must also contain the authorization required to drop the history table (SQLSTATE 42501).

A history table associated with a system-period temporal table cannot be explicitly dropped using the DROP statement (SQLSTATE 42893). A history table is implicitly dropped when the associated system-period temporal table is dropped.

A table cannot be dropped if it has the RESTRICT ON DROP attribute.

A newly detached table is initially inaccessible. This prevents the table from being read, modified, or dropped until the SET INTEGRITY statement can be run to incrementally refresh MQTs or to complete any processing for foreign key constraints. After the SET INTEGRITY statement executes against all dependent tables, the table is fully accessible, its detached attribute is reset, and it can be dropped.

When a table is dropped, all row permissions, including the default row permission, and column masks that are created for the table are also dropped.

If the table is referenced in the definition of a row permission or a column mask, the table cannot be dropped (SQLSTATE 42893).

TABLE HIERARCHY root-table-name

Identifies the typed table hierarchy that is to be dropped. The root-table-name must identify a typed table that is the root table in the typed table hierarchy (SQLSTATE 428DR). The typed table identified by root-table-name and all of its subtables are deleted from the database.

All indexes, materialized query tables, staging tables, primary keys, foreign keys, and check constraints referencing the dropped tables are dropped. All views and triggers that reference the dropped tables are made inoperative. All packages depending on any object dropped or marked inoperative will be invalidated. Any reference columns for which one of the dropped tables is defined as the scope of the reference become unscoped.

Unlike dropping a single subtable, dropping the table hierarchy does not result in the activation of delete triggers of any tables in the hierarchy nor does it log the deleted rows.

TABLESPACE or TABLESPACES tablespace-name

Identifies the table spaces that are to be dropped; tablespace-name must identify a table space that is described in the catalog (SQLSTATE 42704). This is a one-part name. tablespace-name must not identify a table space that contains a history table unless the system-period temporal table with which it is associated is also being dropped (SQLSTATE 42893).

The table spaces will not be dropped (SQLSTATE 55024) if there is any table that stores at least one of its parts in a table space being dropped, and has one or more of its parts in another table space that is not being dropped (these tables would need to be dropped first), or if any table that resides in the table space has the RESTRICT ON DROP attribute.

Objects whose names are prefixed with 'SYS' are built-in objects and, with the exception of the SYSTOOLSPACE and SYSTOOLSTMPSPACE table spaces, cannot be dropped (SQLSTATE 42832).

A SYSTEM TEMPORARY table space cannot be dropped (SQLSTATE 55026) if it is the only temporary table space that exists in the database. A USER TEMPORARY table space cannot be dropped if there is an instance of a created temporary table or a declared temporary table created in it (SQLSTATE 55039). Even if a created temporary table has been dropped, the USER TEMPORARY table space will still be considered to be in used until all instances of the created temporary table are dropped. Instances of a created temporary table are dropped when the session terminates or when the created temporary table is referenced in the session. Even if a declared temporary table has been dropped, the USER TEMPORARY table space will still be considered to be in use until the unit of work containing the DROP TABLE statement has been committed.

Dropping a table space drops all objects that are defined in the table space. All existing database objects with dependencies on the table space, such as packages, referential constraints, and so on, are dropped or invalidated (as appropriate), and dependent views and triggers are made inoperative.

Containers that were created by a user are not deleted. Any directories in the path of the container name that were created by the database manager during CREATE TABLESPACE execution are deleted. All containers that are below the database directory are deleted. When the DROP TABLESPACE statement is committed, the DMS file containers or SMS containers for the specified table space are deleted, if possible. If the containers cannot be deleted (because they are being kept open by another agent, for example), the files are truncated to zero length. After all connections are terminated, or the DEACTIVATE DATABASE command is issued, these zero-length files are deleted.

THRESHOLD threshold-name

Identifies the threshold that is to be dropped. The threshold-name must identify a threshold that exists at the current server (SQLSTATE 42704). This is a one-part name. Thresholds with a queue, for example TOTALSCPARTITIONCONNECTIONS and CONCURRENTDBCOORDACTIVITIES, must be disabled before they can be dropped (SQLSTATE 5U025). The specified threshold is deleted from the catalog.

TRIGGER trigger-name

Identifies the trigger that is to be dropped. The trigger-name must identify a trigger that is described in the catalog (SQLSTATE 42704). The specified trigger is deleted.

Dropping triggers causes certain packages to be marked invalid.

If trigger-name specifies an INSTEAD OF trigger on a view, another trigger may depend on that trigger through an update against the view.

TRANSFORM ALL FOR type-name

Indicates that all transforms groups defined for the user-defined data type type-name are to be dropped. The transform functions referenced in these groups are not dropped. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The type-name must identify a user-defined type described in the catalog (SQLSTATE 42704).

If there are not transforms defined for type-name, an error is returned (SQLSTATE 42740).

DROP TRANSFORM is the inverse of CREATE TRANSFORM. It causes the transform functions associated with certain groups, for a given data type, to become undefined. The functions formerly associated with these groups still exist and can still be called explicitly, but they no longer have the transform property, and are no longer invoked implicitly for exchanging values with the host language environment.

The transform group is not dropped if there is a user-defined function (or method) written in a language other than SQL that has a dependency on one of the group's transform functions defined for the user-defined type type-name (SQLSTATE 42893). Such a function has a dependency on the transform function associated with the referenced transform group defined for type type-name. Packages that depend on a transform function associated with the named transform group are marked inoperative.

TRANSFORMS group-name FOR type-name

Indicates that the specified transform group for the user-defined data type type-name is to be dropped. The transform functions referenced in this group are not dropped. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The type-name must identify a user-defined type described in the catalog (SQLSTATE 42704), and the group-name must identify an existing transform group for type-name.

TRIGGER trigger-name

Identifies the trigger that is to be dropped. The trigger-name must identify a trigger that is described in the catalog (SQLSTATE 42704). The specified trigger is deleted.

Dropping triggers causes certain packages to be marked invalid.

If trigger-name specifies an INSTEAD OF trigger on a view, another trigger may depend on that trigger through an update against the view.

TRUSTED CONTEXT context-name

Identifies the trusted context that is to be dropped. The context-name must identify a trusted context that exists at the current server (SQLSTATE 42704). If the trusted context is dropped while trusted connections for this context are active, those connections remain trusted until they terminate or until the next reuse attempt. If an attempt is made to switch the user on these trusted connections, an error is returned (SQLSTATE 42517). The specified trusted context is deleted from the catalog.

TYPE type-name

Identifies the user-defined type to be dropped. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. For a structured type, the associated reference type is also dropped. The type-name must identify a user-defined type described in the catalog.

RESTRICT

The type is not dropped (SQLSTATE 42893) if any of the following conditions are true:

• The type is used as the type of a column of a table or view.
• The type has a subtype.
• The type is a structured type used as the data type of a typed table or a typed view.
• The type is an attribute of another structured type.
• There exists a column of a table whose type might contain an instance of type-name. This can occur if type-name is the type of the column or is used elsewhere in the column's associated type hierarchy. More formally, for any type T, T cannot be dropped if there exists a column of a table whose type directly or indirectly uses type-name.
• The type is the target type of a reference-type column of a table or view, or a reference-type attribute of another structured type.
• The type, or a reference to the type, is a parameter type or a return value type of a function or method.
• The type is a parameter type or is used in the body of an SQL procedure.
• The type, or a reference to the type, is used in the body of an SQL function or method, but it is not a parameter type or a return value type.
• The type is used in a check constraint, trigger, view definition, or index extension.

If RESTRICT is not specified, the behavior is the same as RESTRICT, except for functions and methods that use the type.

The restrict rule is enforced by default for the same dependencies as in version 9.5 if the auto_reval database configuration parameter is set to disabled.

Functions that use the type: If the user-defined type can be dropped, then for every function, F (with specific name SF), that has parameters or a return value of the type being dropped or a reference to the type being dropped, the following DROP FUNCTION statement is effectively executed:

   DROP SPECIFIC FUNCTION SF

It is possible that this statement also would cascade to drop dependent functions. If all of these functions are also in the list to be dropped because of a dependency on the user-defined type, the drop of the user-defined type will succeed (otherwise it fails with SQLSTATE 42893).

Methods that use the type: If the user-defined type can be dropped, then for every method, M of type T1 (with specific name SM), that has parameters or a return value of the type being dropped or a reference to the type being dropped, the following statements are effectively executed:

   DROP SPECIFIC METHOD SM
   ALTER TYPE T1 DROP SPECIFIC METHOD SM

The existence of objects that are dependent on these methods may cause the DROP TYPE operation to fail.

All packages that are dependent on methods defined in supertypes of the type being dropped, and that are eligible for overriding, are invalidated.

If the type is referenced in the definition of a row permission or a column mask, the type cannot be dropped (SQLSTATE 42893).

TYPE MAPPING type-mapping-name

Identifies the user-defined data type mapping to be dropped. The type-mapping-name must identify a data type mapping that is described in the catalog (SQLSTATE 42704). The data type mapping is deleted from the database.

No additional objects are dropped.

USAGE LIST usage-list-name

Identifies the usage list that is to be dropped. The usage-list-name, including the implicit or explicit qualifier, must identify a usage list that is described in the catalog (SQLSTATE 42704). Memory allocated for the usage list is released and is not under transactional control.

USER MAPPING FOR authorization-name | USER SERVER server-name

Identifies the user mapping to be dropped. This mapping associates an authorization name that is used to access the federated database with an authorization name that is used to access a data source. The first of these two authorization names is either identified by the authorization-name or referenced by the special register USER. The server-name identifies the data source that the second authorization name is used to access.

The authorization-name must be listed in the catalog (SQLSTATE 42704). The server-name must identify a data source that is described in the catalog (SQLSTATE 42704). The user mapping is deleted.

No additional objects are dropped.

VARIABLE variable-name

Identifies the global variable that is to be dropped. The variable-name must identify a global variable that exists at the current server (SQLSTATE 42704).

If the variable is referenced in the definition of a row permission or a column mask, the variable cannot be dropped (SQLSTATE 42893).

RESTRICT

The RESTRICT keyword prevents the global variable from being dropped if it is referenced in an SQL routine definition, trigger definition, or view definition (SQLSTATE 42893).

The restrict rule is enforced by default for the same dependencies as in version 9.5 if the following conditions are met:

• The auto_reval database configuration parameter is set to disabled
• An inlined trigger definition, inlined SQL function definition, inlined SQL method definition, or view references the variable

VIEW view-name

Identifies the view that is to be dropped. The view-name must identify a view that is described in the catalog (SQLSTATE 42704). The subviews of a typed view are dependent on their superviews. All subviews must be dropped before a superview can be dropped (SQLSTATE 42893).

The specified view is deleted. The definition of any view or trigger that is directly or indirectly dependent on that view is marked inoperative. Any materialized query table or staging table that is dependent on any view that is marked inoperative is dropped. Any packages dependent on a view that is dropped or marked inoperative will be invalidated. This includes packages dependent on any superviews above the subview in the hierarchy. Any reference columns for which the dropped view is defined as the scope of the reference become unscoped.

If the view is referenced in the definition of a row permission or a column mask, the view cannot be dropped (SQLSTATE 42893).

VIEW HIERARCHY root-view-name

Identifies the typed view hierarchy that is to be dropped. The root-view-name must identify a typed view that is the root view in the typed view hierarchy (SQLSTATE 428DR). The typed view identified by root-view-name and all of its subviews are deleted from the database.

The definition of any view or trigger that is directly or indirectly dependent on any of the dropped views is marked inoperative. Any packages dependent on any view or trigger that is dropped or marked inoperative will be invalidated. Any reference columns for which a dropped view or view marked inoperative is defined as the scope of the reference become unscoped.

WORK ACTION SET work-action-set-name

Identifies the work action set that is to be dropped. The work-action-set-name must identify a work action set that exists at the current server (SQLSTATE 42704). All work actions that are contained by the work-action-set-name are also dropped.

WORK CLASS SET work-class-set-name

Identifies the work class set that is to be dropped. The work-class-set-name must identify a work class set that exists at the current server (SQLSTATE 42704). All work classes that are contained by the work-class-set-name are also dropped.

WORKLOAD workload-name

Identifies the workload that is to be dropped. This is a one-part name. The workload-name must identify a workload that exists at the current server (SQLSTATE 42704). SYSDEFAULTUSERWORKLOAD or SYSDEFAULTADMWORKLOAD cannot be dropped (SQLSTATE 42832). A workload must be disabled and must not have active workload occurrences associated with it before it can be dropped (SQLSTATE 5U023). To drop a workload with an associated threshold (SQLSTATE 5U031), you must drop the threshold first. The specified workload is deleted from the catalog.

WRAPPER wrapper-name

Identifies the wrapper to be dropped. The wrapper-name must identify a wrapper that is described in the catalog (SQLSTATE 42704). The wrapper is deleted.

All server definitions, user-defined function mappings, and user-defined data type mappings that are dependent on the wrapper are dropped. All user-defined function mappings, nicknames, user-defined data type mappings, and user mappings that are dependent on the dropped server definitions are also dropped. Any index specifications dependent on the dropped nicknames are dropped, and any views dependent on these nicknames are marked inoperative. All packages dependent on the dropped objects and inoperative views are invalidated. All federated procedures that are dependent on the dropped server definitions are also dropped.

XSROBJECT xsrobject-name

Identifies the XSR object to be dropped. The xsrobject-name must identify an XSR object that is described in the catalog (SQLSTATE 42704).

Check constraints that reference the XSR object are dropped. All triggers and views referencing the XSR object are marked inoperative. Packages having a dependency on a dropped XSR object are invalidated.

In a partitioned database environment, you can issue this statement against an XSR object by connecting to any partition.

Rules

¹

This dependency is implicit in depending on a table with these constraints, triggers, or a distribution key.

²

If a package has an INSERT, UPDATE, or DELETE statement acting upon a view, then the package has an insert, update or delete usage on the underlying base table of the view. In the case of UPDATE, the package has an update usage on each column of the underlying base table that is modified by the UPDATE.

If a package has a statement acting on a typed view, creating or dropping any view in the same view hierarchy will invalidate the package.

³

If a package, materialized query table, staging table, view, or trigger uses an alias, it becomes dependent both on the alias and the object that the alias references. If the alias is in a chain, a dependency is created on each alias in the chain.

Aliases themselves are not dependent on anything. It is possible for an alias to be defined on an object that does not exist.

⁴

A user-defined type T can depend on another user-defined type B, if T:

• names B as the data type of an attribute
• has an attribute of REF(B)
• has B as a supertype.

⁵

If the user-defined type is referenced as a function parameter type or return type, then the type will be dropped and its catalog data will be maintained due to the routine parameter dependency. A value 'X' in the VALID column of the SYSCAT.DATATYPES catalog view indicates this dropped type. Its catalog data will be deleted by a DROP FUNCTION statement if the DROP FUNCTION statement also dropped the last routine parameter dependency on this type, or will be deleted by a CREATE TYPE statement with the same schema name, module name, and type name. If the user-defined type is a structured type, any methods that are associated with the type are also dropped.

⁶

Dropping a table space or a list of table spaces causes all the tables that are completely contained within the given table space or list to be dropped. However, if a table spans table spaces (indexes, long columns, or data partitions in different table spaces) and those table spaces are not in the list being dropped, the table spaces cannot be dropped as long as the table exists.

⁷

A function can depend on another specific function if the depending function names the base function in a SOURCE clause. A function or method can also depend on another specific function or method if the depending routine is written in SQL and uses the base routine in its body. An external method, or an external function with a structured type parameter or returns type will also depend on one or more transform functions.

⁸

Only loss of SELECT privilege will cause a materialized query table to be dropped or a view to become inoperative. If the view that is made inoperative is included in a typed view hierarchy, all of its subviews also become inoperative.

⁹

If a package has an INSERT, UPDATE, or DELETE statement acting on table T, then the package has an insert, update or delete usage on T. In the case of UPDATE, the package has an update usage on each column of T that is modified by the UPDATE.

If a package has a statement acting on a typed table, creating or dropping any table in the same table hierarchy will invalidate the package.

¹⁰

Dependencies do not exist at the column level because privileges on columns cannot be revoked individually.

If a package, trigger or view includes the use of OUTER(Z) in the FROM clause, there is a dependency on the SELECT privilege on every subtable or subview of Z. Similarly, if a package, trigger, or view includes the use of DEREF(Y) where Y is a reference type with a target table or view Z, there is a dependency on the SELECT privilege on every subtable or subview of Z.

¹¹

A materialized query table is dependent on the underlying tables or nicknames specified in the fullselect of the table definition.

Cascade semantics apply to dependent materialized query tables.

A subtable is dependent on its supertables up to the root table. A supertable cannot be dropped until all of its subtables are dropped.

A history table is dependent on the system-period temporal table with which it is associated. Cascade semantics apply to the history table when the system-period temporary table on which depends is dropped.

¹²

A package can depend on structured types as a result of using the TYPE predicate or the subtype-treatment expression (TREAT expression AS data-type). The package has a dependency on the subtypes of each structured type specified in the right side of the TYPE predicate, or the right side of the TREAT expression. Dropping or creating a structured type that alters the subtypes on which the package is dependent causes invalidation.

All packages that are dependent on methods defined in supertypes of the type being dropped, and that are eligible for overriding, are invalidated.

¹³

A check constraint or trigger is dependent on a type if the type is used anywhere in the constraint or trigger. There is no dependency on the subtypes of a structured type used in a TYPE predicate within a check constraint or trigger.

¹⁴

A view is dependent on a type if the type is used anywhere in the view definition (this includes the type of typed view). There is no dependency on the subtypes of a structured type used in a TYPE predicate within a view definition.

¹⁵

A subview is dependent on its superview up to the root view. A superview cannot be dropped until all its subviews are dropped. Refer to ¹⁶ for additional view dependencies.

¹⁶

A trigger or view is also dependent on the target table or target view of a dereference operation or DEREF function. A trigger or view with a FROM clause that includes OUTER(Z) is dependent on all the subtables or subviews of Z that existed at the time the trigger or view was created.

¹⁷

A typed view can depend on the existence of a unique index to ensure the uniqueness of the object identifier column.

¹⁸

A table may depend on a user defined data type (distinct or structured) because the type is:

• used as the type of a column
• used as the type of the table
• used as an attribute of the type of the table
• used as the target type of a reference type that is the type of a column of the table or an attribute of the type of the table
• directly or indirectly used by a type that is the column of the table.

¹⁹

Dropping a server cascades to drop the function mappings and type mappings created for that named server.

²⁰

If the distribution key is defined on a table in a multiple partition database partition group, the distribution key is required.

²¹

If a dependent OLE DB table function has "R" dependent objects (see DROP FUNCTION), then the server cannot be dropped.

²²

An SQL function or method can depend on the objects referenced by its body.

²³

When an attribute A of type TA of type-name T is dropped, the following DROP statements are effectively executed:

   Mutator method: DROP METHOD A (TA) FOR T
   Observer method: DROP METHOD A () FOR T
   ALTER TYPE T
     DROP METHOD A(TA)
     DROP METHOD A()

²⁴

A table may depend on an attribute of a user-defined structured data type in the following cases:

1. The table is a typed table that is based on type-name or any of its subtypes.
2. The table has an existing column of a type that directly or indirectly refers to type-name.

²⁵

A REVOKE of SELECT privilege on a table or view that is used in the body of an SQL function or method body causes an attempt to drop the function or method body, if the function or method body defined no longer has the SELECT privilege. If such a function or method body is used in a view, trigger, function, or method body, it cannot be dropped, and the REVOKE is restricted as a result. Otherwise, the REVOKE cascades and drops such functions.

²⁶

A trigger depends on an INSTEAD OF trigger when it modifies the view on which the INSTEAD OF trigger is defined, and the INSTEAD OF trigger fires.

²⁷

A method declaration of an original method that is overridden by other methods cannot be dropped (SQLSTATE 42893).

²⁸

If the method of the method body being created is declared to override another method, all packages dependent on the overridden method, and on methods that override this method in supertypes of the method being created, are invalidated.

²⁹

When a new subtype of an existing type is created, all packages dependent on methods that are defined in supertypes of the type being created, and that are eligible for overriding (for example, no mutators or observers), are invalidated.

³⁰

If the specific method of the method body being dropped is declared to override another method, all packages dependent on the overridden method, and on methods that override this method in supertypes of the specific method being dropped, are invalidated.

³¹

Cached dynamic SQL has the same semantics as packages.

³²

When a remote base table is dropped using the DROP TABLE statement, both the nickname and the remote base table are dropped.

³³

A primary key or unique keys that are not referenced by a foreign key do not restrict the altering of a nickname local name or local type.

³⁴

An XSROBJECT can become inoperative for decomposition as a result of changes to a table that is associated with the XML schema for decomposition. Changes that could impact decomposition are: dropping the table or dropping a column of the table, or changing a column of the table. The decomposition status of the XML schema can be reset by issuing an ALTER XSROBJECT statement to enable or disable decomposition for the XML schema.

³⁵

• A service class cannot be dropped if any threshold is mapped to it (SQLSTATE 5U031).
• A service class cannot be dropped if any workload is mapped to it (SQLSTATE 5U031).
• A service superclass cannot be dropped until all of its user-defined service subclasses have been dropped (SQLSTATE 5U031).
• A service superclass cannot be dropped if any work action set is mapped to it (SQLSTATE 5U031).
• A service subclass cannot be dropped if any work action is mapped to it (SQLSTATE 5U031).

³⁶

A work class set cannot be dropped until the work action set that is defined on it has been dropped.

³⁷

Once the index or table is dropped, its usage list will be invalidated in the catalog. Revalidation will take place on the next activation of the list or it can be explicitly revalidated using the procedure ADMIN_REVALIDATE_DB_OBJECTS.

³⁸

Revoking a privilege is restricted if it causes an object to be dropped or invalidated, and a permission or mask depends on it. For example, if you have a view which depends on a table, and a permission or mask that references the view, REVOKE SELECT on the table invalidates the view, but causes an error.

³⁹

Packages are invalidated when a table on which the enabled permission is defined has row level access control activated on the table. Packages are not affected when dropping a permission that is disabled or is defined on a table with row access control deactivated.

⁴⁰

Packages are invalidated when a table on which the enabled permission is defined has row level access control activated on the table. Packages are not affected when dropping a permission that is disabled or is defined on a table with row access control deactivated.

The DROP DATABASE PARTITION GROUP statement might fail (SQLSTATE 55071) if an add database partition server request is either pending or in progress. This statement might also fail (SQLSTATE 55077) if a new database partition server is added online to the instance and not all applications are aware of the new database partition server.

Notes

• It is valid to drop a user-defined function while it is in use. Also, a cursor can be open over a statement which contains a reference to a user-defined function, and while this cursor is open the function can be dropped without causing the cursor fetches to fail.
• If a package which depends on a user-defined function is executing, it is not possible for another authorization ID to drop the function until the package completes its current unit of work. At that point, the function is dropped and the package becomes inoperative. The next request for this package results in an error indicating that the package must be explicitly rebound.
• The removal of a function body (this is very different from dropping the function) can occur while an application which needs the function body is executing. This may or may not cause the statement to fail, depending on whether the function body still needs to be loaded into storage by the database manager on behalf of the statement.
• In addition to the dependencies recorded for any explicitly specified UDF, the following dependencies are recorded when transforms are implicitly required:
  1. When the structured type parameter or result of a function or method requires a transform, a dependency is recorded for the function or method on the required TO SQL or FROM SQL transform function.
  2. When an SQL statement included in a package requires a transform function, a dependency is recorded for the package on the designated TO SQL or FROM SQL transform function.

  Since these describe the only circumstances under which dependencies are recorded due to implicit invocation of transforms, no objects other than functions, methods, or packages can have a dependency on implicitly invoked transform functions. On the other hand, explicit calls to transform functions (in views and triggers, for example) do result in the usual dependencies of these other types of objects on transform functions. As a result, a DROP TRANSFORM statement may also fail due to these "explicit" type dependencies of objects on the transform(s) being dropped (SQLSTATE 42893).

• Since the dependency catalogs do not distinguish between depending on a function as a transform versus depending on a function by explicit function call, it is suggested that explicit calls to transform functions are not written. In such an instance, the transform property on the function cannot be dropped, or packages will be marked inoperative, simply because they contain explicit invocations in an SQL expression.
• System created sequences for IDENTITY columns cannot be dropped using the DROP SEQUENCE statement.
• When a sequence is dropped, all privileges on the sequence are also dropped and any packages that refer to the sequence are invalidated.
• For relational nicknames, the DROP NICKNAME statement within a given unit of work (UOW) cannot be processed under either of the following conditions (SQLSTATE 55007):
  • A nickname referenced in this statement has a cursor open on it in the same UOW
  • Either an INSERT, DELETE, or UPDATE statement is already issued in the same UOW against the nickname that is referenced in this statement
• For non-relational nicknames, the DROP NICKNAME statement within a given unit of work (UOW) cannot be processed under any of the following conditions (SQLSTATE 55007):
  • A nickname referenced in this statement has a cursor open on it in the same UOW
  • A nickname referenced in this statement is already referenced by a SELECT statement in the same UOW
  • Either an INSERT, DELETE, or UPDATE statement has already been issued in the same UOW against the nickname that is referenced in this statement
• A DROP SERVER statement (SQLSTATE 55006), or a DROP FUNCTION MAPPING or DROP TYPE MAPPING statement (SQLSTATE 55007) within a given unit of work (UOW) cannot be processed under either of the following conditions:
  • The statement references a single data source, and the UOW already includes one of the following items:
    • A SELECT statement that references a nickname for a table or view within this data source
    • An open cursor on a nickname for a table or view within this data source
    • Either an INSERT, DELETE, or UPDATE statement issued against a nickname for a table or view within this data source
  • The statement references a category of data sources (for example, all data sources of a specific type and version), and the UOW already includes one of the following items:
    • A SELECT statement that references a nickname for a table or view within one of these data sources
    • An open cursor on a nickname for a table or view within one of these data sources
    • Either an INSERT, DELETE, or UPDATE statement issued against a nickname for a table or view within one of these data sources
• The DROP WORKLOAD statement does not take effect until it is committed, even for the connection that issues the statement.
• Only one of these statements can be issued by any application at a time, and only one of these statements is allowed within any one unit of work. Each statement must be followed by a COMMIT or a ROLLBACK statement before another one of these statements can be issued (SQLSTATE 5U021).
  • CREATE HISTOGRAM TEMPLATE, ALTER HISTOGRAM TEMPLATE, or DROP (HISTOGRAM TEMPLATE)
  • CREATE SERVICE CLASS, ALTER SERVICE CLASS, or DROP (SERVICE CLASS)
  • CREATE THRESHOLD, ALTER THRESHOLD, or DROP (THRESHOLD)
  • CREATE WORK ACTION, ALTER WORK ACTION, or DROP (WORK ACTION)
  • CREATE WORK CLASS, ALTER WORK CLASS, or DROP (WORK CLASS)
  • CREATE WORKLOAD, ALTER WORKLOAD, or DROP (WORKLOAD)
  • GRANT (Workload Privileges) or REVOKE (Workload Privileges)
• Soft invalidation: After the drop or change of a database object done by the following statements, active access to the dropped or changed object continues until the access is complete.
  • ALTER FUNCTION
  • ALTER MODULE ... DROP FUNCTION
  • ALTER MODULE ... DROP VARIABLE
  • ALTER TABLE ... DETACH PARTITION
  • ALTER VIEW
  • DROP ALIAS
  • DROP FUNCTION
  • DROP TRIGGER
  • DROP VARIABLE
  • DROP VIEW
  • All of the CREATE OR REPLACE statements except CREATE OR REPLACE SEQUENCE.
  This is the case when the database registry variable DB2_DLL_SOFT_INVALID is set to ON. When it is set to OFF, the drop or change of these objects will only complete after all active access to the object to be dropped or changed is complete.
• Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of DB2 and with other database products. These alternatives are non-standard and should not be used.
  • NODEGROUP can be specified in place of DATABASE PARTITION GROUP
  • DISTINCT TYPE type-name can be specified in place of TYPE type-name
  • DATA TYPE type-name can be specified in place of TYPE type-name
  • SYNONYM can be specified in place of ALIAS
  • PROGRAM can be specified in place of PACKAGE
• Invalidation of packages and dynamically cached statements after dropping row permissions or column masks: If row level access control is activated on the table, dropping an enabled row permission defined for that table invalidates all packages and dynamically cached statements that reference that same table. If column level access control is activated on the table, dropping an enabled column mask defined for that table invalidates all packages and dynamically cached statements that reference that same table. There is no invalidation for dropping disabled masks or permissions.
• Circular dependency: Circular dependency exists in the following example:

  CREATE PERMISSION RP1 ON T1 FOR ROWS
       WHERE C1>(SELECT MAX(C1) FROM T2)
  ENFORCED FOR ALL ACCESS
  ENABLE;
  
  CREATE PERMISSION RP2 ON T2 FOR ROWS
       WHERE C1>(SELECT MAX(C1) FROM T1)
  ENFORCED FOR ALL ACCESS
  ENABLE

  The DROP TABLE T1 and DROP TABLE T2 statements fail because RP1 depends on T2 and RP2 depends on T1. The user with the SECADM authority should drop one of the row permissions first then issue the DROP TABLE statement.

Examples

• Example 1:  Drop table TDEPT.

     DROP TABLE TDEPT

• Example 2:  Drop the view VDEPT.

     DROP VIEW VDEPT

• Example 3:  The authorization ID HEDGES attempts to drop an alias.

     DROP ALIAS A1

  The alias HEDGES.A1 is removed from the catalogs.
• Example 4:  Hedges attempts to drop an alias, but specifies T1 as the alias-name, where T1 is the name of an existing table (not the name of an alias).

     DROP ALIAS T1

  This statement fails (SQLSTATE 42809).
• Example 5: Drop the BUSINESS_OPS database partition group. To drop the database partition group, the two table spaces (ACCOUNTING and PLANS) in the database partition group must first be dropped.

     DROP TABLESPACE ACCOUNTING
     DROP TABLESPACE PLANS
     DROP DATABASE PARTITION GROUP BUSINESS_OPS

• Example 6:  Pellow wants to drop the CENTRE function, which he created in his PELLOW schema, using the signature to identify the function instance to be dropped.

     DROP FUNCTION CENTRE (INT,FLOAT)

• Example 7:  McBride wants to drop the FOCUS92 function, which she created in the PELLOW schema, using the specific name to identify the function instance to be dropped.

     DROP SPECIFIC FUNCTION PELLOW.FOCUS92

• Example 8:  Drop the function ATOMIC_WEIGHT from the CHEM schema, where it is known that there is only one function with that name.

     DROP FUNCTION CHEM.ATOMIC_WEIGHT

• Example 9:  Drop the trigger SALARY_BONUS, which caused employees under a specified condition to receive a bonus to their salary.

     DROP TRIGGER SALARY_BONUS

• Example 10:  Drop the distinct data type named shoesize, if it is not currently in use.

     DROP TYPE SHOESIZE

• Example 11:  Drop the SMITHPAY event monitor.

     DROP EVENT MONITOR SMITHPAY

• Example 12:  Drop the schema from Example 2 under CREATE SCHEMA using RESTRICT. Notice that the table called PART must be dropped first.

    DROP TABLE PART
    DROP SCHEMA INVENTRY RESTRICT

• Example 13:  Macdonald wants to drop the DESTROY procedure, which he created in the EIGLER schema, using the specific name found in the system catalog to identify the procedure to be dropped.

     DROP SPECIFIC PROCEDURE  EIGLER.SQL100506102825100

• Example 14:  Drop the procedure OSMOSIS from the BIOLOGY schema, where it is known that there is only one procedure with that name.

     DROP PROCEDURE BIOLOGY.OSMOSIS

• Example 15:  User SHAWN used one authorization ID to access the federated database and another to access the database at an Oracle data source called ORACLE1. A mapping was created between the two authorizations, but SHAWN no longer needs to access the data source. Drop the mapping.

     DROP USER MAPPING FOR  SHAWN SERVER ORACLE1

• Example 16:  An index of a data source table that a nickname references has been deleted. Drop the index specification that was created to let the optimizer know about this index.

     DROP INDEX INDEXSPEC 

• Example 17:  Drop the MYSTRUCT1 transform group.

     DROP TRANSFORM MYSTRUCT1 FOR POLYGON

• Example 18:  Drop the method BONUS for the EMP data type in the PERSONNEL schema.

     DROP METHOD BONUS (SALARY DECIMAL(10,2)) FOR PERSONNEL.EMP

• Example 19:  Drop the sequence ORG_SEQ, with restrictions.

     DROP SEQUENCE ORG_SEQ

• Example 20:  A remote table EMPLOYEE was created in a federated system using transparent DDL. Access to the table is no longer needed. Drop the remote table EMPLOYEE.

     DROP TABLE EMPLOYEE

• Example 21: Drop the function mapping BONUS_CALC and reinstate the default function mapping (if one exists).

     DROP FUNCTION MAPPING BONUS_CALC

• Example 22: Drop the security label component LEVEL.

     DROP SECURITY LABEL COMPONENT LEVEL

• Example 23: Drop the security label EMPLOYEESECLABEL of the security policy DATA_ACCESS.

     DROP SECURITY LABEL DATA_ACCESS.EMPLOYEESECLABEL

• Example 24: Drop the security policy DATA_ACCESS.

     DROP SECURITY POLICY DATA_ACCESS

• Example 25: Drop the security label component GROUPS.

     DROP SECURITY LABEL COMPONENT GROUPS

• Example 26: Drop the XML schema EMPLOYEE located in the SQL schema HR.

     DROP XSROBJECT HR.EMPLOYEE

• Example 27: Drop service subclass DOGSALES under service superclass PETSALES.

     DROP SERVICE CLASS DOGSALES UNDER PETSALES

• Example 28: Drop service superclass PETSALES, which has no user-defined service subclasses. The default subclass for service class PETSALES is automatically dropped.

     DROP SERVICE CLASS PETSALES

• Example 29: DROP permission P1.

     DROP PERMISSION P1

• Example 30: DROP mask M1.

     DROP MASK M1

• Example 31: Drop a storage group named TEST_SG.

     DROP STOGROUP TEST_SG

• Example 32: Drop the usage list MON_PAYROLL

     DROP USAGE LIST MON_PAYROLL