in db2 for z/os segmented tables recover space from dropped tables and mass deletes. during day to day processing do they recover space when a page becomes empty??

we have segmented tablespaces with only one table so the dropped tables does not apply. is there any other advantage to a segmented tablespace with only one table??

Segmented tablespaces have a space map at the beginning of each segment. In some situations this can help DB2 more quickly find empty space during inserts, but the space map must be maintained by DB2. But in certain cases, inserting into segmented tablespaces can cause hot spots at the end of each segment.

IMO, segmented tablespaces are way over-sold if there is only one table in the tablespace. However, for DBA's that don't properly define PCTFREE and don't reorg the tablespaces when needed, segmented tablespaces are probably a good idea.

Some other advantages of segmented tablespaces:

- if you have rows of varying length (eg compressed), the greater detail about how much free space is available on each page means that DB2 can find pages with enough free space for a short row when there isn't enough for the longest possible row.

- mass deletes (delete without a where clause) are done by updating the free space pages, not each page

The free space pages occur rather less frequently than each segment - although more frequently than for simple tablespaces.

Not sure what you mean by "recover space". If you mean "make the space occupied by the deleted row available for other rows", the answer is "yes" for both simple and segmented tablespaces. The difference is that in a multi-table segmented tablespace all rows in the segment must be deleted before the segment is available to store rows from a different table.

James Campbell