Several years ago I did put together a package of db2utils which included a procedure called
DROP_SCHEMA. This was written before
ADMIN_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 large
UNION query to get all objects belonging to the schema from the various
WITH DROP_LIST (CREATE_TIME, SCHEMA_NAME, DDL) AS (
TABSCHEMA AS SCHEMA_NAME,
'DROP ' || CASE TYPE
WHEN 'A' THEN 'ALIAS'
WHEN 'H' THEN 'TABLE'
WHEN 'N' THEN 'NICKNAME'
WHEN 'S' THEN 'TABLE'
WHEN 'T' THEN 'TABLE'
WHEN 'U' THEN 'TABLE'
WHEN 'V' THEN 'VIEW'
WHEN 'W' THEN 'VIEW'
END || ' ' || QUOTE_IDENTIFIER(TABSCHEMA) || '.' || QUOTE_IDENTIFIER(TABNAME) AS DDL
TRIGSCHEMA AS SCHEMA_NAME,
'DROP TRIGGER ' || QUOTE_IDENTIFIER(TRIGSCHEMA) || '.' || QUOTE_IDENTIFIER(TRIGNAME) AS DDL
ROUTINESCHEMA AS SCHEMA_NAME,
'DROP ' || CASE ROUTINETYPE
WHEN 'F' THEN 'SPECIFIC FUNCTION'
WHEN 'M' THEN 'SPECIFIC METHOD'
WHEN 'P' THEN 'SPECIFIC PROCEDURE'
END || ' ' || QUOTE_IDENTIFIER(ROUTINESCHEMA) || '.' || QUOTE_IDENTIFIER(SPECIFICNAME) AS DDL
TYPESCHEMA AS SCHEMA_NAME,
'DROP TYPE ' || QUOTE_IDENTIFIER(TYPESCHEMA) || '.' || QUOTE_IDENTIFIER(TYPENAME) AS DDL
SEQSCHEMA AS SCHEMA_NAME,
'DROP SEQUENCE ' || QUOTE_IDENTIFIER(SEQSCHEMA) || '.' || QUOTE_IDENTIFIER(SEQNAME) AS DDL
WHERE SEQTYPE <> 'I'
SCHEMANAME AS SCHEMA_NAME,
'DROP SCHEMA ' || QUOTE_IDENTIFIER(SCHEMANAME) || ' RESTRICT' AS DDL
SELECT CREATE_TIME, DDL
WHERE SCHEMA_NAME = ASCHEMA!
(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
FOR D AS
ORDER BY CREATE_TIME DESC
EXECUTE IMMEDIATE D.DDL;
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.