The ADMIN_DROP_SCHEMA procedure is used to drop a specific
schema and all objects contained in it.
Syntax
>>-ADMIN_DROP_SCHEMA--(--schema--,--dropmode--,----------------->
>--errortabschema--,--errortab--)------------------------------><
Procedure parameters
- schema
- An input argument of type VARCHAR(128) that specifies the name
of the schema being dropped. The name must be specified in uppercase
characters.
- dropmode
- Reserved for future use and should be set to NULL.
- errortabschema
- An input and output argument of type VARCHAR(128) that specifies
the schema name of a table containing error information for objects
that could not be dropped. The name is case-sensitive. This table
is created for the user by the ADMIN_DROP_SCHEMA procedure in the
SYSTOOLSPACE table space. If no errors occurred, then this parameter
is NULL on output.
- errortab
- An input and output argument of type VARCHAR(128) that specifies
the name of a table containing error information for objects that
could not be dropped. The name is case-sensitive. This table is created
for the user by the ADMIN_DROP_SCHEMA procedure in the SYSTOOLSPACE
table space. This table is owned by the user ID that invoked the procedure.
If no errors occurred, then this parameter is NULL on output. If the
table cannot be created or already exists, the procedure operation
fails and an error message is returned. The table must be cleaned
up by the user following any call to ADMIN_DROP_SCHEMA; that is, the
table must be dropped in order to reclaim the space it is consuming
in SYSTOOLSPACE.
Table 1. ADMIN_DROP_SCHEMA errortab
formatColumn name |
Data type |
Description |
OBJECT_SCHEMA |
VARCHAR(128) |
Schema name of the object for which the drop
command failed. |
OBJECT_NAME |
VARCHAR(128) |
Name of the object for which the drop command
failed. |
OBJECT_TYPE |
VARCHAR(30) |
Type of object. |
SQLCODE |
INTEGER |
The error SQLCODE. |
SQLSTATE |
CHAR(5) |
The error SQLSTATE. |
ERROR_TIMESTAMP |
TIMESTAMP |
Time that the drop command failed. |
STATEMENT |
CLOB(2 M) |
DDL for the failing object. |
DIAGTEXT |
CLOB(2 K) |
Error message text for the failed drop command. |
Authorization
Drop authority is needed
on all objects being removed for the user calling this procedure.
EXECUTE privilege on the ADMIN_DROP_SCHEMA procedure is also
needed.
Example
CALL SYSPROC.ADMIN_DROP_SCHEMA('SCHNAME', NULL, 'ERRORSCHEMA', 'ERRORTABLE')
The following example is a sample output for this procedure.
Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : ERRORSCHEMA <-- error!
Parameter Name : ERRORTAB
Parameter Value : ERRORTABLE <-- error!
Return Status = 0
The return status is not zero only when
an internal error has been detected (for example, if SYSTOOLSPACE
does not exist).
Errors can be checked by querying the error
table:
SELECT * FROM ERRORSCHEMA.ERRORTABLE
Usage notes
- If objects in another schema depend on an object being dropped,
the default DROP statement semantics apply.
- This procedure does not support dropping the following objects:
- index extensions
- nicknames
- packages
- typed tables
- array types
- user-defined structured types (and their transform functions)
- typed views
- jars (Java™ routine archives)
- staging tables
- If one of the previously listed objects exists in the schema being dropped,
neither the object nor the schema is dropped, and an entry is added
to the error table indicating that the object was not dropped.
- The operation of this procedure requires the existence of the
SYSTOOLSPACE table space. This table space is used to hold metadata
used by the ADMIN_DROP_SCHEMA procedure as well as error tables returned
by this procedure. If the table space does not exist, an error is
returned.