Problem:

Is it possible to  drop all tables for a specific Auth-Id, within a specific location  ?

Resolution:

Create a .SQL file containing the following statements and run it in the SQL Wizard.

Note - To make this SQL work, the reference to MYAUTH should be changed to meet indinvidual requirements.

DECLARE GLOBAL TEMPORARY TABLE DROPTAB (SQL VARCHAR(512));

INSERT INTO SESSION.DROPTAB

SELECT 'DROP TABLE ' || CREATOR || '.' || NAME || ';'

FROM SYSIBM.SYSTABLES

WHERE CREATOR = 'MYAUTH';

SELECT * FROM SESSION.DROPTAB;

If the select shows a good list of tables to drop, an SQL script can be generated using the SQLWizard export feature.

Open a new export.  

Choose "ASCII Delimited" as the format.  

Enter SESSION.DROPTAB into the "Table name" text box.  

Un-check the "Heading" and "Quote" check boxes.  

Change the "Fld Delim" to a semicolon.  

Change the extension on the "File Name" to SQL rather than FRE.  

Run the export.

This will produce an SQL script that will, when run, complete the deletion task.

Please beware that, the SESSION.DROPTAB table will automatically be dropped when, another  COMMIT is executed or SQLWizard is closed..