EXCLUDE PART KEY COLUMNS field (IX_TB_PART_CONV_EXCLUDE subsystem parameter)

Specifies whether to exclude trailing columns from the table-controlled partitioning keys when table spaces are converted from index-controlled partitioning to table-controlled partitioning.

Deprecated subsystem parameter: Non-default settings for IX_TB_PART_CONV_EXCLUDE are deprecated. Db2 12 supports these settings, but their use is discouraged, and support is removed in Db2 13 or later. For best results, use the default value YES.
Acceptable values: YES, NO
Default: YES
Update: option 16 on panel DSNTIPB
DSNZPxxx: DSN6SPRM IX_TB_PART_CONV_EXCLUDE
YES
When converting from index-controlled partitioning to table-controlled partitioning, Db2 defines the new partitioning key to use only significant columns of the original partitioning index by default. If all partitions contain 'FF'x for a column in the internal limit key value that is stored in the table's SYSINDEXPART.LIMITKEY entry, Db2 excludes that column. Such columns were specified with a value of 'FF'x, MAXVALUE, MINVALUE, or no value when the index-controlled partitioning index was defined. However, if a new limit key value is specified for a column in an ALTER TABLE statement that is issued in one of the following cases, the column is used to define the new partitioning key regardless of whether the column was originally specified with a value of 'FF'x, MAXVALUE, or MINVALUE:
  • To add a new partition
  • To change a partition boundary
  • To rotate a partition to last on an index-controlled partitioned table space
NO
When converting from index-controlled partitioning to table-controlled partitioning, Db2 uses all columns of the original partitioning index to define the new partitioning key. This definition includes the trailing columns of the original partitioning index that do not affect the partitioning.

This parameter does not affect the automatic conversion to table-controlled partitioning when a CREATE INDEX statement is issued with an ENDING AT clause and without a CLUSTER clause. In this case, the Db2 subsystem uses the partitioning key that is specified by the CREATE INDEX statement.

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).