Converting tables from range-based to growth-based partitions

FL 507 If the partitioning scheme of a table in a partition-by-range (PBR) table space is no longer suitable for your applications, you can convert it to use a partition-by-growth (PBG) partitions without incurring lengthy application outages.

To convert the partitioning scheme of a table with PBR partitions to PBG partitions, complete the following steps:

1. Consider the data set size and maximum number of partitions.
   The data set size and maximum number of partitions (MAXPARTITIONS) for the converted PBG table can be specified on the ALTER TABLE statement. Consider and specify the value that satisfies the requirements for the converted PBG table. If the table has DATA CAPTURE CHANGES, the maximum number of partitions must not be less than the number of partitions for the original PBR table.
2. Plan for any other necessary schema alterations.

   Many pending options are allowed to be stacked with the PBR to PBG conversion. Consider issuing necessary schema changes, stacking those with PBR to PBG conversion, and then materializing all the pending changes in a single online REORG.

   However, the PBR to PBG conversion is not allowed if pending changes already exist for certain options and certain immediate changes are restricted until the PBR to PBG conversion is materialized. For more information, see Restrictions for pending data definition changes.

3. Resolve any restricted, incompatible exception states for the table space and indexes.
   The ALTER TABLE statement might return SQLCODE -650 RC54 if there are restricted states: LPL, WEPR, or GRECP.
   For example, you can issue a DISPLAY DATABASE command with the RESTRICT keyword:

   DISPLAY DATABASE(database-name) SPACENAM(*) RESTRICT(GRECP,LPL,WEPR)

4. Issue the ALTER TABLE statement with the ALTER PARITIONING clause to convert the table partitions from PBR to PBG.
   For example, the following statement converts existing table TB01 to use growth-based partitions and converts the containing table space to a PBG table space.

   ALTER TABLE TB01 ALTER PARTITIONING TO PARTITION BY GROWTH;

5. Run the REORG utility with SHRLEVEL REFERENCE or CHANGE clause to materialize the pending change and any other stacked pending changes.