888-685-3101, ext. 2

Dropping a DB2 Schema and Objects it Contains

by | Apr 7, 2016 | Blog, Databases

server rack

It may be something that’s not terribly common to have to do, but that makes it all the more important that we document how to do it. Dropping a schema and all of the objects in that schema used to be tedious and time-consuming. We used to have to look for a large number of different types of objects and individually drop each one before we were unable to drop the schema itself. In DB2 9.5, IBM introduced ADMIN_DROP_SCHEMA to help with this.

IBM_DB2

Identifying Objects in a Schema

It is best to first list out the objects in a schema so you can communciate with others precisely what is being dropped, or at least record it before the schema is dropped. Also in DB2 9.5, the administrative view SYSIBMADM.OBJECTOWNERS was introduced. This joins together all the various system tables that list the various kinds of objects so we have one location to list the various kinds of objects. It is easy to query to find the objects in a particular schema, and lists the schema itself as an object:

select substr(OWNER,1,12) as OWNER
    , OWNERTYPE
    , substr(OBJECTNAME,1,30) as OBJECTNAME
    , OBJECTTYPE 
from SYSIBMADM.OBJECTOWNERS 
where OBJECTSCHEMA='SSIRS_AGENCY' 
with ur;

OWNER     OWNERTYPE OBJECTNAME               OBJECTTYPE              
--------- --------- ------------------------ ------------------------
SYSIBM    S         SSIR_DMART               SCHEMA                  
DB2BCUP   U         SQL150114132019140       TABLE CONSTRAINT        
DB2BCUP   U         INDV_AID_CD_DM           TABLE                   

  3 record(s) selected.

In the above SQL, you would obviously have to replace the schema name with the name of the schema you are working with.

Backout Planning

Like any good DBA, I first have a back out plan for every change I perform, and this is no different. Here is the data to collect before dropping a schema:

  • db2look with syntax for the whole database
  • List of objects in the schema from SYSIBMADM.OBJECTOWNERS
  • Count of rows in all tables in the schema from SYSCAT.TABLES
  • Exported data from the tables in the schema in del and/or ixf formats

My many-layered back out options are:

  1. Re-create the objects from the db2look ddl
  2. Import/load the data into the tables from the del files
  3. Create the tables and import data in from the ixf files
  4. Last Resort: restore a backup somewhere else and export out what I might have missed

Actually Dropping the Objects and Schema

Like many DBA tasks, there is more effort in planning and preparing the backout plan than there is in the actual work. Actually dropping the schema and all objects it contains is accomplished like this:

$ db2 "call ADMIN_DROP_SCHEMA('SSIRS_AGENCY',NULL,'DBA','DRP_TAB_ERROR')"

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

  Parameter Name  : ERRORTAB
  Parameter Value : -

  Return Status = 0
$ db2 "select * from DBA.DRP_TAB_ERROR"
SQL0204N  "DBA.DRP_TAB_ERROR" is an undefined name.  SQLSTATE=42704

The final select is done to ensure that no errors were generated.

I also verify there is noting left in the schema like this:

$ db2 "select * from SYSIBMADM.OBJECTOWNERS where OBJECTSCHEMA='SSIRS_AGENCY' with ur"

OWNER                                                                                                                            OWNERTYPE OBJECTNAME                                                                                                                       OBJECTSCHEMA                                                                                                                     OBJECTTYPE              
-------------------------------------------------------------------------------------------------------------------------------- --------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------

  0 record(s) selected.