The DROP statement drops an object. Objects that are directly or indirectly dependent on that object may also be dropped.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
To drop a table, view, index, alias, or package, the privileges held by the authorization ID of the statement must include at least one of the following:
To drop a schema, the privileges held by the authorization ID of the statement must include at least one of the following:
To drop a user-defined type, the privileges held
by the authorization ID of the statement must include at least one
of the following: 
To drop a global variable, the privileges held
by the authorization ID of the statement must include at least one
of the following: 

To drop an XSR object, the privileges held by the
authorization ID of the statement must include at least one of the
following: 

To drop a function, the privileges held by the authorization ID of the statement must include at least one of the following:
To drop a procedure, the privileges held by the authorization ID of the statement must include at least one of the following:
To drop a sequence, the privileges held by the authorization ID of the statement must include at least one of the following:
To drop a trigger, the privileges held by the authorization ID of the statement must include at least one of the following:
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.
>>-DROP--+-ALIAS--alias-name-----------------------------------------------------------------+->< +-+-+-FUNCTION-+--function-name--+------------------------------+-+--+----------+---+ | | '-ROUTINE--' '-(--+--------------------+--)-' | '-RESTRICT-' | | | | .-,--------------. | | | | | | V | | | | | | '---parameter-type-+-' | | | '-SPECIFIC--+-FUNCTION-+--specific-name-------------------------' | | '-ROUTINE--' | +-INDEX--index-name-----------------------------------------------------------------+ +-PACKAGE--package-name--+-------------------------+--------------------------------+ | | .-VERSION-. | | | '-+---------+--version-id-' | +-+-+-PROCEDURE-+--procedure-name--+------------------------------+-+--+----------+-+ | | '-ROUTINE---' '-(--+--------------------+--)-' | '-RESTRICT-' | | | | .-,--------------. | | | | | | V | | | | | | '---parameter-type-+-' | | | '-SPECIFIC--+-PROCEDURE-+--specific-name--------------------------' | | '-ROUTINE---' | +-SCHEMA--schema-name--+----------+-------------------------------------------------+ | +-RESTRICT-+ | | '-CASCADE--' | | .-RESTRICT-. | +-SEQUENCE--sequence-name--+----------+---------------------------------------------+ +-TABLE--table-name--+----------+---------------------------------------------------+ | +-RESTRICT-+ | | '-CASCADE--' | +-TRIGGER--trigger-name-------------------------------------------------------------+ +-TYPE--+-distinct-type-name-+--+----------+----------------------------------------+ | '-array-type-name----' +-RESTRICT-+ | | '-CASCADE--' | +-VARIABLE--variable-name--+----------+---------------------------------------------+ | +-RESTRICT-+ | | '-CASCADE--' | +-VIEW--view-name--+----------+-----------------------------------------------------+ | +-RESTRICT-+ | | '-CASCADE--' | '-XSROBJECT--xsrobject-name---------------------------------------------------------'
data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
built-in-type |--+-+---SMALLINT---+----------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--52--)------. | +-+-FLOAT--+---------------+-+----------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+--------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+-----------------+-+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '---+-+-CHARACTER-+--LARGE OBJECT-+----+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+----------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+--------+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+-----------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | +---XML--+--------------+---------------------------------------------------------------+ | '-ccsid-clause-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+--------------+---------------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | +---ROWID-------------------------------------------------------------------------------+ '---XML---------------------------------------------------------------------------------' ccsid-clause |--CCSID--integer-----------------------------------------------|
The specified
alias is deleted from the schema. Dropping an alias has no effect
on any constraint, view, or materialized query that was defined using
the alias. An alias can be dropped whether it is referenced in a function,
package, procedure, program, trigger, or variable.
Functions implicitly generated by the CREATE TYPE statement cannot be dropped using the DROP statement. They are implicitly dropped when the distinct type is dropped.
The
function cannot be dropped if another function is dependent on it.
A function is dependent on another function if it was identified in
the SOURCE clause of the CREATE FUNCTION statement. A function can
be dropped whether it is referenced in a function, package, procedure,
program, trigger, variable, or view unless RESTRICT is specified
The specified function is dropped from the schema. All privileges on the user-defined function are also dropped. If this is an SQL function or sourced function, the service program (*SRVPGM) associated with the function is also dropped. If this is an external function, the information that was saved in the program or service program specified on the CREATE FUNCTION statement is removed from the object.
If function-name () is specified, the function identified must have zero parameters.
If an unqualified distinct type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type.
For data types that have a length, precision, or scale attribute, use one of the following:
Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.
Specifies that the function is defined to receive a
locator for this parameter. If AS LOCATOR is specified, the data type
must be a LOB or XML or a distinct type based on a LOB or XML.
Specifies
that the function cannot be dropped if it is referenced in an SQL
function, SQL procedure, table, SQL trigger, variable, or view. 
The specified index is dropped from the schema. An index can be dropped whether it is referenced in a function, package, procedure, program, or trigger.
The specified package is dropped from the schema. All privileges on the package are also dropped.
A package can be dropped whether it is referenced in a function, package, procedure, program, or trigger.
The specified procedure
is dropped from the schema. All privileges on the procedure are also
dropped. If this is an SQL procedure, the program (*PGM) or service
program (*SRVPGM) associated with the procedure is also dropped. If
this is an external procedure, the information that was saved in the
program specified on the CREATE PROCEDURE statement is removed from
the object.
A procedure can be dropped whether it
is referenced in a function, package, procedure, program, or trigger.
If procedure-name () is specified, the procedure identified must have zero parameters.
If
an unqualified distinct type or array type name is specified, the
database manager searches the SQL path to resolve the schema name
for the distinct type or array type.
For data types that have a length, precision, or scale attribute, use one of the following:
Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE PROCEDURE statement.
Specifies that the procedure is defined to receive
a locator for this parameter. If AS LOCATOR is specified, the data
type must be a LOB or XML or a distinct type based on a LOB or XML.
The specified schema is dropped. Each object in the schema is dropped as if the appropriate DROP statement was executed with the specified drop option (CASCADE, RESTRICT, or neither). See the DROP description of these object types for information about the handling of objects dependent on these objects.
DROP SCHEMA is only valid when the commit level is *NONE.
Specifies that the schema will be dropped even if it
is referenced in a function, package, procedure, program, table, trigger,
or variable in another schema.
Specifies
that the sequence cannot be dropped if it is referenced in an SQL
trigger, function, procedure, or variable.
The specified table is dropped from the schema. All privileges, constraints, indexes, and triggers on the table are also dropped.
Any aliases that reference the specified table are not dropped.
Specifies that the table will be dropped even if
it is referenced in a constraint, index, trigger, view, or materialized
query table. All indexes, views, and materialized query tables that
reference the table are dropped even if the authorization ID of the
statement does not explicitly have privileges to those objects.
Specifies
that the table will be dropped even if it is referenced in a constraint,
index, trigger, variable, view, XSR object, or materialized query
table. All constraints, indexes, triggers, variables, views, XSR objects
and materialized query tables that reference the table are dropped
even if the authorization ID of the statement does not explicitly
have privileges to those objects.
Specifies
that the table cannot be dropped if it is referenced in a constraint,
index, trigger, variable, view, XSR object, or materialized query
table.
The specified trigger is dropped from the schema. If the trigger is an SQL trigger, the program object associated with the trigger is also deleted from the schema.
If trigger-name specifies an INSTEAD OF trigger on a view, another trigger may depend on that trigger through an update against the view.
The distinct-type-name or array-type-name must
identify a distinct type or array type that exists at the current
server.
The specified type is deleted from the schema.
Specifies that the type cannot be dropped if any constraints,
indexes, sequences, tables, variables, and views reference the type.
For every procedure or function R that
has parameters or a return value of the type being dropped, the following
DROP statement is effectively executed: 
DROP ROUTINE R
For every trigger T that references the type being dropped, the following DROP statement is effectively executed:
DROP TRIGGER T
It is possible that this statement would cascade to drop
dependent functions or procedures. If all of these functions or procedures
are in the list to be dropped because of a dependency on the type,
the drop of the type will succeed.

