The SQL standard defines a TRUNCATE TABLE command, which deletes all data contained in a table. Unfortunately, does not support this command yet (version 9.5). Strangely, it is in since version 9.1.
UPDATE: Version 9.7 finally introduced the 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 . Note that on Windows systems, you have to replace /dev/null by NUL.
Option 3: DELETE
DELETE FROM tablename
This usually is very slow, so options 1 and 2 are to be preferred.