Tips for DB2 data deletion

DB2 data deletion is one of our most common operations. The following describes how to delete data in DB2. If you are interested in data deletion in DB2, take a look.

Delete

When you use the delete table command to DELETE data in the entire TABLE, this operation deletes the TABLE records one by one and records them in the active transaction logs. When the data volume in the table is large, if the transaction log of the activity is not large enough, the transaction log is full and the log is rolled back.

Even if the transaction log of the activity is large enough, it takes a lot of time to delete a table with a large amount of data. This problem can be solved by importing an empty file as a data file) and replacing the REPLACE table. For example

Import from/dev/null of del replace into target table name

In this way, transaction logs only record this command and immediately release the occupied space, instead of scanning records one by one like deleting commands, this is similar to dropping the table and then creating a table that is identical but has no 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. Use list tablespace show detail to compare the remaining space in the TABLESPACE after the two commands are executed.

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.

DB2 event monitoring command

Implementation Process of batch execution of DB2 SQL scripts

DB2 Online Export Method

Common db2 stored procedure statements

Usage of the DB2 create server statement