SQL Reference

DROP

The DROP statement deletes an object. Any objects that are directly or indirectly dependent on that object are either deleted or made inoperative. (See Inoperative Trigger and Inoperative views for details.) Whenever an object is deleted, its description is deleted from the catalog and any packages that reference the object are invalidated.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges that must be held by the authorization ID of the DROP statement when dropping objects that allow two-part names must include one of the following:

SYSADM or DBADM authority
DROPIN privilege on the schema for the object
definer of the object as recorded in the DEFINER column of the catalog view for the object
CONTROL privilege on the object (applicable only to indexes, index specifications, nicknames, packages, tables, and views).

The authorization ID of the DROP statement when dropping a table or view hierarchy must hold one of the above privileges for each of the tables or views in the hierarchy.

The authorization ID of the DROP statement when dropping a schema must have SYSADM or DBADM authority or be the schema owner as recorded in the OWNER column of SYSCAT.SCHEMATA.

The authorization ID of the DROP statement when dropping a buffer pool, nodegroup, or table space must have SYSADM or SYSCTRL authority.

The authorization ID of the DROP statement when dropping an event monitor, server definition, data type mapping, function mapping or a wrapper must have SYSADM or DBADM authority.

The authorization ID of the DROP statement when dropping a user mapping must have SYSADM or 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--------------------------------------------------------->
 
              (1)
>-----+-ALIAS-------alias-name------------------------------------------+>
      +-BUFFERPOOL--bufferpool-name-------------------------------------+
      +-EVENT--MONITOR--event-monitor-name------------------------------+
      +-FUNCTION----function-name--+-------------------------------+----+
      |                            '-(--+-------------------+---)--'    |
      |                                 |  .-,-----------.  |           |
      |                                 |  V             |  |           |
      |                                 '----data-type---+--'           |
      +-SPECIFIC FUNCTION--specific-name--------------------------------+
      +-FUNCTION MAPPING--function-mapping-name-------------------------+
      |                   (2)                                           |
      +-INDEX--index-name-----------------------------------------------+
      +-NICKNAME--nickname----------------------------------------------+
      +-NODEGROUP--nodegroup-name---------------------------------------+
      |         (3)                                                     |
      +-PACKAGE-------package-name--------------------------------------+
      +-PROCEDURE--procedure-name--+-------------------------------+----+
      |                            '-(--+-------------------+---)--'    |
      |                                 |  .-,-----------.  |           |
      |                                 |  V             |  |           |
      |                                 '----data-type---+--'           |
      +-SPECIFIC PROCEDURE--specific-name-------------------------------+
      +-SCHEMA--schema-name--RESTRICT-----------------------------------+
      +-SERVER--server-name---------------------------------------------+
      +-TABLE--table-name-----------------------------------------------+
      +-TABLE HIERARCHY--root-table-name--------------------------------+
      |                    .-,------------------.                       |
      |                    V                    |                       |
      +--+-TABLESPACE--+------tablespace-name---+-----------------------+
      |  '-TABLESPACES-'                                                |
      +-TRIGGER--trigger-name-------------------------------------------+
      +-+----------------+---TYPE--type-name----------------------------+
      | |          (4)   |                                              |
      | '-DISTINCT-------'                                              |
      +-TYPE MAPPING--type-mapping-name---------------------------------+
      +-USER MAPPING FOR--+-authorization-name-+---SERVER--server-name--+
      |                   '-USER---------------'                        |
      +-VIEW--view-name-------------------------------------------------+
      +-VIEW HIERARCHY--root-view-name----------------------------------+
      '-WRAPPER--wrapper-name-------------------------------------------'
 
>--------------------------------------------------------------><

Notes:

SYNONYM can be used as a synonym for ALIAS.
Index-name can be the name of either an index or an index specification.
PROGRAM can be used as a synonym for PACKAGE.
DATA can also be used when dropping any user-defined type.

Description

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.

All tables, views and triggers ⁸⁴ that reference the alias are made inoperative.

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). The storage for the buffer pool will not be released until the database is stopped.

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 ON, an error (SQLSTATE 55034) is raised. Otherwise, the event monitor is deleted.

