DB2 deleting data from a table

DB2 database system to build the table, there will inevitably be the data in the table to clear the demand, this article will introduce you to the DB2 database of four methods of data deletion, for your reference, you can choose according to the actual situation, hope to be helpful.

1. Use the DELETE statement, which is:

DELETE from < table name >

This statement clears all the data in the table, but because this operation will log, the execution will be slower, and it is important to note that if the table is large, you should consider whether there is enough log space to keep the delete operation successful.

2. Use the not logged initially option, i.e.:

ALTER table < table name > ACTIVATE not logged initially with EMPTY table

This method is only available if the table you are manipulating is defined when the not logged initially option is selected at the time of creation. The entire delete operation will not log, so execution speed is the fastest of several methods, but the deleted data is not recoverable.

3. Use the IMPORT or LOAD command, which is:

IMPORT From/dev/null of DEL REPLACE into < table name >-(Unix System)
or IMPORT from < empty file > of DEL REPLACE into < table name >

LOAD From/dev/null of DEL REPLACE into < table name > nonrecoverable-(Unix system)

or LOAD from < empty file > of DEL REPLACE into < table name > nonrecoverable

In this method, the REPLACE import method first empties all the data in the table, and then import/load imports the empty data into the table, enabling the cleanup of the data.

4. Use the Drop/create TABLE statement, which is:

DROP table < table name >
CREATE table < table name > < Field definition >

If you save a definition statement for a table, or if you have used the Db2look command to obtain a script for the table definition, you can delete the entire table and then recreate the table. If the table is large, the data can be emptied in this way faster than method one.

If you want to implement the effect of TRUNCATE TABLE, use the import from empty file method, other methods are not good.

DB2 deleting data from a table