DROP
The DROP statement drops an object. Objects that are directly or indirectly dependent on that object may also be dropped.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
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
- If the object is a system-period temporal table, the system authorities of *OBJOPR and *OBJEXIST on the associated history table
- The system authority *EXECUTE on the library that contains the object to be dropped
- Database administrator authority
To drop a schema, 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 schema and *OBJOPR and *OBJEXIST on any views, indexes and logical files that are dependent on tables and views in the schema.
- Any additional authorities required to delete other object types that exist in the schema. For example, *OBJMGT to the data dictionary if the schema contains a data dictionary, and some system data authority to the journal receiver. For more information, see Security Reference.
- Database administrator 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 type to be dropped
- The system authority *EXECUTE on the library that contains the type to be dropped
- The DELETE privilege on the SYSTYPES, SYSPARMS, and SYSROUTINES catalog tables, and
- The system authority *EXECUTE on library QSYS2
- Database administrator authority
To drop a global variable, the privileges held by the authorization ID of the statement must include at least one of the following:
- The following system authorities:
- The system authority of *OBJEXIST on the *SRVPGM object for the global variable to be dropped
- The system authority *EXECUTE on the library that contains the global variable to be dropped
- The DELETE privilege on the SYSVARIABLES catalog table, and
- The system authority *EXECUTE on library QSYS2
- Database administrator authority
To drop an XSR object, 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 *SQLXSR object for the XSR object to be dropped
- The system authority *EXECUTE on the library that contains the XSR object to be dropped
- The DELETE privilege on the XSROBJECTS, XSROBJECTCOMPONENTS, and XSRANNOTATIONINFO catalog tables, and
- The system authority *EXECUTE on library QSYS2
- Database administrator 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 service program object associated with the function, and
- The DELETE privilege on the SYSFUNCS, SYSPARMS, and SYSROUTINEDEP catalog tables, and
- The system authority *EXECUTE on library QSYS2
- Database administrator 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, SYSPARMS, and SYSROUTINEDEP catalog tables, and
- The system authority *EXECUTE on library QSYS2
- Database administrator authority
To drop a sequence, the privileges held by the authorization ID of the statement must include at least one of the following:
- The following system authorities:
- The system authority *OBJEXIST on the data area associated with the sequence, and
- The system authority *EXECUTE on the library that contains the sequence to be dropped
- The DELETE privilege on the SYSSEQOBJECTS catalog table, and
- The system authority *EXECUTE on library QSYS2, and
- *USE to the Delete Data Area (DLTDTAARA) command
- Database administrator authority
To drop a trigger, the privileges held by the authorization ID of the statement must include at least one of the following:
- The following privileges:
- The system authority *USE to the Remove Physical File Trigger (RMVPFTRG) command, and
- For the subject table or view of the trigger:
- The ALTER privilege to the subject table or view, and
- The system authority *EXECUTE on the library containing the subject table or view,
- If the trigger being dropped is an SQL trigger:
- The system authority *OBJEXIST on the trigger program object, and
- The system authority *EXECUTE on the library containing the trigger.
- Database administrator authority
- The authorization ID of the statement must have security administrator authority. See Administrative authority.
- The authorization ID of the statement must have security administrator authority. See Administrative authority.
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.
Syntax
Description
- IF EXISTS
- Specifies that no error is issued if the specified object does not exist. Unless other conditions or dependencies prevent the drop operation, a successful SQLSTATE is returned even if no object is dropped.
- ALIAS alias-name
- Identifies
the alias that is to be dropped. The alias-name must identify
an alias that exists at the current server.
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.
- FUNCTION or SPECIFIC FUNCTION
- Identifies the function that
is to be dropped. The function must exist at the current server and
it must be a function that was defined with the CREATE FUNCTION statement.
The particular function can be identified by its name, function signature,
or specific name.
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. A function cannot be dropped if it is referenced in a mask or permission even if RESTRICT is not 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.
- FUNCTION function-name
- Identifies the function by its name. The function-name must identify exactly one function. 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 schema, an error is returned.
- FUNCTION function-name (parameter-type, ...)
- Identifies the function by its function signature, which uniquely
identifies the function. The function-name (parameter-type, ...) must
identify a function with the specified function signature. The specified
parameters must match the data types in the corresponding position
that were specified when the function was created. 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. Synonyms
for data types are considered a match. Parameters
that have defaults must be included in this signature.
If function-name () is specified, the function identified must have zero parameters.
- function-name
- Identifies the name of the function.
- (parameter-type, ...)
- Identifies the parameters of the function.
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:
- Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a function defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
- If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
- If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.
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.
- AS LOCATOR
- 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.
- SPECIFIC FUNCTION specific-name
- Identifies the function by its specific name. The specific-name must identify a specific function that exists at the current server.
- RESTRICT
- Specifies that the function cannot be dropped if it is referenced in an SQL function, SQL procedure, table, mask, permission, SQL trigger, variable, or view.
- RESTRICT not specified
- If the function is referenced in a mask or permission, the drop will fail unless the authorization ID of the statement has security administrator authority.
- INDEX index-name
- Identifies
the index that is to be dropped. The index-name must identify
an index that exists at the current server.
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.
- MASK mask-name
- Identifies
the mask that is to be dropped. The mask-name must identify
a mask that exists at the current server.
The specified mask is dropped from the schema. A mask can be dropped whether it is referenced in a function, package, procedure, program, or trigger.
- PACKAGE package-name
- Identifies
the package that is to be dropped. The package-name must identify
a package that exists at the current server.
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.
- VERSION version-id
- version-id is the version identifier that was assigned to the package when it was created. If version-id is not specified, a null string is used as the version identifier.
- PERMISSION permission-name
- Identifies
the permission that is to be dropped. The permission-name must
identify a permission that exists at the current server.
The specified permission is dropped from the schema. A permission can be dropped whether it is referenced in a function, package, procedure, program, or trigger.
- PROCEDURE or SPECIFIC PROCEDURE
- Identifies the procedure that
is to be dropped. The procedure-name must identify a procedure
that exists at the current server.
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.
- PROCEDURE procedure-name
- Identifies the procedure by its name. The procedure-name must identify exactly one procedure. 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 schema, an error is returned.
- PROCEDURE procedure-name (parameter-type, ...)
- Identifies the procedure by its procedure signature, which uniquely
identifies the procedure. The procedure-name (parameter-type,
...) must identify a procedure with the specified procedure signature.
The specified parameters must match the data types in the corresponding
position that were specified when the procedure was created. 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.
Synonyms for data types are considered a match. Parameters
that have defaults must be included in this signature.
If procedure-name () is specified, the procedure identified must have zero parameters.
- procedure-name
- Identifies the name of the procedure.
- (parameter-type, ...)
- Identifies the parameters of the procedure.
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:
- Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a procedure defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
- If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
- If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement.
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.
- AS LOCATOR
- 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.
- SPECIFIC PROCEDURE specific-name
- Identifies the procedure by its specific name. The specific-name must identify a specific procedure that exists at the current server.
- RESTRICT
- Specifies that the procedure cannot be dropped if it is referenced in an SQL function, SQL procedure, or SQL trigger.
- SCHEMA schema-name
- Identifies the schema that is
to be dropped. The schema-name must identify a schema that
exists at the current server.
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.
- Neither CASCADE nor RESTRICT
- Specifies that the schema will be dropped even if it is referenced in a function, package, procedure, program, table, mask, permission, trigger, or variable in another schema.
- CASCADE
- Specifies that any objects in the schema and any triggers that reference the schema will be dropped. Any masks and permissions in a different schema that reference the schema are dropped if the authorization ID of the statement has security administrator authority.
- RESTRICT
- Specifies that the schema cannot be dropped if it is referenced in an SQL trigger, mask, or permission in another schema or if the schema contains any SQL objects other than catalog views, the journal, and journal receiver.
- SEQUENCE sequence-name
- Identifies the sequence that
is to be dropped. The sequence-name must identify a sequence
that exists at the current server.
- RESTRICT
- Specifies that the sequence cannot be dropped if it is referenced in an SQL trigger, function, procedure, or variable.
- TABLE table-name
- Identifies the table that is
to be dropped. The table-name must identify a base table that
exists at the current server, but must not identify a catalog table or a history table for a system-period temporal table.
The specified table is dropped from the schema. All privileges, constraints, indexes, masks, permissions, and triggers on the table are also dropped.
Any aliases that reference the specified table are not dropped.
- Neither CASCADE nor RESTRICT
- Specifies that the table will be dropped even if it is referenced in a constraint, index, trigger, mask, permission, 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. If the table is a system-period temporal table, the history table is also dropped. If the table is referenced in a mask or permission, the drop will fail unless the authorization ID of the statement has security administrator authority.
- CASCADE
- Specifies that the table will be dropped even if it is referenced in a constraint, index, trigger, variable, mask, permission, 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. If the table is a system-period temporal table, the history table is also dropped. All masks and permissions that reference the table are dropped if the authorization ID of the statement has security administrator authority.
- RESTRICT
- Specifies that the table cannot be dropped if it is referenced in a constraint, index, mask, permission, trigger, variable, view, XSR object, or materialized query table, or if it is a system-period temporal table.
- TRIGGER trigger-name
- Identifies
the trigger that is to be dropped. The trigger-name must identify
a trigger that exists at the current server.
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.
- TYPE distinct-type-name or array-type-name
- Identifies the type that is
to be dropped. 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.
- Neither CASCADE nor RESTRICT
- Specifies that the type cannot be dropped if any constraints,
indexes, masks, permissions, 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.
- CASCADE
- 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. The cascade processing is limited to cases where the type is used to define the routine parameter type, sequence type, variable type and column type. All masks and permissions that reference the type are dropped if the authorization ID of the statement has security administrator authority.
- RESTRICT
- 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, mask, permission, trigger, variable, or view. The restrict checking is limited to cases where the type is used to define the routine parameter type, sequence type, variable type and column type.
- 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.
- Neither CASCADE nor RESTRICT
- Specifies that the variable will be dropped even if it is referenced in a trigger, procedure, function, mask, permission, view, or another variable. All tables and views that reference the variable are dropped. If the variable is referenced in a mask or permissions, the drop will fail unless the authorization ID of the statement has security administrator authority.
- CASCADE
- Specifies that the variable will be dropped even if it is referenced in a table, trigger, procedure, function, view, or another variable. All tables, triggers, procedures, functions, views, and variables that reference the variable are dropped. All masks and permissions that reference the variable are dropped if the authorization ID of the statement has security administrator authority.
- RESTRICT
- Specifies that the variable cannot be dropped if it is referenced in a table, trigger, procedure, function, mask, permission, view, or another variable.
- VIEW view-name
- Identifies
the view that is to be dropped. The view-name must identify
a view that exists at the current server, but must not identify a
catalog view.
The specified view is dropped from the schema. When a view is dropped, all privileges and triggers on that view are dropped.
- Neither CASCADE nor RESTRICT
- Specifies that the view will be dropped even if it is referenced in a trigger, materialized query table, mask, permission, or another view. All views and materialized query tables that reference the view are dropped. If the view is referenced in a mask or permissions, the drop will fail unless the authorization ID of the statement has security administrator authority.
- CASCADE
- 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. All masks and permissions that reference the view are dropped if the authorization ID of the statement has security administrator authority.
- RESTRICT
- Specifies that the view cannot be dropped if it is referenced in a trigger, variable, mask, permission, 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.
Notes
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.
Dependencies: Whenever an object is directly or indirectly dropped, other objects that depend on the dropped object might also be dropped. The following semantics determine what happens to a dependent object when the object that it depends on (the underlying object) is dropped:
- D
- Dependent object is dropped.
- A
- Automatic revalidation is required. The database manager will attempt to revalidate the object when it is referenced.
- R
- DROP statement fails.
Drop Statement
|
ALIAS
|
CONSTRAINT
|
FUNCTION
|
INDEX
|
MASK
|
PERMISSION
|
PROCEDURE
|
SCHEMA
|
SEQUENCE
|
TABLE
|
TRIGGER
|
TYPE
|
VARIABLE
|
VIEW
|
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
DROP ALIAS | ||||||||||||||
DROP FUNCTION | R1 | A | A | A | A | A | A | A | ||||||
DROP FUNCTION RESTRICT | R | R | R | R | R | R | R | R | ||||||
DROP INDEX | ||||||||||||||
DROP MASK | ||||||||||||||
DROP PACKAGE | ||||||||||||||
DROP PERMISSION | ||||||||||||||
DROP PROCEDURE | A | A | A | |||||||||||
DROP PROCEDURE RESTRICT | R | R | R | |||||||||||
DROP SCHEMA2 | A | A | A | A | A | A | A | A | ||||||
DROP SCHEMA CASCADE3 | A | D | D | A | A | D | A | A | ||||||
DROP SCHEMA RESTRICT4 | A | R | R | A | A | R | A | A | ||||||
DROP SEQUENCE | A | A | A | A | ||||||||||
DROP SEQUENCE RESTRICT | R | R | R | R | ||||||||||
DROP TABLE | D | A | D | A | A | A | D | A | A | D | ||||
DROP TABLE CASCADE | D | A | D | D | D | A | D | D | D | D | ||||
DROP TABLE RESTRICT | R | A | R | R | R | A | R | R | R | R | ||||
DROP TRIGGER | ||||||||||||||
DROP TYPE | R | D5 | R | R | R | D | R | R | D | R | R | |||
DROP TYPE CASCADE | D | D | D | D | D | D | D | D | D | D | D | |||
DROP TYPE RESTRICT | R | R | R | R | R | R | R | R | R | R | R | |||
DROP VARIABLE | A | A | A | A | D | A | A | D | ||||||
DROP VARIABLE CASCADE | D | D | D | D | D | D | D | D | ||||||
DROP VARIABLE RESTRICT | R | R | R | R | R | R | R | R | ||||||
DROP VIEW | A | A | A | D | A | A | D | |||||||
DROP VIEW CASCADE | A | D | D | A | D | D | D | D | ||||||
DROP VIEW RESTRICT | A | R | R | A | R | R | R | R | ||||||
DROP XSROBJ | A | A | A | A | A | A | A | |||||||
Notes:
|
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
- The keyword SYNONYM can be used as a synonym for ALIAS.
- The keywords DATA TYPE or DISTINCT TYPE can be used as a synonym for TYPE.
- The keyword PROGRAM can be used as a synonym for PACKAGE.
- The keyword COLLECTION can be used as a synonym for SCHEMA.
For compatibility with other database products, the IF EXISTS clause can be specified following table-name for DROP TABLE.
Examples
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