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 or an error will result (SQLSTATE 42501):
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.
The authorization ID of the DROP statement when dropping a transform must hold SYSADM or DBADM authority, or must be the DEFINER of type-name.
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-------------------------------------------------------+
+-INDEX EXTENSION--index-extension-name--RESTRICT-------------------------+
+-METHOD--method-name--+------------------------------+---FOR--type-name--+
| '-(--+------------------+---)--' |
| | .-,----------. | |
| | V | | |
| '----datatype---+--' |
+-SPECIFIC METHOD--specific-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-' |
+--+-TRANSFORM--+---+-ALL--------+---FOR--type-name-----------------------+
| '-TRANSFORMS-' '-group-name-' |
+-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:
Description
All tables, views and triggers 93 that reference the alias are made inoperative.
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.
There are several different ways available to identify the function instance:
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.
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.
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.
Packages having a dependency on a dropped index or index specification are invalidated.
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.
There are several ways available to identify the method body to be dropped:
If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path.
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 TYPE 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 method with the specified signature exists for the named data type, an error is raised (SQLSTATE 42883).
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. See Command Reference for information on these commands.
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.
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. 94
There are several different ways available to identify the procedure instance:
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 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.
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 indexes, primary keys, foreign keys, check constraints, and summary tables referencing the table are dropped. All views and triggers 95 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.
Packages are not dependent on declared temporary tables, and therefore are not invalidated when such a table is dropped.
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.
When a 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 nodegroup of the USER TEMPORARY table space from being redistributed until the unit of work is committed or savepoint is ended. Dropping a declared temporary table causes the data in the table to be destroyed, regardless of whether DROP is committed or rolled back.
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.
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 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 a declared temporary table created in it (SQLSTATE 55039). 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 has been committed.
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. For SMS table spaces, the deletions occur after all connections are disconnected or the DEACTIVATE DATABASE command is issued.
If there are not transforms defined for type-name, an error is raised (SQLSTATE 42740).
DROP TRANSFORM is the inverse of CREATE TRANSFORM. It causes the transform functions associated with certain groups, for a given datatype, 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.
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).
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 to fail.
No additional objects are dropped.
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.
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.
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.
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.
Dependencies: Table 27 shows the dependencies 96 that objects have on each other. Four different types of dependencies are shown:
Some DROP statement parameters and objects are not shown in Table 27 because they would result in blank rows or columns:
|
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
|
I N D E X |
I N D E X
|
M E T H O D |
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
|
U S E R
|
V I E W |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ALTER NICKNAME | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - |
| ALTER SERVER | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - |
| ALTER TABLE DROP CONSTRAINT | C | - | - | - | - | - | - | - | A1 | - | - | - | - | - | - | - | - |
| ALTER TABLE DROP PARTITIONING KEY | - | - | - | - | - | - | - | R20 | A1 | - | - | - | - | - | - | - | - |
| ALTER TYPE ADD ATTRIBUTE | - | - | - | - | R | - | - | - | A23 | - | R24 | - | - | - | - | - | R14 |
| ALTER TYPE DROP ATTRIBUTE | - | - | - | - | R | - | - | - | A23 | - | R24 | - | - | - | - | - | R14 |
| ALTER TYPE ADD METHOD | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
| ALTER TYPE DROP METHOD | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
| DROP ALIAS | - | R | - | - | - | - | - | - | A3 | - | R3 | - | X3 | - | - | - | X3 |
| DROP BUFFERPOOL | - | - | - | - | - | - | - | - | - | - | - | R | - | - | - | - | - |
| DROP FUNCTION | R | R7 | R | - | R | R7 | - | - | X | - | R | - | R | - | - | - | R |
| DROP FUNCTION MAPPING | - | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | - |
| DROP INDEX | R | - | - | - | - | - | - | - | A | - | - | - | - | - | - | - | R17 |
| DROP INDEX EXTENSION | - | R | - | R | - | - | - | - | - | - | - | - | - | - | - | - | - |
| DROP METHOD | R | R7 | R | - | R | R | - | - | X | - | R | - | R | - | - | - | R |
| DROP NICKNAME | - | R | - | C | - | - | - | - | A | - | - | - | - | - | - | - | X16 |
| DROP NODEGROUP | - | - | - | - | - | - | - | - | - | - | - | C | - | - | - | - | - |
| DROP SERVER | - | C21 | C19 | - | - | - | C | - | A | - | - | - | - | - | C19 | C | - |
| DROP TABLE | C | R | - | C | - | - | - | - | A9 | - | RC11 | - | X16 | - | - | - | X16 |
| DROP TABLE HIERARCHY | C | R | - | C | - | - | - | - | A9 | - | RC11 | - | X16 | - | - | - | X16 |
| DROP TABLESPACE | - | - | - | C6 | - | - | - | - | - | - | CR6 | - | - | - | - | - | - |
| DROP TRANSFORM | - | R | - | - | - | - | - | - | X | - | - | - | - | - | - | - | - |
| DROP TRIGGER | - | - | - | - | - | - | - | - | A1 | - | - | - | - | - | - | - | - |
| DROP TYPE | R13 | R5 | - | - | R | - | - | - | A12 | - | R18 | - | R13 | R4 | - | - | R14 |
| DROP VIEW | - | R | - | - | - | - | - | - | A2 | - | - | - | X16 | - | - | - | X15 |
| DROP VIEW HIERARCHY | - | R | - | - | - | - | - | - | A2 | - | - | - | X16 | - | - | - | X16 |
| DROP WRAPPER | - | - | C | - | - | - | - | - | - | C | - | - | - | - | C | - | - |
| REVOKE a privilege10 | - | CR25 | - | - | - | - | - | - | A1 | - | CX8 | - | X | - | - | - | X8 |
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.
Aliases themselves are not dependent on anything. It is possible for an alias to be defined on an object that does not exist.
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.
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.
Cascade semantics apply to dependent summary tables.
A subtable is dependent on its supertables up to the root table. A supertable cannot be dropped until all its subtables are dropped.
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()
Notes
Since the above describes 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).
Examples
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
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