DB2 UDB – Truncate Tables

DB2 – Equivalent to SQL Server/Oracle TRUNCATE command

Problem
This technote describes how to delete the contents of a table with minimal logging overhead

Solution
Use the LOAD or IMPORT command in REPLACE mode, and import an input file with no rows. This will cause the table to be truncated with very little or no logging overhead.

There are two ways to do this in DB2® Universal Database™ (DB2 UDB). A fast way is perform an IMPORT REPLACE with a delimited file that contains no rows, for example:

import from c:\ixf\rep.del of del replace into testvar

IMPORT is usually the best choice, because it does not lock the table space. If the table has many active pages in the bufferpool, LOAD will be faster, as IMPORT will flush the bufferpool. LOAD must be used if the target table has referential integrity dependencies or summary tables defined on it. In DB2 UDB Version 7 and earlier, LOAD requires exclusive access to the table space. If you plan to use LOAD a lot, put each table that gets LOADed (or uses LOAD to be emptied) in its own table space, or use IMPORT REPLACE.

Note that on UNIX® and Linux® platforms, you can also IMPORT or LOAD from /dev/null