TRUNCATE TABLE in DB2 LUW

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 /dev/null by NUL.

Option 3: DELETE

DELETE FROM tablename

This usually is very slow, so options 1 and 2 are to be preferred.

This entry was posted in DB2. Bookmark the permalink.

5 Responses to TRUNCATE TABLE in DB2 LUW

  1. Markus says:

    Thank you for that nice tipps!

    regards,
    Markus

  2. jung says:

    thank

  3. MOHAMMAD says:

    THANK YOU FOR NICE HELP

  4. prosp4300 says:

    For a temporary table, seems only DELETE FROM works, any other better way, thanks

  5. bala says:

    thank you , option 1 has taken less time compare to option 2

Leave a Reply

Your email address will not be published. Required fields are marked *