Hi,

Is there a way to Drop all the tables created by One Mainframe ID on DB2 z/os. Please advise.

In two steps this is indeed possible:
(1) collect the table names from SYSIBM.SYSTABLES, thereby generating the DROP statements
(2) in a second pass, run the SQL generated in step (1) by cut/pasting it into your SQL frontend (e.g. SPUFI).

Here is a possible SQL statement for pass 1, assuming the user ID is XYZ:

SELECT 'DROP TABLE XYZ.'||rtrim(name)||';'
FROM sysibm.systables
WHERE creator = 'XYZ' AND type = 'T'

(Don't forget the "type" condition: this way only "normal" tables are dropped. Views on those tables will automatically be cascade-dropped.)