Using the new TRUNCATE statement

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.

Getting ready

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

• DELETE privilege

• CONTROL privilege

• DATAACCESS authority

How to do it...

TRUNCATE is just a simple command that can also be embedded in any host language.

• Truncating a table with DROP STORAGE: The TRUNCATE command deletes all the rows from a table. We have the option to retain or drop the space allocated for the table. The default is to drop the storage.

TRUNCATE TABLE <SCHEMA>.<TABLE> DROP STORAGE IMMEDIATE
TRUNCATE TABLE EMPLOYEEE DROP STORAGE IMMEDIATE

• Truncating a table with REUSE STORAGE: We can use the REUSE STORAGE clause in the TRUNCATE command, if we do not want to drop the storage. In this case, the space remains allocated to the table and can be used for the new data.

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:

• 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'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:

• Disable logging for a table: ACTIVATE NOT LOGGED INITIALLY is an attribute of the table for a unit-of-work operation. During this, any changes made to the table by INSERT, UPDATE, DELETE, CREATE INDEX, DROP INDEX, and ALTER TABLE are 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"

• Using LOAD with REPLACE: Another method is to use the LOAD command to delete the data where 1.del is an empty file.

db2 "LOAD FROM 1.del OF DEL REPLACE INTO EMPLOYEEE"

• Replace the data with an empty table: Yet another method is to use NOT LOGGED INITIALLY WITH EMPTY TABLE.

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.