EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

DB2 TRUNCATE TABLE

Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
Home Data Science Data Science Tutorials DB2 Tutorial DB2 TRUNCATE TABLE

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.

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,938 ratings)
  • 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
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More