I have previously written a few tips about fast deletion of rows from DB2 tables. I completely missed to mention that deleting all rows from a socalled segmented tablespace is extremely fast. DB2 cheats and writes only one log record without regard of the number of rows in the table. The rows are marked as deleted but not removed. Well, what is a segmented tablespace anyway.

Actually you have been able to create segmented tablespaces as long as I can remember. I think they were introduced in DB2 version 3 in the early 1990th. They were primarily invented because DB2 has a hard time when a tablespace consist of more than one table and the tablespace is not segmented. Any tablespacescan of a table in a nonsegmented tablespace will scan all tables in the tablespace. This is the main reason behind the fact that allmost all installations only have one table in each tablespace. In a segmented tablespace a tablespacescan is only scanning the table.

If you want to define many tables in a single tablespace you should define them in a segmented tablespace. If you want to speed up your DELETE FROM without a WHERE clause then a segmented tablespace is the answer to your prayers. A segmented tablespace is created just like a normal tablespace adding the parameter SEGSIZE nn. nn must be a number divisible by 4 and not greater than 64. The question is now how to set nn. nn is the number of pages in a segment and all rows in a segment belongs to the same table. If the tables in the tablespace are small a low value for nn is a good choice. If they are large a high value for nn is a good choice. If the tables are both small and large it becomes extremely difficult to choose a good value for nn.

You can read a lot more about setting SEGSIZE and the influence it has on sequential prefetch in Administration Guide. My conclusion after reading all the stuff about SEGSIZE is that you should put small tables into a segmented tablespace for small tables and large tables into a segmented tablespace for large tables. Practical experience has also taught me to pick a small SEGSIZE for small tables. Especially if you still want to have one table in each tablespace. As soon as you use a SEGSIZE greater than 8, DB2 will allocate at least 2 tracks even though you specify PRIQTY as 48 or less. If SEGSIZE is set to 64, DB2 allocates at least 7 tracks. If you have set PRIQTY to 48 or less and SEGSIZE to 64 and you add rows to the table and passes the limit of PRIQTY then DB2 allocates more tracks corresponding to the value of SECQTY even though there is already allocated plenty of room in the tablespace. I have not tried this on DB2 version 8 so maybe it has improved.

In any case you must be a little careful using segmented tablespaces, but they are a significant improvement. Actually DB2 user organisations like IDUG and GSE wants IBM to implement segmentation for partitioned tablespaces also in order to have the fast delete, but they did not succeed in DB2 version 8. Maybe they have in DB2 version 9.

The most annoying thing about segmented tablespaces is that once you have set the SEGSIZE you cannot change it. You have to drop and create the tablespace in order to change the SEGSIZE. And another warning: you cannot DSN1COPY an image copy taken with the old SEGSIZE into the new tablespace with the changed SEGSIZE. Been there, did not work. This makes it even more important to choose the correct SEGSIZE the first time because it is extremely hard to change later.