Four ways to delete DB2 data

There are many ways to delete DB2 data. The following describes the four most commonly used methods to delete DB2 data. If you have encountered any problems in deleting DB2 data, take a look.

1. Use the DELETE statement, namely:

Delete from <Table Name>

This statement clears all data in the table. However, because this operation logs, the execution speed is relatively slow. Note that if the table is large, to ensure the deletion operation is successful, check whether there is sufficient log space.

2. Use the not logged initially option, that is:

Alter table <TABLE Name> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

This method can be used only when the not logged initially option is selected for the table to be operated during creation. The entire delete operation will not be logged, so the execution speed is the fastest among several methods, but the deleted data cannot be recovered. However, this method is prone to errors.

3. Use the IMPORT or LOAD command, that 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 clears all data in the table, and then IMPORT/LOAD imports empty data into the table, thus clearing the data.

4. Use the DROP/create table statement, that is:

Drop table <TABLE Name>
Create table <TABLE Name> <field definition>

If you have saved a table definition statement or used the DB2LOOK command to obtain the table definition script, you can also delete the entire table and recreate the table. If the table is large, data is cleared faster than method 1.

How to resize A DB2 tablespace

DB2 connection Server Configuration

Learn about the DB2 lock types

Deep Analysis of DB2 Lock Mechanism

Implementation of adding verification constraints to DB2