DROP

Note:

With the exception of removed storage groups, any objects directly or indirectly dependent on the deleted object are also removed.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The XDB Server checks to ensure that users dropping different kinds of objects possess the following privileges as appropriate:

To drop a table, table space, or index, users must possess either DBADM for the database, SYSADM or SYSCTRL authority for the location, or be the owner of the object.
To drop an alias, storage group or view, users must possess either SYSADM or SYSCTRL authority or be the owner of the object.
To drop a database, users must possess either SYSADM or SYSCTRL authority for the location, DBADM or DBCTRL authority for the database or the DROP privilege on the database.
To drop a synonym, users must be owners of the synonym.

To drop a distinct type, stored procedure, trigger, or user-defined function, users must possess ownership of the object, the DROPIN privilege for the schema or all schemas, or SYSADM or SYSCTRL authority.

The DROP statement has access to the same privileges held by the combined AuthIDs of the application process.

Syntax

Bold text indicates clauses or options that are supported only syntactically.

DROP
{
    ALIAS alias-name
    | DATABASE database-name
    | DISTINCT TYPE distinct-type-name RESTRICT
    | FUNCTION function-name (parameter-type[,...]) RESTRICT
    | INDEX index-name
    | SEQUENCE sequence-name
    | STOGROUP stogroup-name
    | LOCATION location-name 
    | SYNONYM synonym
    | TABLE table-name
    | TABLESPACE [database-name.]tablespace-name
    | VIEW view-name
    | {PACKAGE | PROGRAM} collection-id.package.id[[VERSION] version-id]
    | PROCEDURE procedure-name RESTRICT
    | SPECIFIC FUNCTION specific-name RESTRICT
    | TRIGGER trigger-name
}

parameter-type:

{data-type [AS LOCATOR]}

data-type:

{built-in-data-type | distinct-type-name}

built-in-data-type:

SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | 
REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] |     
    {CHARACTER | CHAR} VARYING (integer) | 
    VARCHAR (integer) } [FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] } 
    [FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] |
{DATE | TIME | TIMESTAMP} |
ROWID

Parameters:

`alias-name`	Identifies the alias to be dropped. The name must identify an alias that exists at the current server. Dropping an alias has no effect on any view, materialized query table, or synonym that was defined using the alias.
`database-name`	Identifies the database to be dropped.
`location-name`	Identifies a user-created location to be dropped.
`index-name`	Identifies the index to be dropped.
`stogroup-name`	Identifies the storage group to be dropped.
`synonym`	Identifies the synonym to be dropped.
`table-name`	Identifies the table to be dropped.
[`database-name`.]`tablespace-name`	Identifies the table space (optionally qualified with a database name) to be dropped.
`view-name`	Identifies the view to be dropped.

Description

DELETE command rules do not apply when using the DROP command. When dropping a table, for instance, parent and dependent table relationships are not a consideration.

If the user-defined storage group for a database is dropped, then that database no longer has an active default storage group. A USING clause must be specified when creating future table space or indexes within that database. The USING clause must be specified until either another storage group is created with the same name (using CREATE STOGROUP) or another default storage group is designated for the database (using ALTER DATABASE).

ALIAS

This keyword indicates that an alias is to be dropped. The alias must exist at the current location. Dropping an alias does not affect any view or synonym previously defined using the dropped alias.

DATABASE

The keyword indicates that a database is to be dropped. This database must not be a system database (DSNDB04, DSNDB06, DSNDDF, XDBACF, etc.), and must exist at the current location. The database, along with all table spaces, tables and indexes associated with the database are dropped.

DISTINCT TYPE distinct-type-name RESTRICT

Identifies the distinct type to be dropped. The name must identify a distinct type that exists at the current server. The default keyword RESTRICT indicates that the distinct type is not dropped if any of the following dependencies exist:

The definition of a column of a table uses the distinct type.
The definition of an input or result parameter of a user-defined function uses the distinct type.
The definition of a parameter of a stored procedure uses the distinct type.
A sequence exists for which the data type of the sequence is the distinct type.

Whenever a distinct type is dropped, all privileges on the distinct type are also dropped. In addition, the cast functions that were generated when the distinct type was created and the privileges on those cast functions are also dropped.

FUNCTION

Identifies the user-defined function to be dropped. The name must identify a function that has been defined with the CREATE FUNCTION statement at the current server. The name must not identify a cast function that was generated for a distinct type or a function that is in the SYSIBM schema. The required keyword RESTRICT enforces the rule that the function is not dropped if any of the following dependencies exist:

Another function is sourced on the function.
A view uses the function.
A trigger package uses the function.

Whenever a function is dropped, all privileges on the user-defined function are also dropped. Any plans or packages that are dependent on the function dropped are made inoperative.

