Up through DB2 V8 for z/OS, the only way to control
partitioning of DB2 table spaces was by using a clustering index that specified
the range of key values for each partition. With V8, though, DB2 adds the ability
to specify the partitioning criteria in the CREATE TABLE specification. This is
known as table-controlled partitioning and it is the preferred method for
creating (non-Universal) partitioned table spaces. With table-controlled partitioning
you can cluster on a different column (or set of columns) than you are
partitioning on. Furthermore, you can make changes such as dropping a
partitioning index or creating a table in a partitioned table space without
defining any indexes at all.
But given the long history of DB2, many existing partitioned
table spaces are index-controlled.
Fortunately, there is a quick-and-dirty technique that you can use to easily convert from index-controlled to table-based partitioning. Simply follow these steps:
- Identify the index-controlled partitioned table
space you wish to convert
- Convert the clustering index on the table to NOT
CLUSTER using ALTER INDEX. (Alternately, you could drop the clustering index,
but I wouldn’t recommend that unless you no longer need that index at all.)
- Convert the index back to CLUSTER, again using
ALTER INDEX
Voila! DB2 will have converted your table space to
table-controlled partitioning.
Note: DB2 will also convert from index-controlled to
table-controlled partitioning if you use ALTER TABLE to add a new partition,
change a partition boundary, or rotate a partition to last on an
index-controlled partitioned table space. But these are more intrusive methods
than simply altering the index from clustering to non-clustering and back again.