If there are event files in the target path of the event monitor when the event monitor is dropped, the event files are not deleted. However, if a new event monitor is created which specifies the same target path, then the event files are deleted.

FUNCTION

Identifies an instance of a user-defined function (either a complete function or a function template) that is to be dropped. The function instance specified must be a user-defined function described in the catalog. Functions implicitly generated by the CREATE DISTINCT TYPE statement cannot be dropped.

There are several different ways available to identify the function instance:

FUNCTION function-name

Identifies the particular function, and is valid only if there is exactly one function instance with the function-name. The function thus identified may have any number of parameters defined for it. 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. If no function by this name exists in the named or implied schema, an error (SQLSTATE 42704) is raised. If there is more than one specific instance of the function in the named or implied schema, an error (SQLSTATE 42854) is raised.

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

Provides the function signature, which uniquely identifies the function to be dropped. The function selection algorithm is not used.

function-name

Gives the function name of the function 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.

(data-type,...)

Must match the data types that were specified on the CREATE FUNCTION statement in the corresponding position. 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.

If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.

It is not necessary to specify the length, precision or scale for the parameterized data types. Instead, an empty set of parentheses may be coded to indicate that these attributes are to be ignored when looking for a data type match.

FLOAT() cannot be used (SQLSTATE 42601) since the parameter value indicates different data types (REAL or DOUBLE).

However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE PROCEDURE statement.

A type of FLOAT(n) does not need to match the defined value for n since 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.

If no function with the specified signature exists in named or implied schema, an error (SQLSTATE 42883) is raised.

SPECIFIC FUNCTION specific-name

Identifies the particular user-defined function that is to be dropped, using the specific name either specified or defaulted to at function creation time. 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 specific-name must identify a specific function instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised.

It is not possible to drop a function that is in either the SYSIBM schema or the SYSFUN schema (SQLSTATE 42832).

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 page *** 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 reprepared by use of the PREP command. See the Command Reference for information on these commands.

FUNCTION MAPPING function-mapping-name

Identifies the function mapping 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. However, they can be disabled. For an example, see Example 3 in CREATE FUNCTION MAPPING.

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

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 required by the system for a primary key or unique constraint or for a replicated summary table (SQLSTATE 42917). The specified index or index specification is deleted.

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

NICKNAME nickname

Identifies the nickname 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 index specifications that are dependent on the nickname are dropped. Any views dependent on the nickname are marked inoperative. Any packages depending on the dropped index specifications or inoperative views are invalidated. The data source table that the nickname references is not affected.

NODEGROUP nodegroup-name

Identifies the nodegroup that is to be dropped. nodegroup-name must identify a nodegroup that is described in the catalog (SQLSTATE 42704). This is a one-part name.

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

System defined nodegroups cannot be dropped (SQLSTATE 42832).

If a DROP NODEGROUP is issued against a nodegroup that is currently undergoing a data redistribution, the DROP NODEGROUP operation fails an error is returned (SQLSTATE 55038). However, a partially redistributed nodegroup can be dropped. A nodegroup can become partially redistributed if a REDISTRIBUTE NODEGROUP command does not execute to completion. This can happen if it gets interrupted by either an error or a force application all command ⁸⁵ .

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. All privileges on the package are also deleted.

PROCEDURE

Identifies an instance of a stored procedure that is to be dropped. The procedure instance specified must be a stored procedure described in the catalog.

There are several different ways available to identify the procedure instance:

PROCEDURE procedure-name

Identifies the particular procedure, and is valid only if there is exactly one procedure instance with the procedure-name in the schema. The procedure thus identified may have any number of parameters defined for it. If no procedure by this name exists in the named or implied schema, an error (SQLSTATE 42704) is raised. 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. If there is more than one specific instance of the procedure in the named or implied schema, an error (SQLSTATE 42854) is raised.

PROCEDURE procedure-name (data-type,...)

Provides the procedure signature, which uniquely identifies the procedure to be dropped. The procedure selection algorithm is not used.

