Removing Data

Many methods are available to remove data from a DB2 database. To remove all of the data within a database, perform the DROP DATABASE command. This might remove more data than you intended because the entire database, including its configuration, will be physically removed.

It is also possible to remove data using the DROP TABLESPACE or DROP TABLE statements. These statements are usually only issued by the SYSADM or DBADM, since they will remove large amounts of data. If you want to remove all of the data records from a table, it is easier and quicker to perform the DROP TABLE statement. If the table is dropped, it must be recreated before any data can be populated again in the table.

To remove a single data record or a group or records from a table, the DELETE statement should be used. The syntax of the DELETE statement is different from the SELECT or INSERT statements because columns cannot be selected, only rows can be deleted. The DELETE statement also can be used with views. However, there are restrictions on the type of views that can be used within a DELETE statement.

In general, there are two kinds of DELETE statements:

• Searched delete— This DELETE statement is used to delete one or multiple rows from a table. It can use a WHERE clause to establish the delete condition.

• Positioned delete— This kind of DELETE operation is always embedded into a program. It uses cursors to delete the row where the cursor is positioned.

This section focuses on the searched delete. The following SQL statement deletes employees who do not have a telephone number loaded into the table. We will use a searched delete to accomplish this task.

DELETE FROM employee
WHERE phoneno IS NULL

This example uses a WHERE clause to delete the data that meets a specific criterion. To verify the result of the DELETE statement, you can issue a SELECT statement with the same WHERE clause. If the DELETE was successful, the SELECT will return an empty set. If no rows were deleted due to the WHERE clause, DB2 will return SQL0100W indicating that no row was found for DELETE.

A delete also can become more sophisticated by using subselects. The next SQL statement deletes all the employees who do not have a résumé on file:

DELETE from employee
WHERE empno NOT IN
     (SELECT DISTINCT empno
      FROM emp_resume)

In this example, a subselect retrieves the empno of the employees who have a résumé in the emp_resume table. Any empno not in this list will be deleted from the employee table. This is not a very practical query but it does illustrate a method for deleting data.

Deleting All the Rows in a Table

You can delete all the rows in a table if you don't specify a search condition in your DELETE statement. You must be aware of the implications of this type of statement. This is not the only way to delete all the rows in a table. You also can delete all the rows in a table if all the rows meet the search condition.

Deleting all the rows in a table by using a DELETE statement might not be the most efficient method. This kind of statement can consume a lot of log space when your tables are large.

If a table has been created with the NOT LOGGED INITIALLY option, there is a way to delete all of the contents of the table without physically dropping it. Use the following command to tell DB2 to drop the contents of the records (without logging):

ALTER TABLE x ACTIVATE
NOT LOGGED INITIALLY WITH EMPTY TABLE.

The advantage of deleting rows with this technique is that definitions relying on this table are not dropped.