DROP

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

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

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.

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.