EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials DB2 Tutorial DB2 TRUNCATE TABLE
 

DB2 TRUNCATE TABLE

Updated March 6, 2023

db2 truncate table

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

DB2 TRUNCATE TABLE 1

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:

DB2 TRUNCATE TABLE 2

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:

You have made changes to the database

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:

No result

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 –

  1. What is DB2?
  2. Database Management Software
  3. Data Analysis Tools
  4. Data Modelling Tools

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW