Updated March 6, 2023
Introduction to DB2 TRUNCATE TABLE
DB2 truncate table is a statement that is used to delete all the rows present inside a particular table of the DB2 Relational Database Management System. When used without specification of WHERE clause, the DELETE statement removes all the rows of the table, but that is done very inefficiently. While in the case of DB2 truncate table statement, the removal of all the rows of the table is done very efficiently. Here, we will see how we can make the use of DB2 truncate table statement to remove all the rows of the table with its syntax and examples.
Syntax of DB2 TRUNCATE TABLE
The syntax of the TRUNCATE TABLE command comes along with many of the optional features and specifications required while deleting the rows of any table.
The syntax of the DB2 truncate table command is as follows:
TRUNCATE TABLE name of the table
[REUSE STORAGE | DROP STORAGE]
[RESTRICT WHEN DELETE TRIGGERS | IGNORE DELETE TRIGGERS]
[IMMEDIATE]
In the above syntax, the various clauses that are used are explained one by one in the below section:
- name of the table: This is the table name whose rows you wish to delete should be specified here.
- REUSE STORAGE | DROP STORAGE: This is the optional clause that can be mentioned to specify the type of storage methodology that should be followed while making the table empty. It tells whether the storage space for an existing table should be dropped or reused. By default, when not specified, its value is considered to be DROP STORAGE.
- Whenever the DROP STORAGE methodology is followed while deleting the records, then whatever storages are allocated for the table whose data is being deleted is released. This storage space is made available to others as well as the same table provided if they are in the same tablespace.
- When we use the REUSE STORAGE clause while truncating data of the table, then the storage is made empty, but internally the allocation for the table continues.
- RESTRICT WHEN DELETE TRIGGERS | IGNORE DELETE TRIGGERS: This clause helps to mention what should be done in the presence of any triggers that exist in case of the table that we are trying to truncate. Whenever we don’t specify any of the options for this clause, then by default, it goes for considering IGNORE DELETE TRIGGERS clause.
- When IGNORE DELETE TRIGGERS type of methodology is followed while deleting the rows then internally while execution of truncate table command if any of the triggers are there of that table on delete operation then they are not fired.
- When we mention RESTRICT WHEN DELETE TRIGGERS option in this place, then the execution of truncate table command will give out the error if the table which we are trying to truncate has one or more triggers on delete operation.
- IMMEDIATE: This clause helps to mention whether we should allow the rollback of the deleted rows that are truncated by the truncate table command or not. When we do not specify the immediate clause, then we can roll back all the rows that have got deleted because of truncate command by using the ROLLBACK statement, while in a case when we specify the IMMEDIATE clause, then it won’t be possible to rollback the deleted rows.
One more thing to note over here is that when we use the TRUNCATE TABLE statement inside a transaction, this statement should be placed above every other statement in the transaction.
Examples of DB2 TRUNCATE TABLE
Let us have a look at the implementation of the TRUNCATE TABLE command with the help of examples.
We will take two examples. One with the use of an IMMEDIATE clause in it and one without IMMEDIATE clause specification.
Consider an existing table named Sales_Customers that is present in the database.
The table has the following content in it shown in the output of the execution of the below query statement.
Code:
SELECT * FROM [Sales_Customers];
Output:
Let us have a transaction in which we will truncate the Sales_Customers table and then try to rollback it and retrieve the first 5 records of the table. This can be done firstly by beginning the transaction by using BEGIN TRANSACTION, and then executing the truncate query as the first statement inside the transaction by using the statement TRUNCATE TABLE Sales_Customers; And then we will use the ROLLBACK; statement to retrieve back all the deleted rows into the table. Further, we will retrieve the rollbacked rows by using the select statement shown below to get the first 5 records.
Code:
SELECT * FROM Sales_Customers ORDER BY customer_id LIMIT 5;
The execution of the above query statement gives out the following output with the first 5 rows that were truncated. It means that when we do not use IMMEDIATE, the deleted rows by using the truncate table can be rollbacked again.
Output:
Now, let us make use of the same table and try to truncate the rows by using the TRUNCATE TABLE statement along with the IMMEDIATE clause in it.
Code:
TRUNCATE TABLE Sales_Customers
IMMEDIATE;
Output:
If you try to retrieve the rows of the Sales_Customers table now by using the following query statement, then we will not find any rows as they have been deleted.
Code:
SELECT * FROM [Sales_Customers]
Output:
Note that we cannot rollback the deleted rows of the table because of using the IMMEDIATE clause.
Conclusion
We can make use of the TRUNCATE TABLE statement in DB2 to delete all the rows of any particular existing table in a very efficient way. This statement comes with many of the optional clauses that can be added to specify the behavior and execution of the truncate command while deleting the rows. W can control the storage space management for emptied space, rollback possibility and trigger manipulation while deleting using the extra clauses available like [REUSE STORAGE | DROP STORAGE], [IMMEDIATE] and [RESTRICT WHEN DELETE TRIGGERS | IGNORE DELETE TRIGGERS] respectively after TRUNCATE TABLE and name of the table clause.
Recommended Articles
This is a guide to the DB2 TRUNCATE TABLE. Here we discuss the introduction and the examples of the DB2 TRUNCATE TABLE, respectively. You may also have a look at the following articles to learn more –