Specifies
that the type will be dropped even if it is referenced in a constraint,
function, index, procedure, sequence, table, trigger, variable, or
view. All constraints, functions, indexes, procedures, sequences,
tables, triggers, variables, and views that reference the type are
dropped.
Specifies
that the type cannot be dropped if it is referenced in a constraint,
function (other than a function that was created when the type was
created), index, procedure, sequence, table, trigger, variable, or
view.
VARIABLE variable-name 
Identifies
the variable that is to be dropped. The variable-name must
identify a variable that exists at the current server. The specified
variable is dropped from the schema. 
The specified view is dropped from the schema. When a view is dropped, all privileges and triggers on that view are dropped.
Specifies that the view will be dropped even if
it is referenced in a trigger, materialized query table, or another
view. All views and materialized query tables that reference the view
are dropped.
Specifies
that the view will be dropped even if it is referenced in a trigger,
variable, materialized query table, or another view. All triggers,
variables, materialized query tables, and views that reference the
view are dropped.
Specifies
that the view cannot be dropped if it is referenced in a trigger,
variable, materialized query table, or another view.
XSROBJECT xsrobject-name 
Identifies
the XSR object that is to be dropped. The xsrobject-name must
identify an XSR object that exists at the current server. The specified
XSR object is dropped. 
Drop effects: Whenever an object is dropped, its description is dropped from the catalog. If the object is referenced in a function, package, procedure, program, trigger, or variable; any access plans that reference the object are implicitly prepared again when the access plan is next used. If the object does not exist at that time, an error is returned.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
Example 1: Drop your table named MY_IN_TRAY. Do not allow the drop if any views or indexes are created over this table.
DROP TABLE MY_IN_TRAY RESTRICT
Example 2: Drop your view named MA_PROJ.
DROP VIEW MA_PROJ
Example 3: Drop the package named PERS.PACKA.
DROP PACKAGE PERS.PACKA
Example 4: Drop the distinct type DOCUMENT, if it is not currently in use:
DROP DISTINCT TYPE DOCUMENT RESTRICT
Example 5: Assume that you are SMITH and that ATOMIC_WEIGHT is the only function with that name in schema CHEM. Drop ATOMIC_WEIGHT.
DROP FUNCTION CHEM.ATOMIC_WEIGHT RESTRICT
Example 6: Drop the function named CENTER, using the function signature to identify the function instance to be dropped.
DROP FUNCTION CENTER (INTEGER, FLOAT) RESTRICT
Example 7: Drop CENTER, using the specific name to identify the function instance to be dropped.
DROP SPECIFIC FUNCTION JOHNSON.FOCUS97
Example 8: Assume that procedure OSMOSIS is in schema BIOLOGY. Drop OSMOSIS.
DROP PROCEDURE BIOLOGY.OSMOSIS
Example 9: Assume that trigger BONUS exists in the default schema. Drop BONUS.
DROP TRIGGER BONUS