Segmented (non-UTS) table spaces (deprecated)

Segmented non-UTS table spaces can store data for more than one table, especially for relatively small tables. The pages hold segments, and each segment holds records from only one table. Start of changeNon-UTS table spaces for base tables are deprecated and likely to be unsupported in the future.End of change

Deprecated function: Start of changeFL 504 Non-UTS table spaces for base tables are deprecated. CREATE TABLESPACE statements that run at application compatibility level V12R1M504 or higher always create a partition-by-growth or partition-by-range table space, and CREATE TABLE statements that specify a non-UTS table space (including existing multi-table segmented table spaces) return an error. However, you can use a lower application compatibility level to create table spaces of the deprecated types if needed, such as for recovery situations. For instructions, see Creating non-UTS table spaces (deprecated).End of change
Tip: For best results, convert any segmented (non-UTS) table spaces to a partition-by-growth or partition-by-range universal table space (UTS) as soon as possible. For more information, see Converting deprecated table spaces to the UTS types.

Segmented (non-UTS) table spaces hold a maximum of 64 GB of data and can contain one or more VSAM data sets.

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.

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.