IBM DB2 9.7 Advanced Application Developer Cookbook

Using the new TRUNCATE statement

In the earlier version of DB2, in order to empty the tables, we used the DELETE statement. The DELETE statement logs everything, so it's not efficient when we are dealing with a large volume of data. An alternate solution is to load the table using a null file and replacing the table data with it. In DB2 9.7, the TRUNCATE command is introduced, which deletes the data from a table quickly and does not log the activity, resulting in very good performance.

Getting ready

We need one of the following privileges to execute the TRUNCATE command:

• DELETE privilege
• CONTROL privilege
• DATAACCESS authority

How to do it...

TRUNCATE is just a simple command that can also be embedded in any host language.

• Truncating a table with DROP ...