Deleting Database (Data) Objects

Just as it is important to be able to create and modify objects, it is important to be able to delete existing objects when they are no longer needed. Most existing objects can be removed from a database by executing some form of the DROP SQL statement. The basic syntax for this statement is:

 DROP [ObjectType] [ObjectName] 

where:

ObjectType	Identifies the type of object that is to be deleted (dropped).
ObjectName	Identifies the name that has been assigned to the object that is to be deleted.

Thus, if you wanted to delete a table that has been assigned the name SALES and resides in a schema named CORP, you could do so by executing a DROP SQL statement that looks something like this:

 DROP TABLE corp.salses 

Database objects can also be dropped from the Control Center by highlighting the appropriate object and selecting the appropriate action from any object menu found. Figure 4-27 shows the Control Center menu items that must be selected in order to drop a particular object (in this case, a table).

Figure 4-27: Dropping a table from the Control Center.

It is important to keep in mind that when an object is dropped, its removal may affect other objects that depend upon its existence. In some cases, when an object is dropped, all objects dependent upon that object are dropped as well (for example, if a table space containing one or more tables is dropped, all tables that resided in that table space, along with their corresponding data, are also dropped). In other cases, an object cannot be dropped if other objects are dependent upon its existence (for example, a schema can only be dropped after all objects in that schema have been dropped). And it goes without saying that built-in objects, such as system catalog tables and views, cannot be dropped.

Tip 

The rules that govern how objects can be dropped as well as the rules that determine how dependent objects are affected when an object is dropped can be found in the IBM DB2 SQL Reference, Volume 2 product documentation.