procedure-name

Gives the procedure name of the procedure 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.

(data-type,...)

Must match the data types that were specified on the CREATE PROCEDURE statement in the corresponding position. The number of data types, and the logical concatenation of the data types is used to identify the specific procedure instance which is to be dropped.

If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.

FLOAT() cannot be used (SQLSTATE 42601) since the parameter value indicates different data types (REAL or DOUBLE).

However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement.

A type of FLOAT(n) does not need to match the defined value for n since 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.

If no procedure with the specified signature exists in named or implied schema, an error (SQLSTATE 42883) is raised.

SPECIFIC PROCEDURE specific-name

Identifies the particular stored procedure that is to be dropped, using the specific name either specified or defaulted to at procedure creation time. 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 specific-name must identify a specific procedure instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised.

SCHEMA schema-name RESTRICT

Identifies the schema that is 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).

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.

TABLE table-name

Identifies the base table or summary table that is to be dropped. The table-name must identify a table that is described in the catalog (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 specified table is deleted from the database.

All indexes, primary keys, foreign keys, and check constraints referencing the table are dropped. All views and triggers ⁸⁶ that reference the table are made inoperative. All packages depending on any object dropped or marked inoperative will be invalidated. This includes packages dependent on any supertables above the subtable in the hierarchy. Any reference columns for which the dropped table is defined as the scope of the reference become unscoped.

All files that are linked through any DATALINK columns are unlinked. The unlink operation is performed asynchronously so the files may not be immediately available for other operations.

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.

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,summary 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.

All files that are linked through any DATALINK columns are unlinked. The unlink operation is performed asynchronously so the files may not be immediately available for other operations.

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.

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). System table spaces cannot be dropped (SQLSTATE 42832). A temporary table space can not be dropped (SQLSTATE 55026) if it is the only temporary table space that exists in the database.

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

Containers created by the user are not deleted. Any directories in the path of the container name that were created by the database manager on CREATE TABLESPACE will be deleted. All containers that are below the database directory are deleted.

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. See the "Notes" section in CREATE TRIGGER concerning the creation of triggers (which follows the same rules).

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. If DISTINCT is specified, then the type-name must identify a distinct type described in the catalog. The type is not dropped (SQLSTATE 42893) if any of the following are true.

The type is a distinct type 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 a data type of a typed table.
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 that cannot be dropped.
The type is used in a check constraint, trigger or view definition.

If the user-defined type can be dropped, then for every function, F, 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 FUNCTION F

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

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.

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.

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

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.

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.

Rules

Dependencies: Table 24 shows the dependencies ⁸⁷ that objects have on each other. Four different types of dependencies are shown:

R: Restrict semantics. The underlying object cannot be dropped as long as the object that depends on it exists.
C: Cascade semantics. Dropping the underlying object causes the object that depends on it (the depending object) to be dropped as well. However, if the depending object cannot be dropped because it has a Restrict dependency on some other object, the drop of the underlying object will fail.
X: Inoperative semantics. Dropping the underlying object causes the object that depends on it to become inoperative. It remains inoperative until a user takes some explicit action.
A: Automatic Invalidation/Revalidation semantics. Dropping the underlying object causes the object that depends on it to become invalid. The database manager attempts to revalidate the invalid object.

Some DROP statement parameters and objects are not shown in Table 24 because they would result in blank rows or columns:

EVENT MONITOR, PACKAGE, PROCEDURE, SCHEMA, TYPE MAPPING, and USER MAPPING DROP statements do not have object dependencies.
Alias, bufferpool, partitioning key, privilege, and procedure object types do not have DROP statement dependencies.
A DROP SERVER, DROP FUNCTION MAPPING, or DROP TYPE MAPPING statement in 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 a SELECT statement that references a nickname for a table or view within this data source (SQLSTATE 55006).
- The statement references a category of data sources (for example, all data sources of a specific type and version), and the UOW already includes a SELECT statement that references a nickname for a table or view within one of these data sources (SQLSTATE 55006).

Table 24. Dependencies

Object Type >

Statement V

