Start of change

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: Start of change 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.

End of change

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:

  • Start of changeFL 502 Defining or altering key labels for z/OS® DFSMS data set encryptionEnd of change
  • Start of changeRelative page numberingEnd of change
  • Start of changeInserting partitionsEnd of change
  • 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: Start of changeFor 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).End of change

Procedure

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.
End of change