Erasing all data from table

Details: Category: DB2 LUW Tips & Tricks; Published: Thursday, 28 April 2016 22:46; Written by Super User; Hits: 2403

When you need to remove all data from a table in DB2 on LUW you have four choices depending on whether or not you want it logged or not.

These methods are all good methods but are only valid for either certain types of tables or situations. It is the DBA's and/or Developer's task to choose the correct method in their applications and work.

1) Using the DELETE statement

This statement is the most used method in applications as this method is logged and thus provides rollback if application fails at some critical point.

Example :

DELETE FROM MYSCHEMA.EMP;

For large table this method is the slowest of all these methods because of the heavy logging that it produces. A mass delete rollback will certainly slow down your online system or in worst case make it unusable for a period of time.

2) Using the TRUNCATE statement

This method was introduced in DB2 version 9.7 and provides a fast data removal statement for applications that is not logged with the ability to reclaim or reuse storage as well as ignoring delete triggers on tables if they exists.

This method as some rules/restrictions though : (from the online IBM manual )

        Referential Integrity: The table, and all tables in a table hierarchy, must not be a parent table in an enforced referential constraint (SQLSTATE 428GJ). A self-referencing RI constraint is permitted.
        Partitioned tables: The table must not be in set integrity pending state due to being altered to attach a data partition (SQLSTATE 55019). The table needs to be checked for integrity prior to executing the TRUNCATE statement. With DB2® Version 9.7 Fix Pack 1 and later releases, the table must not have any logically detached partitions (SQLSTATE 55057). The asynchronous partition detach task must complete prior to executing the TRUNCATE statement.
        Exclusive Access: No other session can have a cursor open on the table, or a lock held on the table (SQLSTATE 25001).
        WITH HOLD cursors: The current session cannot have a WITH HOLD cursor open on the table (SQLSTATE 25001).

Example :

TRUNCATE TABLE MYSCHEMA.EMP IGNORE DELETE TRIGGERS DROP STORAGE IMMEDIATE;

3) Using the LOAD or IMPORT command

These methods are the most used by DBA's and in scripting as these methods are very fast but you will need to do some additional tasks as well after load or in worst case do a recovery from backup.

Example :

IMPORT FROM /dev/null OF DEL REPLACE INTO tablename

LOAD FROM /dev/null OF DEL REPLACE INTO tablename

In Windows you replace /dev/null with NUL

4) Using the ALTER TABLE statement

This method is not logged and the data is not recoverable except through RESTORE facility. And like TRUNCATE the DELETE triggers are ignored / disabled.

Also "Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE in the same unit of work after the table is altered by this statement are not logged. Any changes made to the system catalog by the ALTER statement in which the NOT LOGGED INITIALLY attribute is activated are logged. Any subsequent changes made in the same unit of work to the system catalog information are logged.

At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated and all operations that are done on the table in subsequent units of work are logged." (from the online IBM Manual ).

This method is fast and provides you with the ability to replace large amount of data and do some additional updates and other work in the same unit of work without putting heavy logging on the system before the data is made available to other transactions.

Example :

 ALTER TABLE MYSCHEMA.EMP ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE; 
 INSERT INTO MYSCHEMA.EMP SELECT * FROM MYOTHERSCHEMA.EMP; 
 UPDATE MYSCHEMA.EMP SET SALARY = SALARY * 1.20; 
 COMMIT;