删除schema
DROP SCHEMA ERRORSCHEMA1 RESTRICT --schema中不能含有任何对象
--RESTRICT 关键字强制实施以下规则:不能在指定的模式中为要从数据库中删除的模式定义对象。RESTRICT 关键字并非可选关键字
IBM网址: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022036.html?cp=SSEPGG_10.5.0%2F3-6-1-3-0-2&lang=zh
DROP SCHEMA ERRORSCHEMA1 RESTRICT --schema中不能含有任何对象
--RESTRICT 关键字强制实施以下规则:不能在指定的模式中为要从数据库中删除的模式定义对象。RESTRICT 关键字并非可选关键字
IBM网址: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0022036.html?cp=SSEPGG_10.5.0%2F3-6-1-3-0-2&lang=zh
DB2 10.5 for Linux, UNIX, and Windows
ADMIN_DROP_SCHEMA procedure - Drop a specific schema and its objects
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--)------------------------------><
The schema is SYSPROC.
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.Column name | Data type | Description |
---|---|---|
OBJECT_SCHEMA | VARCHAR(128) | object_schema - Object schema monitor element |
OBJECT_NAME | VARCHAR(128) | object_name - Object name monitor element |
OBJECT_TYPE | VARCHAR(30) | objtype - Object type monitor element |
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
One of the following authorizations is required:
- EXECUTE privilege on the ADMIN_DROP_SCHEMA procedure
- DATAACCESS authority
- DBADM authority
- SQLADM authority
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.
Example
CALL SYSPROC.ADMIN_DROP_SCHEMA('SCHNAME', NULL, 'ERRORSCHEMA', 'ERRORTABLE')
The following is an example of output for this procedure.
Value of output parameters -------------------------- Parameter Name : ERRORTABSCHEMA Parameter Value : ERRORSCHEMA 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
- XSR objects
- If one of these 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.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28258625/viewspace-1656139/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28258625/viewspace-1656139/