You use DROP TABLE to delete a base table and the associated indexes.
When a base table is deleted, all the table and column privileges for this base table are revoked from the current authorization identifier. Table and column privileges that have been passed on are also revoked.
The BASE_TABLES view in the INFORMATION_SCHEMA provides you with information on which base tables have been defined (see chapter "Information schemas").
You can also use DROP TABLE to delete BLOB tables. In this case all BLOBs contained therein will also be deleted.
The current authorization identifier must own the schema to which the table belongs.
DROP TABLE table [DEFERRED] { CASCADE | RESTRICT }
table
Name of the base table to be deleted.
DEFERRED
This clause initiates high-speed deletion of the table in which only the contiguous part of the table and of the associated explicit and implicit indexes are deleted. Any relocations which exist are retained.
The next time the user space is reorganized using the utility statement REORG SPACE all the existing tables and indexes are recovered in the user space.
The relocations which have not been deleted then also disappear.
Information on the storage structure of base tables is provided in the “ Core manual”.
In the case of partitioned tables the DEFERRED clause applies for all partitions; it cannot be restricted to individual partitions.
The DEFERRED clause can only be specified in the case of explicit deletion. In the case of implicit deletion, e.g. with DROP SPACE ... CASCADE, it cannot be specified.
When DEFERRED is to apply only for the table but not for the indexes, the indexes must first be deleted using DROP INDEX (without specifying DEFERRED). The table can then be deleted using DROP TABLE ... DEFERRED.
DEFERRED omitted:
SESAM/SQL deletes the table and all associated indexes. This can be time-consuming when indexes are very large and fragmented.
CASCADE
The base table table and all the associated indexes are deleted. All the views, routines,and integrity constraints that reference table directly or indirectly are also deleted.
RESTRICT
The deletion of the base table table is restricted. The base table table cannot be deleted if it is used in a view definition, a routine or an integrity constraint of another base table.
Examples
In this example, the CUSTOMERS table is deleted only if all integrity constraints of other base tables that reference the CUSTOMERS table have been deleted beforehand. In addition, the CUSTOMERS table must not be used in any view definition.
ALTER TABLE contacts DROP CONSTRAINT contact_cust_num_ref_customers CASCADE
ALTER TABLE orders DROP CONSTRAINT o_cust_num_ref_customers CASCADE
DROP TABLE customers RESTRICT
The example deletes the IMAGES and DESCRIPTIONS tables, together with all indexes, views, and integrity constraints that reference these tables.
DROP TABLE images CASCADE
DROP TABLE descriptions CASCADE
See also
CREATE TABLE, ALTER TABLE