Deleting a DB2 UDB Database

Suppose you create a test database that mirrors a production database, so you can develop a new application without affecting your production environment. When the development process is complete and the test database is no longer needed, you might want to get rid of it, so the storage space it occupied can be used for other things. This brings up an interesting question: How do you go about destroying a DB2 UDB database?

Just as there are two ways to create a DB2 UDB database, there are two ways to destroy one: by using the Control Center or by using the DROP DATABASE command. By highlighting the object shown in the objects pane of the Control Center that corresponds to the database to be destroyed and right-clicking the mouse button, you can display a menu that contains a list of options available for that particular database. If you then select the Drop item from this menu, you will be presented with a Confirmation dialog, where you will be asked to confirm your decision to delete the database (the name of the database you are about to delete will be displayed in this dialog in an effort to prevent you from accidentally deleting the wrong one). Once you confirm that the database specified is to be deleted, its contents are destroyed, its entry is removed from both the system and the local database directory, and its tablespace storage containers are made available for other databases to use. Figure 4-5 shows the Control Center menu items that must be selected in order to drop (delete) an existing database.

Figure 4-5. Destroying a database from the Control Center.

A database can also be deleted by executing the DROP DATABASE command. The syntax for this command is:

 DROP [DATABASE  DB] [  DatabaseAlias  ] <AT DBPARTITIONNUM> 

where:

DatabaseAlias

Identifies the alias assigned to the database to be destroyed.

So, if you wanted to destroy a database that has the name and alias TEST_DB, you could do so by executing a DROP DATABASE command that looks like this:

 DROP DATABASE TEST_DB 

It is important to remember that only users with System Administrator (SYSADM) authority or System Control (SYSCTRL) authority are allowed to drop an existing a database.