if you drop a table , all indexes, relations get dropped with it.
Only the database and the tablespace (if not implicitly created) remain.
select char('drop table "' !! strip(creator) !! '"."' !! strip(name) !! '" ;') from sysibm.systables where creator = 'userid'
generates all drops necessary.
run dsntiaul with this select and then run DSNTIAD with the output.
actually we run the follwing to clean up all user-created tables which are older than 14 days and who didn't bother to specify a database or tablespace:
SELECT CHAR('DROP TABLE "'
CONCAT RTRIM(CREATOR)
CONCAT'"."'
CONCAT RTRIM(NAME)
CONCAT '";')
FROM SYSIBM.SYSTABLES
WHERE TYPE IN ('M','T')
AND (DBNAME = 'DSNDB04' OR DBNAME LIKE 'DSN00%')
AND (ALTEREDTS + 14 DAYS) < CURRENT_TIMESTAMP
FOR FETCH ONLY WITH UR;