Conversion of a table space to a table-controlled partitioned table space or a range-partitioned table space
Analysis performs different actions depending on the type of table space that you choose to convert to a table-controlled partitioned table space or a range-partitioned table space.
The following table describes these actions and lists the commands that Analysis generates in the worklist.
If you convert this type of table space | Specification performs the following action | and Analysis generates these commands in the worklist |
|---|---|---|
A partitioned table space with a partitioning index | Uses the Db2 catalog to populate the limit key and the partitioning key columns | ALTER TABLE ALTER PARTITION or ALTER INDEX NOT CLUSTER |
A nonpartitioned table space, or a partitioned table space that does not have a partitioning index | Does not populate the limit key and the partitioning key columns | DROP TABLESPACE CREATE TABLESPACE CREATE TABLE (using table-controlled partitioning) |
A partitioned table space that you have LIKEd | Might use an index that was created in the work ID to populate the limit key and the partitioning key columns | CREATE TABLESPACE CREATE TABLE (using table-controlled partitioning) CREATE INDEX CLUSTER |
Preserving the values of limit keys
IBM recommends that you convert a table in an index-controlled partitioned table space to table-controlled partitioning by altering the definition of the partitioning index from CLUSTER to NOT CLUSTER, and then back to CLUSTER. When you perform these actions, Db2 automatically sets the limit key values for the last partition to the MAXVALUE or MINVALUE .
When you choose to convert an index-controlled partitioned table space to a table-controlled partitioned or range-partitioned table space, ALTER and BMC AMI Change Manager for Db2 use one of the following methods to accomplish the task:
Alter the definition of a partitioning index from CLUSTER to NOT CLUSTER and then back to CLUSTER
This method is used if the table spaces are defined as LARGE and in which the DSSIZE of the table space is greater than zero (0), or if you choose not to preserve the values of the limit keys in the last partition of the table space.
Alter the value of the limit key for the last partition in the table space to the value that existed for the last partition
This method, in which you preserve the values of the limit keys in the last partition of the table space, prevents the limit key from being set to MAXVALUE (or high values). However, this method requires the reorganization of the last partition.
- If you preserve the values, ALTER and Change Manager require that a REORG be run against the last partition. (If a REORG is not run, Db2 leaves the table space partition in a REORG PENDING status.) If the data stored in the last partition is beyond the limit key value, Db2 discards rows when it reorganizes the partition. To verify whether the data is beyond the limit key value, issue a SELECT MAX statement on the first column of the partitioning key.
- If you do not preserve the values, Db2 automatically assigns the limit key values for the last partition to MAXVALUE or MINVALUE (or high or low values). All data that is currently stored in the last partition remains in the partition, even if the data was actually beyond the limit key value that was originally specified for the partition. In addition, ALTER and Change Manager do not allow you to rotate or add partitions. Allowing Db2 to set high values for your limit key has the following disadvantages:
- The original limit key value specified for that partition will be lost.
- If you add or rotate a partition to this table, you will have to change the limit key for the existing last partition. When you change a limit key, Db2 puts the table space partition in a REORG PENDING status.
Before you run Analysis, you can change whether to preserve the values of the limit keys on the Tablespace Parts List panel.
Rotating partitions
You can identify table space partitions by both their physical and logical partition numbers. It is important to know the partition numbers because Db2 modifies the logical partition numbers when it rotates partitions of a table space. The following table shows how the components of ALTER and Change Manager reference the partition numbers.
Referencing physical and logical partition numbers
Component/Object | Reference to partition numbers |
|---|---|
Specification | Uses the logical partition number. Specification displays the physical partition number for information only. |
Baseline | Saves the physical and logical partition numbers. |
Baseline Report | Uses the logical partition number. The baseline report includes the physical partition number as a comment. |
Worklist | Uses the physical partition number. |
DDL | Uses the physical partition number. |
CDL | Uses the logical partition number. |
ALTER and Change Manager enable you to rotate the first partitions in a table-controlled partitioned or a range-partitioned table space to the last partitions in the table space. You specify the number of partitions to rotate in the Tablespace Parts List panel. For each partition, Analysis builds an ALTER TABLE ROTATE PARTITION statement in the worklist. When you execute the worklist, Db2 deletes all of the data in the rotated partitions of the table. The products allow you to change the value of the limit keys so that they are valid for the last partitions.
Related topic