The SQL standard defines a
TRUNCATE TABLE command, which deletes all data contained in a table. Unfortunately, DB2 for Linux, UNIX, and Windows does not support this command yet (version 9.5). Strangely, it is supported in DB2 for z/OS since version 9.1.
UPDATE: Version 9.7 finally introduced the TRUNCATE TABLE command.
Below you find three ways to simulate the behavior of
TRUNCATE TABLE tablename on DB2 LUW.
Option 1: ALTER TABLE
ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
The documentation describes this operation as follows:
Causes all data currently in table to be removed. Once the data has been removed, it cannot be recovered except through use of the RESTORE facility. If the unit of work in which this alter statement was issued is rolled back, the table data will not be returned to its original state.
When this action is requested, no DELETE triggers defined on the affected table are fired. Any indexes that exist on the table are also deleted.
Option 2: IMPORT
IMPORT FROM /dev/null OF DEL REPLACE INTO tablename
In contrast to Option 1, this operation is fully recoverable. Note that on Windows systems, you have to replace
Option 3: DELETE
DELETE FROM tablename
This usually is very slow, so options 1 and 2 are to be preferred.