Summary: in this tutorial, you will learn how to use the Db2
TRUNCATE TABLE statement to delete all rows from a table.
Introduction to Db2
TRUNCATE TABLE statement
Fortunately, Db2 provides the
TRUNCATE TABLE that also delete all rows from a table, but in a more efficient way.
The following shows the syntax of the
TRUNCATE TABLE statement:
TRUNCATE TABLE table_name [DROP STORAGE | REUSE STORAGE] [IGNORE DELETE TRIGGERS | RESTRICT WHEN DELETE TRIGGERS] [IMMEDIATE]
In this syntax:
First, specify the name of the table from which you want to delete all data.
DROP STORAGE or
REUSE STORAGE option determines whether Db2 should drop or reuse the existing storage allocated for the table.
DROP STORAGEreleases all storages allocated for the table and make the storage available for use for the same table or any other tables within the tablespace.
REUSE STORAGEmakes all storages empty, but continues allocating for the table.
The default option is
IGNORE DELETE TRIGGERS and
RESTRICT WHEN DELETE TRIGGERS options determine the behavior of the truncate operation when the table has associated delete triggers.
IGNORE DELETE TRIGGERSoption will not fire any trigger defined on the table.
RESTRICT WHEN DELETE TRIGGERSoption will result in an error if the table has one or more deleted triggers.
By default, the
TRUNCATE TABLE statement uses
IGNORE DELETE TRIGGERS option.
IMMEDIATE option, if specified, will delete all rows from the table without the ability to roll back. On the other hand, if you don’t specify the
IMMEDIATE option, you can use a
ROLLBACK statement to undo the truncate operation.
Notice that when you use the
TRUNCATE TABLE statement in a transaction, you must place it as the first statement.
TRUNCATE TABLE examples
Let’s take some examples of using the
TRUNCATE TABLE statement.
First, create a new table named
books2 that has the same structure as the
CREATE TABLE books2 LIKE books;
Then, insert all rows from the
books table to the
books2 table using the
INSERT INTO SELECT statement:
INSERT INTO books2 SELECT * FROM books;
1) Using Db2
TRUNCATE TABLE statement within a transaction example
First, start a new transaction:
Second, truncate the
TRUNCATE TABLE books2;
Third, rollback the transaction:
Fourth, verify the truncate operation by querying some rows from the
SELECT title FROM books2 ORDER BY title LIMIT 5;
Here is the output:
As you can see clearly from the output, the truncate operation on the
books2 table was rolled back successfully.
2) Using Db2
TRUNCATE TABLE statement to truncate a table immediately
The following example uses the
TRUNCATE TABLE statement to truncate the
books2 table immediately with the
TRUNCATE TABLE books2 IMMEDIATE;
If you query data from the
books2 table, you will get an empty result set:
SELECT * FROM books2;
In this tutorial, you’ve learned how to use the Db2 statement to delete all data from a table.