You can identify the particular function to be dropped by its name, function signature, or specific name. If the function was defined with a table parameter (the LIKE TABLE was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), the function signature cannot be used to identify the function. Instead, identify the function with its function name, if unique, or with its specific name.

FUNCTION `function-name`RESTRICT	Identifies the function by its name. There must be exactly one function with function-name in the implicitly or explicitly specified schema; otherwise, an error occurs.
FUNCTION `function-name` (`parameter-type`,...) RESTRICT	Provides the function signature, which uniquely identifies the function. There must be exactly one function with the function signature in the implicitly or explicitly specified schema; otherwise, an error occurs. If the function was defined with a table parameter (the LIKE TABLE was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), the function signature cannot be used to uniquely identify the function. Instead, use one of the other syntax variations to identify the function with its function name, if unique, or its specific name.
`function-name`	Identifies the name of the function.
(`parameter-type`,...)	Identifies the parameters of the function.

If an unqualified distinct type name is specified, XDB searches the SQL path to resolve the schema name for the distinct type.

The data types of the 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 are used to identify the function.

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 XDB ignores the attribute when determining whether the data types match.
FLOAT cannot be specified with empty parentheses because its parameter value indicates different data types (REAL or DOUBLE).
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.
The specific value for FLOAT(n) does not have exactly match the defined value of the source function because 1<=n<= 21 indicates REAL and 22<=n<=53 indicates DOUBLE. Matching is based on whether the data type is REAL or DOUBLE.

If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default length of the data type is implied. For example:

CHAR	CHAR(1)
GRAPHIC	GRAPHIC(1)
DECIMAL	DECIMAL(5,0)
FLOAT	DOUBLE (length of 8)

The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

For data types with a subtype or encoding scheme attribute, specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that XDB 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 RESTRICT

Identifies the function by its specific name, which was explicitly specified or implicitly created when the function was created. There must be exactly one function with specific-name in the implicitly or explicitly specified schema.

INDEX

The name must identify a user-defined index that exists at the current server but must not identify a populated index on an auxiliary table. A populated index on an auxiliary table can only be dropped by dropping the base table.

If the index that is dropped was created by specifying the ENDING AT clause to define partition boundaries, the table is converted to use table-controlled partitioning. The high limit key for the last partition is set to the highest possible value for ascending key columns or the lowest possible value for descending key columns.

Whenever an index is directly or indirectly dropped, its index space is also dropped. The name of a dropped index space cannot be reused until a commit operation is performed.

If the index is a unique index used to enforce a unique constraint (primary or unique key), the unique constraint must be dropped before the index can be dropped. In addition, if a unique constraint supports a referential constraint, the index cannot be dropped unless the referential constraint is dropped.

However, a unique index (for a unique key only) can be dropped without first dropping the unique key constraint if the unique key was created in a release of DB2 before Version 7 and if the unique key constraint has no associated referential constraints.

If a unique index is dropped and that index was defined on a ROWID column that is defined as GENERATED BY DEFAULT, the table can still be used, but rows cannot be inserted into that table.

If an empty index on an auxiliary table is dropped, the base table is marked incomplete.

LOCATION

This keyword indicates that a user-created location is to be dropped. All database and other objects stored within the location are also dropped. INSTALL authority is needed to drop a location. When dropping a location, not all files are cleaned up. Some files may be left in the directory that contained the location. If you attempt to create another location in the same directory, these files will be overwritten. If you wish to clean up your hard drive, delete these files -- just make sure that you are deleting files from an already deleted location.

Note:

You cannot drop a location that was created in the same session. You must first exit the interactive SQL session during which you created the location, and then start another interactive SQL session to drop the newly created location.

PROCEDURE procedure-name RESTRICT

Identifies the stored procedure to be dropped. The name must identify a stored procedure that has been defined with the CREATE PROCEDURE statement at the current server. The required keyword RESTRICT prevents the procedure from being dropped if a trigger definition contains a CALL statement with the name of the procedure.

When a procedure is directly or indirectly dropped, all privileges on the procedure are also dropped. In addition, any plans or packages that are dependent on the procedure are made inoperative.

SEQUENCE sequence-name

Identifies the sequence to be dropped. The name must identify an existing sequence at the current server. If no sequence by this name exists in the explicitly or implicitly specified schema, an error occurs.

sequence-name must not be the name of an internal sequence object that is generated by the system for an identity column. Sequences generated by the system for identity columns cannot be dropped with the DROP SEQUENCE statement. A sequence object for an identity column is implicitly dropped when the table containing the identify column is dropped.

The default keyword RESTRICT indicates that the sequence is not dropped if any of the following dependencies exist:

