DB2 - SQL DROP Statement

The DROP statement removes an object in the current server.

The DROP DATABASE statement is used to drop an existing Storage group, Database, Tablespace, Table, Index, View, Alias, Synonym.

Database:

Whenever a database is dropped, all of its tablespaces, tables, index spaces, and indexes are also dropped.

Tablespace:

Whenever a tablespace is dropped, all the tables in the tablespace are also dropped.

Table:

Whenever a table is dropped, all referential constraints in which the table is a parent or dependent, and all synonyms, views, and indexes defined on the table are also dropped. If the table space for the table was implicitly created, it is also dropped. Alias is not dropped.

DROP STOGROUP stogroup-name
     DATABASE database-name
     TABLESPACE table-space-name
     TABLE table-name
     INDEX index-name
     VIEW view-name
     ALIAS alias-name
     SYNONYM synonym-name

Example 1:

The DROP DATABASE statement is used to drop an existing SQL database.

Be careful when you deleting a database. Deleting a database will result in loss of complete information stored in the database.

The following SQL statement drops the existing database "IBM_DB":

DROP DATABASE IBM_DB;

Once a database is dropped, you can check it in the list of databases with the following SQL command: SHOW DATABASES.

Example 2:

Let see how to drop a table in a database.

The following SQL DROP TABLE statement to drop the Table_Students table:

DROP TABLE Table_Students

Mostly developer don't have access to execute the SQL DROP statements. Ony Admin have the privilege to use SQL DROP statement.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!