Implications of dropping a table

Dropping a table has several implications that you should be aware of.

Begin general-use programming interface information.The DROP TABLE statement deletes a table. For example, to drop the project table, run the following statement: 

DROP TABLE DSN8910.PROJ;

The statement deletes the row in the SYSIBM.SYSTABLES catalog table that contains information about DSN8910.PROJ. This statement also drops any other objects that depend on the project table. This action results in the following implications:

  • The column names of the table are dropped from SYSIBM.SYSCOLUMNS.
  • If the dropped table has an identity column, the sequence attributes of the identity column are removed from SYSIBM.SYSSEQUENCES.
  • If triggers are defined on the table, they are dropped, and the corresponding rows are removed from SYSIBM.SYSTRIGGERS and SYSIBM.SYSPACKAGES.
  • Any views based on the table are dropped.
  • Packages that involve the use of the table are invalidated.
  • Cached dynamic statements that involve the use of the table are removed from the cache.
  • Synonyms for the table are dropped from SYSIBM.SYSSYNONYMS.
  • Indexes created on any columns of the table are dropped, along with any pending changes that are associated with the index.
  • Referential constraints that involve the table are dropped. In this case, the project table is no longer a dependent of the department and employee tables, nor is it a parent of the project activity table.
  • Authorization information that is kept in the Db2 catalog authorization tables is updated to reflect the dropping of the table. Users who were previously authorized to use the table, or views on it, no longer have those privileges, because catalog rows are deleted.
  • Access path statistics and space statistics for the table are deleted from the catalog.
  • The storage space of the dropped table might be reclaimed.
    • Start of changeFL 506 If the table space containing the table is a universal table space, a LOB table space, or implicitly created (using the CREATE TABLE statement without the TABLESPACE clause), the table space and any pending changes that are associated with the table space are also dropped. If the data sets are in a storage group, dropping the table space reclaims the space. For user-managed data sets, you must reclaim the space yourself.End of change
    • If the table space containing the table is partitioned, or contains only the one table, you can drop the table space.
    • If the table space containing the table is segmented, Db2 reclaims the space.
    • If the table space containing the table is simple, and contains other tables, you must run the REORG utility to reclaim the space.
  • If the table contains a LOB column, the auxiliary table and the index on the auxiliary table are dropped. Start of change FL 506 The LOB table space is also dropped, regardless of whether it was implicitly or explicitly created.End of change

If a table has a partitioning index, you must drop the table space or use LOAD REPLACE when loading the redefined table. If the CREATE TABLE that is used to redefine the table creates a table space implicitly, commit the DROP statement before re-creating a table by the same name. You must also commit the DROP statement before you create any new indexes with the same name as the original indexes.End general-use programming interface information.