Quick clearing of big data DB2 tables

In the process of using the DB2 database, we sometimes need to quickly clear the DB2 table. Below we will teach you several ways to quickly clear the DB2 table, hoping to learn how to quickly clear the DB2 table.

In a DB2 database, if you want to quickly clear a large TABLE like using truncate table in ORACLE, you can use either of the following methods:

1. Run the "alter table name activate not logged initially with empty table" command.
2. Use an empty file to import and replace the data in the table.

* On windows, you can use EXPORT to EXPORT an empty file, and then use the IMPORT command to IMPORT and replace the data in the empty file. For example:
Export to test. ixf of ixf messages log.txt select * from test where 1 = 2
Import from test. ixf of ixf messages log1.txt replace into test
* On UNIX platforms, in addition to the method, you can also use NULL to import and replace the table data, for example:
Import from/home/null of del replace into test

If you use the "delete table" command to DELETE the data in the entire large TABLE, it will consume a large amount of activity log space because this command deletes data one by one and logs the operation into the transaction log of the activity, the database transaction log may be full or incorrect. In addition, it takes a long time to delete large table data. For a table that belongs to the DMS tablespace, the DELETE command scans records one by one, and the occupied record space is still marked as used by the table without immediately releasing the space, you must use the REORG command to release the remaining space.

Using the preceding two methods to quickly clear a large table, DB2 transaction logs will only record this command and immediately release the occupied space, instead of scanning records one by one like deleting commands, this saves a lot of Database Transaction logs and processing time. Using the LOAD command and the REPLACE parameter can achieve the effect similar to the IMPORT command and the REPLACE, but because the LOAD itself does not remember the log, for recoverable databases, after LOAD is complete, we recommend that you perform online backup immediately. In contrast, the IMPORT command and REPLACE operations are simpler.

Edit recommendations]

Silent status of three DB2 tables

Troubleshooting of DB2 database detachment

How to detach a DB2 database

How to manually Uninstall a DB2 database

DB2 table management statements