Sunday, May 27, 2012

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.

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.