Tuesday, February 18, 2014

How to Drop schema including its contents in DB2

One day i was doing a schema refresh activity that's when this point came in, I first have to drop the schemas including content and then do a copy.

DB2 has provided us, with a admin procedure which drop's a specific schema and also all the objects contained in it. 

ADMIN_DROP_SCHEMA procedure

Syntax: ADMIN_DROP_SCHEMA(<schema>,<dropmode>,<errortabschema>,<errortab>)

<schemname> - Mention the name of the schema to be dropped in CAPTITAL LETTER
<dropmode> - Reserved for future use and should be set to NULL
<errortabschema> - specify the schema name of a table containing error information for objects that could not be dropped 
<errortab> - specify the name of a table containing error information for objects that could not be dropped.

Example: db2 "call ADMIN_DROP_SCHEMA('SAMPLE',NULL,'ERRORSCHEMA','ERRORTAB')"

db2inst1@test-machine:~$ db2 "call admin_drop_schema('SAMPLE',NULL,'ERRORSCHEMA','ERRORTAB')"

  Value of output parameters
  --------------------------
  Parameter Name  : ERRORTABSCHEMA
  Parameter Value : -

  Parameter Name  : ERRORTAB
  Parameter Value : -

  Return Status = 0

Return status=0 so it means success. Just query the schemaname in database and could see it created ERRORSCHEMA and as it didn't have any issues no errortable is created. If a schema doesn't contain any objects it easy to drop a schema using simple command

db2 "drop schema <schemaname> RESTRICT"

RESTRICT keyword enforces the rule that no objects can be defined in the specified schema for the schema to be deleted from the database

Let me use it on the empty schema which is just created when we tried to drop SAMPLE schema using ADMIN_SCHEMA_DROP procedure, Below is the example for it.


db2inst1@test-machine:~$ db2 "list tables for schema errorschema"

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------

  0 record(s) selected.

db2inst1@test-machine:~$ db2 "drop schema errorschema restrict"
DB20000I  The SQL command completed successfully.

I dropped the schema using admin_drop_schema and it has given me return status 0, still i could able to see it in catalog. After some research i understood that there are some dependants which are dropped so i was unable to drop it from catalog. I then took
DB2LOOK for the specific schema and then was able to the USER DEFINED DATA TYPES which are there so i was unable to drop the schema from catalog. I then dropped the user defined dataypes and executed the command db2 "drop schema schemaname restrict" this time
i was able to drop it from catalog. Before dropping the user defined data types i saved them in some file so if needed i can create them again. Also to make sure if your left with any more take a DB2LOOK and check again, Below is the example for the same.
db2 "drop type userdefined1"
DB20000I  The SQL command completed successfully.
db2 "drop type userdefined2"
DB20000I  The SQL command completed successfully.
db2 "drop schema schemaname restrict"
DB20000I  The SQL command completed successfully.

References from IBM : ADMIN_DROP_SCHEMA procedure V9.7



No comments:

Post a Comment