After creating a table in the DB2 database system, you will inevitably need to clear the data in the table. This article will introduce four ways to delete data in the DB2 database for your reference, you can make the selection based on the actual situation, hoping to help.
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.
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.
If you want to implement the truncate table effect, use the import from empty file method. Other methods are not good.