In the earlier version of DB2, in order to empty the tables, we used the DELETE statement. The DELETE statement logs everything, so it's not efficient when we are dealing with a large volume of data. An alternate solution is to load the table using a null file and replacing the table data with it. In DB2 9.7, the TRUNCATE command is introduced, which deletes the data from a table quickly and does not log the activity, resulting in very good performance.
We need one of the following privileges to execute the TRUNCATE command:
DELETEprivilegeCONTROLprivilegeDATAACCESSauthority
TRUNCATE is just a simple command that can also be embedded in any host language.
TRUNCATE TABLE <SCHEMA>.<TABLE> DROP STORAGE IMMEDIATE
TRUNCATE TABLE EMPLOYEEE DROP STORAGE IMMEDIATE
TRUNCATE TABLE <SCHEMA>.<TABLE> REUSE STORAGE IMMEDIATE
TRUNCATE TABLE EMPLOYEEE REUSE STORAGE IMMEDIATE
The following screenshot illustrates the sample output for the TRUNCATE command:
 |
The TRUNCATE statement cannot be rolled back, as with the DELETE statement. This is very useful if you have tons of records to be deleted, saving archive log space and time.
The sample table used in this recipe had 0.2 million rows. TRUNCATE deleted all rows in a second, where the same set of records DELETE would take 10 seconds or more on an average-performing system, and sometimes we may hit the condition when the transaction log is full and may need to change the LOGSECOND/LOGFILSZ parameter.
DELETE FROM EMPLOYEEE
 |
One can use TRUNCATE on a table, which is present on the current server. The TRUNCATE statement cannot be used against the following database objects:
Cataloged table
Nickname
View
Sub table
Staging table
System MQT
Range Clustered table
If the table that we are truncating is a root table in the hierarchy, then all tables in the hierarchy are truncated.
The DROP STORAGE or REUSE STORAGE clause specifies whether to drop or reuse the existing allocated storage space for the table.
The IMMEDIATE clause is mandatory, where it specifies if the TRUNCATE operation is processed immediately and cannot be undone. Always and always, the TRUNCATE statement should be the first statement in the transaction. If we have many statements inside the transaction, other operations can be undone, except the TRUNCATE operation.
IGNORE DELETE TRIGGERS or RESTRICT WHEN DELETE TRIGGERS specifies if any delete triggers, which are defined on the table, would not be activated by the TRUNCATE operation and is the default behavior. Otherwise, an error is returned in the case of RESTRICT WHEN DELETE TRIGGER.
There are different ways to delete the data without logging the activity in the transaction logs other than TRUNCATE. They are explained as follows:
Disable logging for a table:
ACTIVATE NOT LOGGED INITIALLYis an attribute of the table for a unit-of-work operation. During this, any changes made to the table byINSERT, UPDATE, DELETE, CREATE INDEX, DROP INDEX, andALTER TABLEare not logged.Now let's see how we can delete the table data without logging:
db2 +c "ALTER TABLE EMPLOYEEE ACTIVATE NOT LOGGED INITIALLY"
db2 "DELETE FROM EMPLOYEEE"
db2 "COMMIT"
 |
db2 "LOAD FROM 1.del OF DEL REPLACE INTO EMPLOYEEE"
db2 +c "ALTER TABLE EMPLOYEEE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE"
db2 "COMMIT"
 |
Now that we know we have many ways to delete data, we should be using the right method in the right situation. For example, when one uses the ACTIVATE NOT LOGGED INITIALLY option and the unit of work fails, the table has to be rebuilt and the data is lost. In any DB2 High Availability and Disaster Recovery (HADR) setup, be very sure that only tables (the data of which can be easily reproducible) can be marked as NOT LOGGED INITIALLY if required, else we may end up losing the data upon a DR switch. Also, be very cautious while working in a huge data warehouse environment in LOAD with the REPLACE clause. When the data is distributed across multiple partitions, one can expect the APPLHEAPSZ error and the table may go inaccessible. There is also the issue of running out of the utility's heap space UTIL_HEAP_SZ, if you have many data range partitions.