A trigger that uses the sequence in a NEXT VALUE or PREVIOUS VALUE expression exists.
An inline SQL function that uses the sequences in a NEXT VALUE or PREVIOUS VALUE expression exists.

Whenever a sequence is dropped, all privileges on the sequence are also dropped, and the plans and packages that refer to the sequence are invalidated. Dropping a sequence, even if the drop process is rolled back, results in the loss of the still-unassigned cache values for the sequence.

STOGROUP

This keyword indicates that a stogroup is to be dropped. The storage group must exist at the current location and must not be the default storage group (SYSDEFLT) or a storage group still used by any table space or index space. Objects defined within a stogroup are not automatically dropped when the stogroup is dropped. Users must first drop the individual objects in the stogroup or alter the objects to utilize a different stogroup. An error occurs when attempting to drop a stogroup being used by a table space.

SYNONYM

This keyword indicates that a synonym is to be dropped. In a dynamic DROP SYNONYM statement, the synonym identified must be owned by the CURRENT SQLID. Dropping a synonym does not affect any view or alias previously defined using the synonym. A user with SYSADM privileges can drop synonyms owned by other users.

Dropping a synonym has no effect on any view, materialized query table, or alias that was defined using the synonym, nor does it invalidate any plans or packages that use such views, materialized query tables, or aliases.

TABLE

This keyword indicates that a table is to be dropped. The table must exist at the current location and must not be a system catalog table. When a table is dropped (either directly or indirectly), all privileges on the table are dropped. Additionally, all parent or dependent referential constraints built on the table, all synonyms, views, and indexes defined on the table are also dropped.

A table in a partitioned table space can only be dropped by dropping the table space. A populated auxiliary table can only be dropped by dropping the associated base table.

Whenever a table is directly or indirectly dropped, all materialized query tables defined on the table are also dropped. Whenever a materialized query table is directly or indirectly dropped, all privileges on the materialized query table and all synonyms, views, and indexes that are defined on the materialized query table are also dropped. Any alias defined on the materialized query table is not dropped. Any plans or packages that are dependent on the dropped materialized query table are marked invalid.

If a table with LOB columns is dropped, the auxiliary tables associated with the table and the indexes on the auxiliary tables are also dropped. Any LOB table spaces that were implicitly created for the auxiliary tables are also dropped.

If an empty auxiliary table is dropped, the definition of the base table is marked incomplete.

If the table has a security label column, the primary authorization ID of the DROP statement must have a valid security label, and the RACF SECLABEL class must be active.

TABLESPACE

This keyword indicates that a table space is to be dropped. All tables in the dropped table space are also dropped.

The table space must exist at the current location (and must not be in the DSNDB06 database). Table spaces that were created implicitly cannot be dropped. If a database name qualifier is not included, than the default DSNDB04 database for that location is implicitly specified.

A table space in a TEMP database can be dropped only if it does not contain an active declared temporary table. A LOB table space can be dropped only if it does not contain an auxiliary table.

Whenever a base table space that contains tables with LOB columns is dropped, all the auxiliary tables and indexes on those auxiliary tables that are associated with the base table space are also dropped.

TRIGGER trigger-name

Identifies the trigger to be dropped. The name must identify a trigger that exists at the current server.

Whenever a trigger is directly or indirectly dropped, all privileges on the trigger are also dropped and the associated trigger package is freed. The name of that trigger package is the same as the trigger name and the collection ID is the schema name.

VIEW

This keyword indicates that a view is to be dropped. The name must identify a view that exists at the current server.

Whenever a view is directly or indirectly dropped, all privileges on the view and all synonyms and views that are defined on the view are also dropped. Whenever a view is directly or indirectly dropped, all materialized query tables defined on the view are also dropped.

Dependencies when dropping objects

Whenever an object is directly or indirectly dropped, other objects that depend on the dropped object might also be dropped. (The catalog stores information about the dependencies of objects on each other.) The following semantics determine what happens to a dependent object when the object that it depends on (the underlying object) is dropped:

Cascade (D)	Dropping the underlying object causes the dependent object to be dropped. However, if the dependent object cannot be dropped because it has a restrict dependency on another object, the drop of the underlying object fails.
Restrict (R)	The underlying object cannot be dropped if a dependent object exists.
Inoperative (O)	Dropping the underlying object causes the dependent object to become inoperative.
Invalidation (V)	Dropping the underlying object causes the dependent object to become invalidated.

Example:

The statements in the following examples are assumed to be in PL/I programs.

Place information about the result sets returned by stored procedure P1 into the descriptor named by SQLDA1. Assume that the stored procedure is called with a one-part name from current server SITE2.

EXEC SQL CONNECT TO SITE2;
EXEC SQL CALL P1;
EXEC SQL DESCRIBE PROCEDURE P1 INTO :SQLDA1;