To drop a schema in DB2, you need to ensure that the schema is empty (i.e., all objects within the schema, such as tables, views, and other database objects, are removed). DB2 does not allow dropping a schema that contains objects. Here's a step-by-step guide:
Connect to the Database: Connect to the DB2 database where the schema resides.
db2 connect to <DATABASE_NAME> user <USER_NAME> using <PASSWORD>
List Objects in the Schema (Optional): To view the objects in the schema before dropping it, use:
1 Drop CONSTRAINT
2 DROP SEQUENCE
3 DROP ALIAS
4 DROP TABLE
5 DROP PROCEDURE
6 DROP TRIGGER
SELECT * FROM SYSCAT.TABLES WHERE TABSCHEMA = '<SCHEMA_NAME>';
SELECT * FROM SYSCAT.VIEWS WHERE VIEWSCHEMA = '<SCHEMA_NAME>';
SELECT * FROM SYSCAT.INDEXES WHERE INDSCHEMA = '<SCHEMA_NAME>';
select substr(TABSCHEMA ,1,10) SCHEMANAME , Substr(TABNAME,1,80) TABLENAME, count(*) columnscount
from syscat.columns where TABSCHEMA not like '%SYS%'
group by TABSCHEMA , TABNAME having TABSCHEMA='test' with UR
SELECT TAB.TABSCHEMA AS SCHEMA_NAME ,TAB.TABNAME AS TABLE_NAME
FROM SYSCAT.TABLES TAB WHERE TAB.TYPE = 'T'
AND TAB.TABSCHEMA = 'TEST'
ORDER BY TAB.TABSCHEMA, TAB.TABNAME;
SELECT SEQSCHEMA AS SCHEMA_NAME ,SEQNAME AS SEQ_NAME ,
CYCLE ,ORDER ,CACHE
FROM SYSCAT.SEQUENCES
WHERE SEQSCHEMA = 'TEST'
AND SEQTYPE = 'S'
ORDER BY SEQSCHEMA , SEQNAME;
SELECT TABSCHEMA,TABNAME,BASE_TABNAME
FROM SYSCAT.TABLES
WHERE TYPE = 'A'
AND TABSCHEMA = 'TEST'
SELECT ROUTINESCHEMA AS SCHEMA_NAME ,
ROUTINENAME AS PROCEDURE_NAME
FROM SYSCAT.ROUTINES
WHERE ROUTINETYPE = 'P'
AND ROUTINESCHEMA NOT LIKE 'SYS%'
AND ROUTINESCHEMA NOT LIKE 'SQLJ%'
AND ROUTINESCHEMA = 'TEST'
ORDER BY SCHEMA_NAME , PROCEDURE_NAME;
SELECT TABSCHEMA AS TABLE_SCHEMA ,
TRIGNAME AS TRIGGER_NAME ,
TABNAME AS TABLE_NAME ,
CASE TRIGTIME
WHEN 'B' THEN 'BEFORE'
WHEN 'A' THEN 'AFTER'
WHEN 'I' THEN 'INSTEAD OF'
END AS ACTIVATION
,RTRIM(
CASE WHEN EVENTUPDATE ='Y' THEN 'UPDATE ' ELSE ''
END
||
CASE WHEN EVENTDELETE ='Y' THEN 'DELETE ' ELSE ''
END
||
CASE WHEN EVENTINSERT ='Y' THEN 'INSERT ' ELSE ''
END
) AS EVENT
FROM SYSCAT.TRIGGERS T
WHERE TABSCHEMA NOT LIKE 'SYS%'
ORDER BY TABLE_SCHEMA , TABLE_NAME ,
TRIGGER_NAME ;
Drop Objects in the Schema: Remove all objects (tables, views, indexes, etc.) in the schema. For example:
DROP CONSTRAINT <SCHEMA_NAME>.<TABLE_NAME>;
DROP SEQUENCE <SCHEMA_NAME>.<TABLE_NAME>;
DROP ALIAS <SCHEMA_NAME>.<TABLE_NAME>;
DROP TABLE <SCHEMA_NAME>.<TABLE_NAME>;
DROP VIEW <SCHEMA_NAME>.<VIEW_NAME>;
DROP INDEX <SCHEMA_NAME>.<INDEX_NAME>;
DROP PROCEDURE <SCHEMA_NAME>.<VIEW_NAME>;
DROP TRIGGER <SCHEMA_NAME>.<VIEW_NAME>;
STEP 1 : DROP CONSTRAINT
db2 -x "
SELECT substr(R.reftabschema,1,20) AS P_Schema,
substr(R.reftabname,1,40) AS PARENT,
substr(R.tabschema,1,20) AS C_Schema,
substr (R.tabname,1,40) AS CHILD,
substr(R.constname,1,80) AS CONSTNAME,
substr(LISTAGG(C.colname,', ') WITHIN
GROUP (
ORDER BY C.colname),1,40) AS FKCOLS
FROM syscat.references R,
syscat.keycoluse C
WHERE R.constname = C.constname
AND R.tabschema = C.tabschema
--AND R.tabname = C.tabname
AND R.tabschema in ('TEST')
GROUP BY R.reftabschema,
R.reftabname,
R.tabschema,
R.tabname,
R.constname" > TEST-RI.txt
STEP 2 :
cat TEST-RI.txt | while read P_SCHEMA PARENT C_SCHEMA CHILD CONSTNAME FKCOLS
do
echo "ALTER TABLE $C_SCHEMA.$CHILD DROP CONSTRAINT $CONSTNAME ;" >> TEST_RI_DROP.sql
done
If you want to automate this, you can generate DROP statements dynamically by querying the SYSCAT tables.
DROP SCHEMA TEST RESTRICT
Drop the Schema: Once the schema is empty, you can drop it.
DROP SCHEMA <SCHEMA_NAME> RESTRICT;
RESTRICT ensures that the schema can only be dropped if it contains no objects.
Verify the Schema Is Dropped: Check if the schema still exists:
SELECT SCHEMANAME FROM SYSCAT.SCHEMATA WHERE SCHEMANAME = '<SCHEMA_NAME>';
Ensure you have the necessary privileges to drop objects and the schema. Typically, the schema owner or a database administrator can perform this task.
Use caution while dropping objects, as this action is irreversible and will delete the data permanently.
If you're using AWS RDS for DB2, ensure you adhere to any additional restrictions or processes enforced by the managed service.
Would you like help with generating automated scripts to drop all objects in a schema?