DROP TABLE

DROP TABLE removes a table and all of its data permanently from the DB2 catalog.

DROP TABLE syntax and effect

• DROP TABLE is a DDL statement. The syntax is very simple:-
• DROP TABLE MM01.EMP_TEMP;
• Dropping a table deletes every row, drops its indexes, and removes the table definition from the DB2 catalog.
• Synonyms created on the table are dropped automatically, but aliases on the table are retained.
• Views built on the dropped table become invalid and must be dropped or recreated.

DROP TABLE versus DELETE

• DELETE removes rows but keeps the table definition. DROP TABLE removes the table itself.
• DELETE can be rolled back. DROP TABLE cannot be rolled back easily, so always take a backup before dropping.
• If you only want an empty table, use DELETE FROM tablename without a WHERE clause, or run the REORG utility with DISCARD.
• Dropping a parent table that has foreign key relationships fails unless the child tables are dropped first or the constraints are removed.

Authorization and safety

• You need DROP authority on the table or DBADM authority to drop a table.
• Before dropping, run an image copy (COPY utility) or unload the data so the table can be recovered if it was dropped by mistake.
• In production, tables are dropped only during approved change windows and after DBA review.