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.

Recovering from a failed LOAD operation in DB2 LUW

Have you ever dealt with situation where you have to bring a table to a normal state from the faild load condition and you don't know what load command the user used ?
When you do a db2 load and if that terminates with error then the table sits in integrity pending state, below are the sql statements and the sysmtoms of the situation

SELECT * FROM DB2INST1.EMPLOYEE
Operation not allowed for reason code "1" on table "db2inst1.employee".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.8.86

Based on the above error code you attempt

SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0668N  Operation not allowed for reason code "3" on table
"db2inst1.employee".  SQLSTATE=57016

SQL0668N - reason code "3" - states that

Cause:
        The table is in the Load Pending state. A previous LOAD attempt
         on this table resulted in failure. No access to the table is
         allowed until the LOAD operation is restarted or terminated.

Resolution:

         Restart or terminate the previously failed LOAD operation on
         this table by issuing LOAD with the RESTART or TERMINATE option
         respectively.

Caution: below steps will truncate the data in your table.

Recovery Steps:

touch test.del ...just to create some dummy file (zero bytes)
LOAD FROM 'test.del' OF DEL TERMINATE INTO db2inst1.employee .. I did not know what the original load command was.
SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED

After these steps your table should be back to normal and is usable.