The DROP statement deletes an object. Any objects that are directly
or indirectly dependent on that object are also deleted. Whenever an
object is deleted, its description is deleted from the catalog.
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:
- The following system authorities:
- The system authorities of *OBJOPR and *OBJEXIST on the object to be
dropped
- If the object is a table or view, the system authorities of *OBJOPR and
*OBJEXIST on any views, indexes, and logical files that are dependent on that
table or view
- The system authority *EXECUTE on the library that contains the object to
be dropped
- Administrative authority
To drop a collection, the privileges held by the authorization ID of the
statement must include at least one of the following:
- The following system authorities:
- The system authorities of *OBJEXIST, *OBJOPR, *EXECUTE, and *READ on the
library to be dropped.
- The system authorities of *OBJOPR and *OBJEXIST on all objects in the
collection and *OBJOPR and *OBJEXIST on any views, indexes and logical files
that are dependent on tables and views in the collection.
- Any additional authorities required to delete other object types that
exist in the collection. For example, *OBJMGT to the data dictionary if
the collection contains a data dictionary, and some system data authority to
the journal receiver. For more information, see the Security - Reference, SC41-5302-04 book.
- Administrative authority
To drop a user-defined type, the privileges held by the authorization ID of
the statement must include at least one of the following:
- The following system authorities:
- The system authorities of *OBJOPR and *OBJEXIST on the user-defined type
to be dropped
- The DELETE privilege on the SYSTYPES, SYSPARMS, and SYSROUTINES catalog
tables, and
- The system authority *EXECUTE on library QSYS2
- Administrative authority
To drop a function, the privileges held by the authorization ID of the
statement must include at least one of the following:
- The following system authorities:
- For SQL functions, the system authority *OBJEXIST on the program object
associated with the function, and
- The DELETE privilege on the SYSFUNCS and SYSPARMS catalog tables, and
- The system authority *EXECUTE on library QSYS2
- Administrative authority
To drop a procedure, the privileges held by the authorization ID of the
statement must include at least one of the following:
- The following system authorities:
- For SQL procedures, the system authority *OBJEXIST on the program object
associated with the procedure, and
- The DELETE privilege on the SYSPROCS and SYSPARMS catalog tables, and
- The system authority *EXECUTE on library QSYS2
- Administrative authority
The authorization ID of the statement has the DELETE privilege on a table
when:
- It is the owner of the table,
- It has been granted the DELETE privilege on the table, or
- It has been granted the system authorities of *OBJOPR and *DLT on the
table.
(1)
>>-DROP--+-ALIAS-------alias-name--------------------------------------------------+->
| (2) |
+--+-COLLECTION-+---------collection-name--drop-option--------------------+
| '-SCHEMA-----' |
+-+---------------+---TYPE--type-name--+----------+-----------------------+
| | (3) | +-RESTRICT-+ |
| '-DISTINCT------' '-CASCADE--' |
+--+-FUNCTION-+--function-name--+------------------------------------+----+
| '-ROUTINE--' '-(--+------------------------+---)--' |
| | .-,----------------. | |
| | V | | |
| '----parameter-type---+--' |
+-INDEX--index-name-------------------------------------------------------+
| (4) |
+-PACKAGE-------package-name----------------------------------------------+
+--+-PROCEDURE-+---procedure-name--+------------------------------------+-+
| '-ROUTINE---' '-(--+------------------------+---)--' |
| | .-,----------------. | |
| | V | | |
| '----parameter-type---+--' |
+-SPECIFIC--+-FUNCTION--+---specific-name---------------------------------+
| +-PROCEDURE-+ |
| '-ROUTINE---' |
+-TABLE--table-name--drop-option------------------------------------------+
'-VIEW--view-name--drop-option--------------------------------------------'
>--------------------------------------------------------------><
drop-option
.-CASCADE--.
|---+----------+------------------------------------------------|
'-RESTRICT-'
Notes:
- The keyword SYNONYM can be used as a synonym for ALIAS.
- The keyword DATABASE can be used as a synonym for COLLECTION or
SCHEMA.
- The keyword DATA can be used when dropping any user-defined-type.
- The keyword PROGRAM can be used as a synonym for PACKAGE.
|
parameter-type
|--+---BIGINT-------------------------------------------------------------------------------------------------------+->
+-+-INTEGER-+----------------------------------------------------------------------------------------------------+
| '-INT-----' |
+---SMALLINT-----------------------------------------------------------------------------------------------------+
+--+-DECIMAL-+---+------------------------------------+----------------------------------------------------------+
| +-DEC-----+ | (1) | |
| '-NUMERIC-' '-(--integer--+-----------+--)-------' |
| '-, integer-' |
+-+-FLOAT--+---------------------+-+-----------------------------------------------------------------------------+
| | | (1) | | |
| | '-(--integer--)-------' | |
| +-REAL---------------------------+ |
| '-DOUBLE--+-----------+----------' |
| '-PRECISION-' |
+----+-BLOB----------------+-----+-----------------------------+---+------------+--------------------------------+
| '-BINARY LARGE OBJECT-' | (1) | '-AS LOCATOR-' |
| '-(--integer--+---+---)-------' |
| +-K-+ |
| '-M-' |
+--+--+--+-CHARACTER-+---+---------------------+------------------+---+-----------------+----------------------+-+
| | | '-CHAR------' | (1) | | +-FOR BIT DATA----+ | |
| | | '-(--integer--)-------' | +-FOR SBCS DATA---+ | |
| | | (1) | +-FOR MIXED DATA--+ | |
| | '-----+-VARCHAR-------------------+---(--integer--)---------' '-CCSID--integer--' | |
| | '--+-CHARACTER-+---VARYING--' | |
| | '-CHAR------' | |
| '----+-CLOB-------------------+-----+-----------------------------+---+-----------------+---+------------+--' |
| +-CHAR LARGE OBJECT------+ | (1) | +-FOR BIT DATA----+ '-AS LOCATOR-' |
| '-CHARACTER LARGE OBJECT-' '-(--integer--+---+---)-------' +-FOR SBCS DATA---+ |
| +-K-+ +-FOR MIXED DATA--+ |
| '-M-' '-CCSID--integer--' |
+--+--+----GRAPHIC-----+---------------------+----------+---+-----------------+-------------+--------------------+
| | | | (1) | | '-CCSID--integer--' | |
| | | '-(--integer--)-------' | | |
| | | (1) | | |
| | '-----+-VARGRAPHIC------+---(--integer--)---------' | |
| | '-GRAPHIC VARYING-' | |
| '----DBCLOB------+-----------------------------+---+-----------------+---+------------+--' |
| | (1) | '-CCSID--integer--' '-AS LOCATOR-' |
| '-(--integer--+---+---)-------' |
| +-K-+ |
| '-M-' |
+---DATE---------------------------------------------------------------------------------------------------------+
+---TIME---------------------------------------------------------------------------------------------------------+
+---TIMESTAMP----------------------------------------------------------------------------------------------------+
+---DATALINK--+---------------------+---+-----------------+------------------------------------------------------+
| | (1) | '-CCSID--integer--' |
| '-(--integer--)-------' |
'-distinct-type--+-----------------+-----------------------------------------------------------------------------'
| (2) |
'-AS LOCATOR------'
>---------------------------------------------------------------|
Notes:
- The values that are specified for length, precision, or scale attributes
must match the values that were specified when the function was
created. Coding specific values is optional. Empty parentheses,
(), can be used instead to indicate that DB2 ignores the attributes when
determining whether data types match.
- AS LOCATOR can be specified only for distinct type based on a LOB data
type.
|
- ALIAS alias-name
- Identifies the alias you want to drop. The alias-name
must identify an alias that exists at the current server. The specified
alias is deleted from the collection or library. Aliases can be dropped
at any time except when they are in use.
Any access plans that reference the alias are implicitly prepared again
when a program that uses the access plan is next run.
- COLLECTION or SCHEMA collection-name
- Identifies the collection you want to drop. The
collection-name must identify a collection that exists at the
current server. The specified collection is deleted. All objects
in the collection are dropped.
Any access plans that reference any object in the collection are implicitly
prepared again when a program that uses the access plan is next run. If
the referenced object does not exist at that time, a negative value is
returned in the SQLCODE field of the SQLCA.
DROP COLLECTION is only valid when the commit level is *NONE.
- TYPE type-name
- Identifies the user-defined type you want to drop. The
type-name must identify a user-defined type that exists at the
current server. The specified type is deleted from the collection or
library.
If other objects are dependent on the user-defined type, the effect depends
on the drop option:
- FUNCTION
- Identifies the function you want to drop. You can identify the
particular function to be dropped by its name, function signature, or specific
name. The rules for function resolution (and the path) are not
used. Functions implicitly generated by the CREATE DISTINCT TYPE
statement cannot be dropped.
- FUNCTION function-name
- The function-name must identify exactly one function that
exists at the current server. Functions cannot be dropped if other
functions are sourced on them. The function may have any number of
parameters defined for it. If there is more than one function of the
specified name in the specified or implicit collection, an error is
returned.
- FUNCTION function-name (parameter-type, ...)
- The function-name (parameter-type, ...)
must identify a function with the specified function signature that exists at
the current server. The specified parameters 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 function-name () is specified, the function
identified must have zero parameters.
If an unqualified distinct type name is specified, DB2 searches the SQL
path to resolve the collection name for the distinct type.
For data types that have a length, precision, or scale attribute, you can
specify a value or use a set of empty parentheses.
- Empty parentheses indicate that DB2 ignores the attribute when determining
whether the data types match.
- If you use a specific value for a length, precision, or scale attribute,
the value must exactly match the value that was specified (implicitly or
explicitly) in the CREATE FUNCTION statement.
- If length, precision, or scale is not explicitly specified, and empty
parentheses are not specified, the default attributes of the data type are
implied. For more information on the default attributes see CREATE TABLE.
For data types with a subtype or CCSID attribute, specifying the FOR DATA
clause or CCSID clause is optional. Omission of either clause indicates
that DB2 ignores the attribute when determining whether the data types
match. If you specify either clause, it must match the value that was
implicitly or explicitly specified in the CREATE FUNCTION statement.
- SPECIFIC FUNCTION specific-name
- The specific-name must identify a specific function that exists
at the current server.
The specified function is deleted from the catalog tables SYSFUNCS and
SYSPARMS. 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. All privileges on the function are also dropped.
- INDEX index-name
- Identifies the index you want to drop. The index-name
must identify an index that exists at the current server. The specified
index is deleted from the collection or library. Indexes can be dropped
at any time except when they are in use.
Any access plans that reference the index are implicitly prepared again
when a program that uses the access plan is next run.
- PACKAGE package-name
- Identifies the package you want to drop. The
package-name must identify a package that exists at the current
server. The specified package is deleted from the collection or
library. All privileges on the package are also dropped.
- PROCEDURE
- Identifies the procedure you want to drop. You can identify the
particular procedure to be dropped by its name, procedure signature, or
specific name. The rules for procedure resolution (and the path) are
not used.
- PROCEDURE procedure-name
- The procedure-name must identify exactly one procedure that
exists at the current server. The procedure may have any number of
parameters defined for it. If there is more than one procedure of the
specified name in the specified or implicit collection, an error is
returned.
- PROCEDURE procedure-name (parameter-type,
...)
- The procedure-name (parameter-type, ...)
must identify a procedure with the specified procedure signature that exists
at the current server. The specified parameters 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 procedure-name () is
specified, the procedure identified must have zero parameters.
If an unqualified distinct type name is specified, DB2 searches the SQL
path to resolve the collection name for the distinct type.
For data types that have a length, precision, or scale attribute, you can
specify a value or use a set of empty parentheses.
- Empty parentheses indicate that DB2 ignores the attribute when determining
whether the data types match.
- If you use a specific value for a length, precision, or scale attribute,
the value must exactly match the value that was specified (implicitly or
explicitly) in the CREATE PROCEDURE statement.
- If length, precision, or scale is not explicitly specified, and empty
parentheses are not specified, the default attributes of the data type are
implied. For more information on the default attributes see CREATE TABLE.
For data types with a subtype or CCSID attribute, specifying the FOR DATA
clause or CCSID clause is optional. Omission of either clause indicates
that DB2 ignores the attribute when determining whether the data types
match. If you specify either clause, it must match the value that was
implicitly or explicitly specified in the CREATE PROCEDURE statement.
- SPECIFIC PROCEDURE specific-name
- The specific-name must identify a specific procedure that
exists at the current server.
The specified procedure is deleted from the catalog tables SYSPROCS and
SYSPARMS. If this is an SQL procedure, the program (*PGM) object
associated with the SQL procedure is also dropped. All privileges on
the procedure are also dropped.
- TABLE table-name
- Identifies the table you want to drop. The table-name
must identify a base table that exists at the current server, but must not
identify a catalog table. The specified table is deleted from the
collection or library. All privileges, constraints, indexes, triggers,
views, and logical files on the table are also dropped.
Any access plans that reference the table will be prepared again when a
program that contains the access plan is next run. If the referenced
table does not exist at that time, a negative value will be returned in the
SQLCODE field of the SQLCA.
- VIEW view-name
- Identifies the view you want to drop. The view-name must
identify a view that exists at the current server, but must not identify a
catalog view. The specified view is deleted from the collection or
library. Any view that is directly or indirectly dependent on that view
is also deleted. When a view is dropped, all privileges on that view
are dropped.
Any access plans that reference the view are implicitly prepared again when
a program that uses the access plan is next run. If the referenced view
does not exist at that time, a negative value will be returned in the SQLCODE
field of the SQLCA.
- CASCADE
- Specifies that any tables, views, indexes, files, or referential
constraints that are dependent on the object being dropped are also
dropped.
- RESTRICT
- Specifies that the object cannot be dropped if any views, logical files,
indexes, or constraints are dependent on the object being dropped. If
the object being dropped is a collection, this includes all dependencies on
the tables and views in the collection.
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
Drop your view named MA_PROJ.
DROP VIEW MA_PROJ
Drop the package named PERS.PACKA.
DROP PACKAGE PERS.PACKA
Drop the distinct type DOCUMENT, if it is not currently in use:
DROP DISTINCT TYPE DOCUMENT RESTRICT
Assume that you are SMITH and that ATOMIC_WEIGHT is the only function
with that name in collection CHEM. Drop ATOMIC_WEIGHT.
DROP FUNCTION CHEM.ATOMIC_WEIGHT RESTRICT
Assume that you are SMITH and that you created the function CENTER in
collection SMITH. Drop CENTER, using the function signature to identify
the function instance to be dropped.
DROP FUNCTION CENTER (INTEGER, FLOAT) RESTRICT
Assume that you are SMITH and that you created another function named
CENTER, which you gave the specific name FOCUS97, in schema JOHNSON.
Drop CENTER, using the specific name to identify the function instance to be
dropped.
DROP SPECIFIC FUNCTION JOHNSON.FOCUS97
Assume that you are SMITH and that stored procedure OSMOSIS is in
collection BIOLOGY. Drop OSMOSIS.
DROP PROCEDURE BIOLOGY.OSMOSIS
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]