Segmented (non-UTS) table spaces (deprecated)

A table space that is segmented is useful for storing more than one table, especially relatively small tables. The pages hold segments, and each segment holds records from only one table.

Recommendation: Start of changeSegmented (non-UTS) table spaces are deprecated. They are supported in DB2® 10, but support might be removed in the future. Use universal table spaces instead.End of change

Segmented table spaces hold a maximum of 64 GB of data and can contain one or more VSAM data sets. A table space can be larger if either of the following conditions is true:

  • The table space is a partitioned table space that you create with the DSSIZE option.
  • The table space is a LOB table space.

Table space pages can be 4 KB, 8 KB, 16 KB, or 32 KB in size. The pages hold segments, and each segment holds records from only one table. Each segment contains the same number of pages, and each table uses only as many segments as it needs.

When you run a statement that searches all the rows for one table, DB2 does not need to scan the entire table space. Instead, DB2 can scan only the segments of the table space that contain that table. The following figure shows a possible organization of segments in a segmented table space.

Figure 1. A possible organization of segments in a segmented table space
Begin figure description. Segment 1 contains Table A, Segment 2 contains Table B, Segment 3 contains Table C, Segment 4 contains Table A, Segment 5 contains Table B, and so on. End figure description.

When you use an INSERT statement, a MERGE statement, or the LOAD utility to insert records into a table, records from the same table are stored in different segments. You can reorganize the table space to move segments of the same table together.

Definition of a segmented (non-UTS) table space

A segmented (non-UTS) table space consists of segments that hold the records of one table.

You define a segmented (non-UTS) table space by using the CREATE TABLESPACE statement with a SEGSIZE clause. If you use this clause, the value that you specify represents the number of pages in each segment. The value must be a multiple of 4 (from 4 to 64). The choice of the value depends on the size of the tables that you store. The following table summarizes the recommendations for SEGSIZE.

Table 1. Recommendations for SEGSIZE
Number of pages SEGSIZE recommendation
≤ 28 4 to 28
> 28 < 128 pages 32
≥ 128 pages 64

Another clause of the CREATE TABLESPACE statement is LOCKSIZE TABLE. This clause is valid only for tables that are in segmented table spaces. DB2, therefore, can acquire locks that lock a single table, rather than the entire table space.

If you want to leave pages of free space in a segmented (non-UTS) table space, you must have at least one free page in each segment. Specify the FREEPAGE clause with a value that is less than the SEGSIZE value.

Example: If you use FREEPAGE 30 with SEGSIZE 20, DB2 interprets the value of FREEPAGE as 19, and you get one free page in each segment.
Restriction: If you are creating a segmented table (non-UTS) space for use by declared temporary tables, you cannot specify the FREEPAGE or LOCKSIZE clause.

Characteristics of segmented (non-UTS) table spaces

Segmented table spaces share the following characteristics:

  • When DB2 scans all the rows for one table, only the segments that are assigned to that table need to be scanned. DB2 does not need to scan the entire table space. Pages of empty segments do not need to be fetched.
  • When DB2 locks a table, the lock does not interfere with access to segments of other tables.
  • When DB2 drops a table, its segments become available for reuse immediately after the drop is committed without waiting for an intervening REORG utility job.
  • When all rows of a table are deleted, all segments except the first segment become available for reuse immediately after the delete is committed. No intervening REORG utility job is necessary.
  • A mass delete, which is the deletion of all rows of a table, operates much more quickly and produces much less log information.
  • If the table space contains only one table, segmenting it means that the COPY utility does not copy pages that are empty. The pages might be empty as a result of a dropped table or a mass delete.
  • Some DB2 utilities, such as LOAD with the REPLACE option, RECOVER, and COPY, operate on only a table space or a partition, not on individual segments. Therefore, for a segmented table space, you must run these utilities on the entire table space. For a large table space, you might notice availability problems.
  • Maintaining the space map creates some additional overhead.

Creating fewer table spaces by storing several tables in one table space can help you avoid reaching the maximum number of concurrently open data sets. Each table space requires at least one data set. A maximum number of concurrently open data sets is determined during installation. Using fewer table spaces reduces the time that is spent allocating and deallocating data sets.