Getting ready

We need one of the following privileges to execute the TRUNCATE command:

How to do it...

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:

How it works...

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:

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's more...

There are different ways to delete the data without logging the activity in the transaction logs other than TRUNCATE. They are explained as follows:

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.