Improve the flexibility of changing PARTITION key

When to change PARITITION keys in multiple PARTITIOIN, a validation routine to check the data consistency runs every time ALTER command (ALTER TABLE ALTER PARTITION) is executed. In most cases we change the key value for multiple PARTITION keys and it’s quite rare changing it only for one PARTITION key. Because of this, we’ve faced difficulties to execute ALTER command since it requires a lot of work to investigate the exact execution sequence in advance.

To improve the situation, it’s preferred for us that the validation routine doesn’t run soon after executing ALTER, but runs at predetermined timing, or within REORG function. Though similar issue [DB24ZOS-I-888] has been solved already, we assume it would not be a fundamental solution since it takes a way of changing to PBG.

One of the solutions to avoid the issue, we assume, is to create Temporary table which LIMITKEY is completely in order, and then create Target table. It makes us a bit easier to find the exact execution sequence of ALTER command, though, we still need to take time and effort to create Temporary table. It takes our time to execute ALTER command because of this validation logic, or we need to create special tool to proceed the operation especially in the environment PARTITION key changes required frequently. This validation logic requires us an additional time and cost.

[current]

+-----+---------+-----------------------+

| PART | LIMITKEY | ALTER execution sequence |

+-----+---------+-----------------------+

| 1 | 100 | - |

+-----+---------+-----------------------+

| 2 | 200 | - |

+-----+---------+-----------------------+

| 3 | 300 | - |

+-----+---------+-----------------------+

[Temporary]

+-----+---------+-----------------------+

| PART | LIMITKEY | ALTER execution sequence |

+-----+---------+-----------------------+

| 1 | 000 | 1 |

+-----+---------+-----------------------+

| 2 | 100 | 2 |

+-----+---------+-----------------------+

| 3 | 200 | 3 |

+-----+---------+-----------------------+

[Target]

+-----+---------+-----------------+

| PART | LIMITKEY | ALTER Procedure |

+-----+---------+-----------------+

| 1 | 201 | 3 |

+-----+---------+-----------------+

| 2 | 300 | 2 |

+-----+---------+-----------------+

| 3 | 350 | 1 |

+-----+---------+-----------------+