Truncate and Delete in DB2

Big Analytics
Big Analytics

Truncate in DB2: It deletes all the rows from a table

TRUNCATE TABLE INVENTORY
DROP STORAGE
IGNORE DELETE TRIGGERS;

Delete in DB2:

DELETE FROM TABLE TABLE_ONE;

It also deletes rows from a table

Differences between TRUNCATE and DELETE

  1. you can use following command which will perform same operation as truncate in DB2 “Alter table table_name activate not logged initially with empty table”. Logging will not happen in this command, while in delete logging will be done.
  2. You can’t rollback in this kind of truncate and you can rollback in delete.
  3. No triggers are fired in alter table command, while delete command will fire triggers.
  4. You can specify where clause in this alters table command
  5. You can’t use conditions (WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.