Dropping all tables from a DB2 schema

Use this query to select and drop all tables of a schema. 

Note: this query constructs a sql statement for every table , e.g DROP TABLE MYTEST.mytable; .

--example SCHEMA = MYTEST
 db2 -x "select 'DROP TABLE '||rtrim(tabschema)||'.'||rtrim(tabname) ||’;’  from syscat.tables where tabschema = 'MYTEST'"

If you’re intention is to drop all the tables and the schema, an alternative approach is to use the ADMIN_DROP_SCHEMA  procedure . Read DB2 - ADMIN_DROP_SCHEMA to drop a schema

Read More

DB2 Database objects overviews - DBA DB2

DB2 SET CURRENT SCHEMA - DBA DB2