for the purpose of testing I have to drop a schema with a large amount of objects often. It consists of > 2000 tables, a large amount of foreign key and check constraints, triggers views and so on. All tables are within the same tablespace, but the tablespace is used by other schemas too.
I did try admin_drop_schema procedure at first, but it takes a lot more time than parsing all schema catalogs, generate all drop statements and execute it as one sql script. The script runs under 45 minutes and the procedure more than a hour. Further more the procedure locks to many things.
What is the fastest way to drop such a schema?
Best Answer
Several years ago I did put together a package of db2utils which included a procedure called
DROP_SCHEMA
. This was written beforeADMIN_DROP_SCHEMA
became available in DB2, and uses a fairly crude mechanism for determining what to drop, and in what order. To whit: it uses a largeUNION
query to get all objects belonging to the schema from the variousSYSCAT
views:(Full source; the
QUOTE_IDENTIFIER
function is another utility in the package)It then orders the result in descending order by creation time, running the SQL DROP statements with
EXECUTE IMMEDIATE
:This is simple, but crude especially in light of DB2's later introduction of deferred revalidation of objects. This can result in creation timestamp orderings for which the drop sequence is no longer correct. In practice, it mostly worked for me and on the rare occasions it didn't I could fall back to running the query manually to figure out what needed dealing with.
This brings me to the other major difference to
ADMIN_DROP_SCHEMA
: error handling.ADMIN_DROP_SCHEMA
takes several parameters which are used to construct and fill a table with error codes and messages for post-analysis.While comprehensive, I found this didn't work well with most of my use-cases. Most of the time I was using these tools to tear down development / testing schemas, then tweaking a creation script and re-running it, or using it to destroy relatively simple personal schemas when users were deleted. Hence I designed
DROP_SCHEMA
deliberately to be atomic: it either works and the schema disappears in its entirety, or it fails with an error and the schema remains in its entirety (assuming you rollback the containing transaction).I did generally find it faster than
ADMIN_DROP_SCHEMA
but that wasn't an intention of the design, and given I haven't maintained it for several years there may well be new schema-based objects in DB2 which it doesn't correctly handle. Still, it may serve as a useful starting point for others' efforts.