A Few Details on the TRUNCATE Statement

TRUNCATE is a simple, yet frequently forgotten way to remove data from your Db2 tables.

The TRUNCATE statement is simply a quick way to remove all of the data from a table. The table can be in any type of table space. And the table can be a declared global temporary table. If the table contains LOB or XML columns, the corresponding table spaces and indexes are also truncated.

For clarification, consider the following example:

TRUNCATE TABLE EXAMPLE_TABLE
  REUSE STORAGE
  IGNORE DELETE TRIGGERS
  IMMEDIATE;

But why do we need this statement? Won't a DELETE without a WHERE clause do the same thing? Well, sort of. TRUNCATE will not log any changes, whereas that DELETE would log your changes. That means TRUNCATE should operate more efficiently. But it could be hard to undo.

Seems easy enough, doesn’t it? But what are those additional parameters? 

Well, REUSE STORAGE tells Db2 to empty the storage that is allocated but keeps it allocated. The alternate, which is the default, is DROP STORAGE. This option tells Db2 to release the storage that is allocated for the table and to make it available for use for the same table or any other table in the table space.  

The next parameter, which is the default if nothing is specified, is IGNORE DELETE TRIGGERS. This tells Db2 to not fire any DELETE triggers. Alternately, you could specify RESTRICT WHEN DELETE TRIGGERS, which will return an error if there are any delete triggers defined on the table. In other words, TRUNCATE is not going to fire any DELETE triggers no matter what you tell it, but it can return an error if DELETE triggers exist that you do not know about (for example). 

Finally, we have the IMMEDIATE option. This causes the TRUNCATE to be immediately executed and it cannot be undone. If IMMEDIATE is not specified you can issue a ROLLBACK to undo the TRUNCATE.

Some More Details

So far, so good. But there always seem to be small things that fall through the cracks until you encounter them in the real world. One example is "How does TRUNCATE operate on archive tables?"

There are two considerations to ponder here. First, will the truncated data be moved to the archive like it would with a DELETE statement? The answer is no, TRUNCATE will NOT move the rows it deletes to the archive table. TRUNCATE simply gets rid of the data without archiving it.

The second consideration is if you TRUNCATE a table that has an archive table, will TRUNCATE remove the data in both the base table and the archive table? Again, the answer is no. TRUNCATE will NOT remove rows in the archive table. You have to remove those separately using a TRUNCATE statement directly against the archive table by name to get rid of archived rows.

Another thing I have seen folks get confused about is the STORAGE option. Some assume that specifying DROP STORAGE will cause the operating system allocated storage/files to be removed. That is not the case. The DROP STORAGE option only releases the storage inside the tablespace. This means that you need to run a REORG to fully clean up the space (after you run the TRUNCATE DROP STORAGE). Also, be sure not to specify REUSE when you run the REORG.

Furthermore, specifying DROP STORAGE should remove extents, not allocated space. So if the tablespace is allocated at say 100 tracks you will never go below 100 tracks without either changing the primary allocation or dropping the object. Now if there were say 5 extents of 10 tracks each, truncating and then reorganizing should get rid of those 50 tracks.

What About PBGs?

For Partition-By-Growth (PBG) table spaces, the REORG utility can physically drop data sets associated with partitions that become entirely empty and are at the "end" of the table space. However, this behavior is controlled by the REORG_DROP_PBG_PARTS subsystem parameter (ZPARM) available since Db2 11 for z/OS:

• REORG_DROP_PBG_PARTS = ENABLE: When this is set, reorganizing the entire PBG tablespace will attempt to consolidate all data into the lowest-numbered partition(s). Any trailing partitions (highest-numbered partitions) that become empty as a result of the REORG will have their associated data sets physically removed (dropped).

• REORG_DROP_PBG_PARTS = DISABLE (Default): Empty trailing partitions are retained, and their data sets are not removed.

So keep this in mind if you are attempting to reclaim space in PBG table spaces that have decreased data volume.

A Useful Little Statement

Overall, TRUNCATE is a useful little tool to keep in your quiver. For certain requirements it can be just what the doctor ordered!

Deleting "n" Rows From a Db2 Table

I regularly receive database- and Db2-related questions via e-mail. And that is great, but I don't always get a chance to respond to everything. If you've sent me a question and I haven't replied, I apologize. But every now and then, I will use one of the e-mail questions in my in-box and write about it in the blog. Today's question is this:

How do you delete N rows from a Db2 table?

Also, how do you retrieve bottom N rows from a Db2 table without sorting the table on key?

And here is my response:

First things first, you need to refresh your knowledge of "relational" database systems and Db2. There really is no such thing as the "top" or "bottom" N rows in a table. Tables are sets of data that have no inherent logical order.

With regard to the result set though, there is a top and a bottom. You can use the FETCH FIRST N ROWS ONLY clause to retrieve only the first N rows, but to retrieve only the bottom N rows is a bit more difficult. For that, you would have to use scrollable cursors.

A scrollable cursor allows you to move back and forth through the results set without first having to read/retrieve all of the rows before. I suggest that you read up on scrollable cursors in the Db2 SQL Reference manual and the Db2 Application Programming manual. All Db2 manuals can be downloaded in Adobe PDF format for free over the IBM web site.

Basically, you would want to FETCH LAST from the scrollable cursor and then loop through with a FETCH PRIOR statement executing the loop N-1 times. That would give you the "bottom" N of any results set -- sorted or not.

As for your other question, I am confused as to why you would want to delete N rows from a table. Doesn't it matter what the data in the rows is? My guess is that you are asking how you would limit a DELETE to a subset of the rows that would apply to the WHERE condition of the DELETE. The answer is, you cannot, at least not without writing some code.

You would have to open a cursor with the same WHERE conditions specifying FOR UPDATE OF. Then you would FETCH and DELETE WHERE CURRENT OF cursor for that row in a loop that occurs N times. Of course, that means you have to write a program to embed that SQL in.

Hope this answer helps...

The DB2 12 for z/OS Blog Series - Part 9: Piece-wise Deletion

Adding the FETCH FIRST clause to the DELETE statement at first appears to be a very simple enhancement, but upon closer examination it is really quite powerful.

Prior to DB2 12 for z/OS, the FETCH FIRST n ROWS ONLY clause could be specified on a SELECT statement. The clause had two impacts:

1. the number specified for n is used by the optimizer to formulate an access path
2. the result set of the SELECT is limited to no more than n rows

With the advent of FETCH FIRST being allowed in a DELETE statement, the number n limits the number of rows that will be deleted in a single DELETE statement. So let's assume that there are 1000 rows for employees in department 200. When we issue this DELETE statement

DELETE FROM EMP

WHERE  EMPDEPT = '200';

All 1000 rows will be deleted. However, if we issue this version of the same statement

DELETE FROM EMP

WHERE  EMPDEPT = '200'

FETCH FIRST 500 ROWS ONLY;

Only 500 rows will be deleted... you could then run it again to delete the remaining 500 rows.

So why would you want to do this? Well, our little example here is not really a good case for using FETCH FIRST on DELETE . Instead, it is primarily designed for situations where a large number of rows would be impacted. For example, assume that instead of 1000 rows there were 2 millions rows. Using FETCH FIRST  to DELETE the rows in batches, instead of 2 million all at once, can make an impossible task possible. The lock management when deleting 2 million rows can render a big, bulk deletion unwieldy as it impacts concurrent access of the data on the same pages where rows are being deleted.

So keep FETCH FIRST  in your arsenal of DB2 12 SQL tools that can help when you need to DELETE  a large number of rows.