C
O
N
S
T
R
A
I
N
T

F
U
N
C
T
I
O
N

F
U
N
C

M
A
P
P
I
N
G

I
N
D
E
X

N
I
C
K
N
A
M
E

N
O
D
E
G
R
O
U
P

P
A
C
K
A
G
E

S
E
R
V
E
R

T
A
B
L
E

T
A
B
L
E
S
P
A
C
E

T
R
I
G
G
E
R

T
Y
P
E

T
Y
P
E

M
A
P
P
I
N
G

U
S
E
R

M
A
P
P
I
N
G

V
I
E
W

ALTER NICKNAME ALTER COLUMN (OPTIONS(DROP) - - - - - - A - - - - - - - -
ALTER SERVER OPTIONS (DROP) - - - - - - A - - - - - - - -
ALTER TABLE DROP CONSTRAINT C - - - - - A¹ - - - - - - - -
ALTER TABLE DROP PARTITIONING KEY - - - - - R²⁰ A¹ - - - - - - - -
DROP ALIAS - - - - - - A³ - R³ - X³ - - - X³
DROP BUFFERPOOL - - - - - - - - - R - - - - -
DROP FUNCTION R R⁷ R - - - X - R - R - - - R
DROP FUNCTION MAPPING - - - - - - A - - - - - - - -
DROP INDEX R - - - - - A - - - - - - - R¹⁷
DROP NICKNAME - - - C - - A - - - - - - - X¹⁶
DROP NODEGROUP - - - - - - - - - C - - - - -
DROP SERVER - - C¹⁹ - C - A - - - - - C¹⁹ C -
DROP TABLE C - - C - - A⁹ - RC¹¹ - X¹⁶ - - - X¹⁶
DROP TABLE HIERARCHY C - - C - - A⁹ - RC¹¹ - X¹⁶ - - - X¹⁶
DROP TABLESPACE - - - C⁶ - - - - CR⁶ - - - - - -
DROP TRIGGER - - - - - - A¹ - - - - - - - -
DROP TYPE R¹³ C⁵ - - - - A¹² - R¹⁸ - R¹³ R⁴ - - R¹⁴
DROP VIEW - - - - - - A² - - - X¹⁶ - - - X¹⁵
DROP VIEW HIERACHY - - - - - - A² - - - X¹⁶ - - - X¹⁶
DROP WRAPPER - - C - - - - C - - - - C - -
REVOKE a privilege¹⁰ - - - - - - A¹ - CX⁸ - X - - - X⁸

Object Type > Statement V	C O N S T R A I N T	F U N C T I O N	F U N C M A P P I N G	I N D E X	N I C K N A M E	N O D E G R O U P	P A C K A G E	S E R V E R	T A B L E	T A B L E S P A C E	T R I G G E R	T Y P E	T Y P E M A P P I N G	U S E R M A P P I N G	V I E W
ALTER NICKNAME ALTER COLUMN (OPTIONS(DROP)	-	-	-	-	-	-	A	-	-	-	-	-	-	-	-
ALTER SERVER OPTIONS (DROP)	-	-	-	-	-	-	A	-	-	-	-	-	-	-	-
ALTER TABLE DROP CONSTRAINT	C	-	-	-	-	-	A¹	-	-	-	-	-	-	-	-
ALTER TABLE DROP PARTITIONING KEY	-	-	-	-	-	R²⁰	A¹	-	-	-	-	-	-	-	-
DROP ALIAS	-	-	-	-	-	-	A³	-	R³	-	X³	-	-	-	X³
DROP BUFFERPOOL	-	-	-	-	-	-	-	-	-	R	-	-	-	-	-
DROP FUNCTION	R	R⁷	R	-	-	-	X	-	R	-	R	-	-	-	R
DROP FUNCTION MAPPING	-	-	-	-	-	-	A	-	-	-	-	-	-	-	-
DROP INDEX	R	-	-	-	-	-	A	-	-	-	-	-	-	-	R¹⁷
DROP NICKNAME	-	-	-	C	-	-	A	-	-	-	-	-	-	-	X¹⁶
DROP NODEGROUP	-	-	-	-	-	-	-	-	-	C	-	-	-	-	-
DROP SERVER	-	-	C¹⁹	-	C	-	A	-	-	-	-	-	C¹⁹	C	-
DROP TABLE	C	-	-	C	-	-	A⁹	-	RC¹¹	-	X¹⁶	-	-	-	X¹⁶
DROP TABLE HIERARCHY	C	-	-	C	-	-	A⁹	-	RC¹¹	-	X¹⁶	-	-	-	X¹⁶
DROP TABLESPACE	-	-	-	C⁶	-	-	-	-	CR⁶	-	-	-	-	-	-
DROP TRIGGER	-	-	-	-	-	-	A¹	-	-	-	-	-	-	-	-
DROP TYPE	R¹³	C⁵	-	-	-	-	A¹²	-	R¹⁸	-	R¹³	R⁴	-	-	R¹⁴
DROP VIEW	-	-	-	-	-	-	A²	-	-	-	X¹⁶	-	-	-	X¹⁵
DROP VIEW HIERACHY	-	-	-	-	-	-	A²	-	-	-	X¹⁶	-	-	-	X¹⁶
DROP WRAPPER	-	-	C	-	-	-	-	C	-	-	-	-	C	-	-
REVOKE a privilege¹⁰	-	-	-	-	-	-	A¹	-	CX⁸	-	X	-	-	-	X⁸

¹

This dependency is implicit in depending on a table with these constraints, triggers, or a partitioning 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, summary 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, then 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 can depend on another user-defined type if the depending user-defined type:

names a user-defined type as the data type of an attribute
names a user-defined structured type within a reference type as the data type of an attribute
has a user-defined type as a supertype.

⁵

Dropping a data type cascades to drop the functions that use that data type. Dropping of these functions will not be prevented by the fact that they depend on each other.

⁶

Dropping a table space or a list of table spaces causes all the tables that are completely contained within the given tablespace or list to be dropped. However, if a table spans table spaces (indexes or long columns in different table spaces) and those table spaces are not in the list being dropped then the table space(s) 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.

⁸

Only loss of SELECT privilege will cause a summary 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 summary table is dependent on the underlying table or tables specified in the fullselect of the table definition.

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

¹²

A package can depend on structured types as a result of using the TYPE predicate. The package has a dependency on the subtypes of each structured type specified in the right side of the TYPE predicate. Dropping or creating a structured type that alters the subtypes on which the package is dependent causes invalidation.

¹³

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 because:

it is used as the type of a column
it is used as the type of the table
it is used as an attribute of a type of the table
it is 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.

¹⁹

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

²⁰

If the partitioning key is defined on a table in a multiple partition nodegroup, the partitioning key is required.

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.
For any dropped table that includes currently linked files through DATALINK columns, the files are unlinked, and will be either restored or deleted, depending on the datalink column definition.
If a table containing a DATALINK column is dropped while any DB2 Data Links Managers configured to the database are unavailable, either through DROP TABLE or DROP TABLESPACE, then the operation will fail (SQLSTATE 57050).

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 nodegroup. To drop the nodegroup, the two table spaces (ACCOUNTING and PLANS) in the nodegroup must first be dropped.

   DROP TABLESPACE ACCOUNTING
   DROP TABLESPACE PLANS
   DROP NODEGROUP 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 DISTINCT 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 to identify the procedure instance to be dropped.

  DROP SPECIFIC PROCEDURE  EIGLER.DESTROY

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

Footnotes:

⁸⁴: This includes both the table referenced in the ON clause of the CREATE TRIGGER statement and all tables referenced within the triggered SQL statements.
⁸⁵: For a partially redistributed nodegroup, the REBALANCE_PMAP_ID in the SYSCAT.NODEGROUPS catalog is not 1.
⁸⁶: This includes both the table referenced in the ON clause of the CREATE TRIGGER statement and all tables referenced within the triggered SQL statements.
⁸⁷: Not all dependencies are explicitly recorded in the catalog. For example, there is no record of which constraints a package has a dependency on.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]