Workaround for ALTER operations that can no longer be done together in Version 11
Two ALTER operations that you used to be able to do together in Version 10 can no longer be done together in Version 11, because one of the ALTER operations is now a pending definition change.
This technote describes how you can logically implement one instance of such a procedure in Version 11.
Suppose that you need to add an extra partition (partition 6) to a partitioned table space but keep the same limit key for the last partition of the table space. So what used to be the limit key for partition 5 is now the limit key for partition 6 and partition 5 now has a lower limit key. Basically, the partition 5 data is now spread across two partitions, but the upper limit for the table space is still the same.
In Version 10, you added this partition by doing two ALTER operations. The first ALTER operation lowered the limit key for partition 5 and the second ALTER operation added partition 6. Then you ran REORG, which distributes the data appropriately across the two partitions.
However, in Version 11, this procedure doesn't work, because the first ALTER operation (changing the limit key) is now a pending definition change. A pending definition change cannot be done at the same time as an immediate definition change. (The second ALTER operation is still an immediate definition change.) See the release incompatibility "Altering limit keys blocks immediate definition changes" in Application and SQL release incompatibilities.
In this case, Version 11 requires the following procedure:
1. Do the pending definition change (lower the limit key for partition).
2. Materialize that change by running REORG.
3. Do the immediate definition change (add partition 6).
The problem is that step #2 would discard a bunch of rows before the extra partition to hold them was added. How can you logically implement this procedure in Version 11?
Resolving the problem
Use one of the following procedures:
- If possible, add a partition (partition 6) with a higher limit key. (Adding a partition with a higher limit key is an immediate alter.) Then lower the limit keys for both partition 6 and partition 5. Both partitions are then placed in advisory REORG-pending status. Then, run REORG on both partitions. This solution will not work if the last partition already has a limit key set to MAXVALUE.
- If the last partition has a limit key set to MAXVALUE, then the data needs to be unloaded and saved. The table keys can be altered. The subsequent REORG will then discard rows that are higher than the limit key. Once the activity is complete and the new partition has been added, then LOAD REPLACE can be run using the saved data.
- If the problem was that the last partition is filling up, use REORG REBALANCE to redistribute the data across the existing partitions. See Redistributing data across partitions by using REORG