Converting partitioned (non-UTS) table spaces to partition-by-range universal table spaces

You can convert existing partitioned (non-UTS) table spaces, which are deprecated, to partition-by-range table spaces.

Before you begin

Tip: Unlike non-UTS table spaces, Db2 supports access to currently committed data in UTS. Applications that use a sequence of FETCH, DELETE, and INSERT statements in the same commit scope instead of an UPDATE statement might need to be modified to use UPDATE statements before the conversion to UTS. The reason is that a row that has been logically updated using DELETE and INSERT can re-appear in the FETCH result set before the application commits.

For more information about currently committed data access, see Accessing currently committed data to avoid lock contention.

About this task

Partition-by-range table spaces are preferred over deprecated partitioned (non-UTS) tables spaces because the segmented structure of partition-by-range table spaces improves space management and faster performance for mass-deletes. Also, some capabilities introduced in DB2® 9 and later releases are supported for partition-by-range table spaces but not for partitioned (non-UTS) tables spaces, such as:

• FL 502 Defining or altering key labels for z/OS® DFSMS data set encryption
• Relative page numbering
• Inserting partitions
• Dropping columns
• Altering the COMPRESS attribute of a table space
• Altering the DSSIZE value
• Altering the MEMBER CLUSTER attribute of a table space
• Altering the buffer pool page size for a table space
• Inline LOBs
• Clone tables
• Altering the compression attribute of an index as a pending definition change
• Altering the buffer pool page size of an index as a pending definition change

Learn more: For comprehensive background, how-to information, and examples for various paths for converting your deprecated classic partitioned (non-UTS) table spaces to partition-by-range table spaces, see the white paper Conversion from index-controlled partitioning to Universal Table Space (UTS).

To convert a partitioned (non-UTS) table space to a partition-by-range table space, complete the following steps:

1. If table space uses index-controlled partitioning, convert it to use table-controlled partitioning, as described in Converting table spaces to use table-controlled partitioning.
2. Convert the table space to a partition-by-range (UTS))space by issuing an ALTER TABLESPACE statement that specifies segment size for the table space.
   For example, the following statement converts the ts-name table space to a partition-by-range UTS with a segment size of 16 pages per segment.

    ALTER TABLESPACE ts-name SEGSIZE 16;

   The ALTER TABLESPACE statement is recorded as a pending data definition change.
3. Materialize the pending data definition change by running the REORG utility for the entire table space.
   When the REORG completes, the SYSTABLESPACE catalog table contains TYPE='R', which indicates a PBR UTS.