DB2 LUW - Drop Schema and all objects under it

Did you ever try to drop schema in DB2 LUW, lot of DBA's face that situation quiet often when building databases for new applications. The command to drop a schema in DB2 LUW 9 is as follows,

DROP SCHEMA DB2INST1 RESTRICT;

But if there are objects undneath that schema you will get following return message.

DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA" cannot be processed because there is an object "DB2INST1.TEST_TAB", of type "TABLE", which depends on it.. SQLCODE=-478, SQLSTATE=42893

You have drop all the child objects under the given schema first and then drop the schema.

So the other good workaround to this is "ADMIN_DROP_SCHEMA", usage is explained below.

db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('SAMPLE', NULL, 'ERRORSCHEMA', 'ERRORTABLE')";

Where,

"SAMPLE" is the schema name.

NULL is reserved for future use.

'ERRORSCHEMA' 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.

'ERRORTABLE' 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.