Start of change

Changing partitioning schemes for tables with range-based partitions

FL 507 You can change the partitioning scheme for a table that uses range-based (PBR) partitions by first converting the table from PBR to PBG and then stacking a conversion from PBG to PBR with the desired partitioning scheme. A single REORG utility operation can materialize the stacked changes.

Before you begin

The application that issues that ALTER TABLE statement to change the table partitioning scheme from PBR to PBG partitions must run at application compatibility level V13R1M507 or higher.

About this task

The stacked conversions can accomplish the following changes to the range-based partitioning scheme.

Alter partitioning key columns
  • Add or insert columns to the partitioning key in any position.
  • Drop columns from the partitioning key in any position.
  • Reorder columns in the partitioning key.
  • Change the descending, ascending, or random attributes of columns in the partitioning key.
Obtain new range-base partitions
  • Drop empty or non-empty partitions at any position.
  • Add new partitions at any position.
Alter limit key values of multiple partitions with a PBG to PBR RPN conversion
With PBG to PBR RPN conversion , the limit key values for all partitions can be specified in a single ALTER statement.
Remove legacy 40-byte truncated limit key values
Convert legacy limit key values that were truncated at 40 bytes to non-truncated limit key values.

Procedure

To convert a the partitioning scheme of a table with range-based (PBR) partitions, complete the following steps:

  1. Plan for the new range-partitioning scheme of the table and desired attributes of the resultant PBR table space.
  2. Plan for necessary schema alterations that can be stacked with the conversions for the desired attributes for the resultant PBR table.
  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 an ALTER table statement to convert the table to use PBG partitions.
    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;
    For more information about this step, see Converting tables from range-based to growth-based partitions.
  5. Issue an ALTER table statement to convert table to use the desired range-based (PBR) partitions.
    For example, the following statement converts existing table TB01 to use range-based partitions and converts the containing table space to a PBR table space with relative page numbers.
    ALTER TABLE TB01 ALTER PARTITIONING TO 
  PARTITION BY RANGE (ACCT_NUM)               
           (PARTITION 1 ENDING AT (199),        
           PARTITION 2 ENDING AT (299),        
           PARTITION 3 ENDING AT (399),        
           PARTITION 4 ENDING AT (MAXVALUE));
    For more information about this step, see Converting tables from growth-based to range-based partitions.
  6. Run the REORG utility with SHRLEVEL REFERENCE or CHANGE clause to materialize the stacked pending changes.
End of change