No Truncate Table? No Problem!

Published: March 23, 2011

Hey, Ted:

I am working on a project using DB2 for i, but my experience is with other database management systems. I can't find the SQL TRUNCATE TABLE statement. Does DB2 cover this functionality some other way?

--Brad

Even in 7.1, the latest release, IBM does not implement the TRUNCATE TABLE statement. However, since this statement is included in other DB2 products, as well as in Informix, I expect we'll see it eventually as part of our world.

For the benefit of readers who are not familiar with TRUNCATE TABLE, this statement removes all rows of a table (records of a physical file) without dropping the table (deleting the physical file). It is the SQL equivalent of the Clear Physical File (CLRPFM) CL command.

In the meantime, you have nothing to worry about, because this functionality is indeed covered by a feature of the DELETE statement.

When you issue a DELETE without a WHERE clause, you are telling the system to remove all records from the table. If the table is small, the database engine will probably delete the rows individually. However, if the table is large, the system may use either a clear operation (when commitment control is not active) or a change file operation (when commitment control is active.)

I did a quick experiment with two sequential physical files (not SQL tables) that illustrates this point. Commitment control was not active.

One file had 12 records. After I ran a DELETE without a WHERE, Display File Description (DSPFD) showed zero active records and 12 deleted ones.

The second file had about 255,000 records. After a DELETE with no WHERE, DSPFD showed zero active records and zero deleted ones.

Of course, you can always fall back on CLRPFM. It works on all types of physical files, including SQL tables, even when those tables are journaled.

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot