Since DB2 will not allow dropping a schema with anything in it, I would like to make a little script to save me the pain and agony of clearing it out by hand every time. I tried this:

BEGIN ATOMIC
FOR V AS
SELECT TABNAME FROM SYSCAT.TABLES
WHERE TABSCHEMA='DIE_SCHEMA'
DO DROP TABLE TABNAME;
END FOR;
END@

but of course that would be to easy. It fails because DROP TABLE is not DML.

So how does one automate this all to common yet painful task?

Use a cursor, fetch loop and dynamic SQL.

Andy

There is a stored procedure that will do that for you:

connect to sample;
call SYSPROC.ADMIN_DROP_SCHEMA('DB2INST1',NULL,'ERROR', 'DROP_ERROR');

This assumes that the database is sample and the schema to drop is 'DB2INST1'

There is also a SP to copy one schema name to another:

call SYSPROC.ADMIN_COPY_SCHEMA('DB2INST1','DB2INST2,'CO PY',NULL,NULL,NULL,'ERROR','ADMIN_COPY_ERRORS');

do a DB2 infocenter search for more info on these and other useful SP's.

For some reason the word 'COPY' displays as 'CO PY' in the second command and I can't fix it (looks fine in edit mode).

I would also use the ADMIN_DROP_SCHEMA procedure that Marcus mentioned for the simple reason that not only table objects are an issue but you could also have routines (stored procs or UDFs), triggers, or other things in the schema, which may have cross-dependencies and need to be dropped in a specific order.