Converting tables from growth-based to range-based partitions

You can convert the partitioning scheme of a table from partition-by-growth (PBG) to partition-by-range (PBR) with minimal impact to your applications.

About this task

FL 500

You can alter the partitioning scheme of a table with growth-based partitions (in a PBG table space) to use range-based partitions. The table space for the table is converted to PBR with relative page numbers (RPN). The conversion is usually a pending definition change that is materialized by a subsequent REORG.

If the PBR table space is defined with fewer partitions than the PBG table space, the excess partitions of the PBG table space are dropped, and information about the deleted partitions is deleted from catalog and directory tables.

The conversion process handles any existing non-partitioned indexes on the table. However, Db2 does not change any aspects or attributes of those indexes, so you might want to create partitioned indexes on the table after completing the conversion.

When to convert tables from PBG to PBR

PBG and PBR universal table spaces (UTS) are both strategic table spaces types for Db2 for z/OS tables. PBG table spaces are the default UTS type, and most conversions to UTS result in PBG table spaces.

Tip: PBG table spaces are best used for small to medium-sized tables. If you expect a table to grow much larger than the 64 GB, consider using a partition-by-range (PBR) table space instead.

When a table in a PBG table space grows too large, several drawbacks can begin to arise, including the following issues:

Insert and query performance degradation, which is perhaps the most important factor suggesting that conversion is required. Such performance degradation can have many causes, but for large tables in PBG tables spaces, the size of the table space is often one of the major causes.
Difficulty regaining clustering of the data (which requires a REORG of the entire table space).
Problems associated with very large non-partitioned indexes, because partitioned (partitioning and DPSI) indexes are not supported for tables in PBG table spaces. For more information, see
Lack of partition parallelism support for utilities.
Limited support for partition-level utility operations.

If you encounter these issues for tables in PBG table spaces, consider converting them to PBR.

Requirements for converting tables from PBG to PBR

A table can be converted from PBG to PBR if the following conditions are met:

The high limit key for the last partition requires MAXVALUE for ascending key columns or MINVALUE for descending key columns to avoid the data being discarded during the subsequent online REORG execution.
If the table to be converted is defined with DATA CAPTURE CHANGES, and the data sets of the table space are defined, the number of partitions for the range-partitioning scheme must not be less than the maximum number of partitions for the PBG table space. This requirement avoids a situation where IFCID 0306 is unable to decompress log records associated with deleted partitions because the compression dictionaries no longer exist.
The data sets for the table space and indexes must be associated with a DFSMS data class that is specified with extended format and extended addressability.
The table identified by table-name to be altered must:
- Be a table that exists at the current server
- Be in a complete state if the alteration is a pending change
- Not be a catalog or directory table
- Not be a created global temporary table
- Not have table OBID = 1
- Not contain an XML column
- Not be involved in a clone relationship
- Not be defined with ORGANIZE BY HASH
- Not be an accelerated table
- Not contain a LOB column
The containing table space of the altered table must follow these rules:
- Be a partition-by-growth table space
- Not have unmaterialized pending changes

Procedure

To convert an existing table in a PBG table space to use range-based partitions, complete the following steps:

Check if the related table space and indexes are in exception states.
For example, you might issue the following DISPLAY DATABASE command with the RESTRICT keyword.
```
-DISPLAY DATABASE(database-name) SPACENAM(*) RESTRICT(GRECP,LPL,WEPR)
```
Plan for any other required schema alterations.
After you complete the next step and until you materialize the change, certain pending changes and certain immediate changes are restricted if referencing the table space, the table it contains, or indexes on the table. Also, for certain schema definition changes that are pending for the table space, the table it contains, or the indexes on the table, the partitioning scheme alteration is restricted. For more information, see Restrictions for pending data definition changes.
Determine a suitable partitioning scheme to use for the table, including the columns that will define the partitions and the limit key values for each partition, and evaluate the following considerations:
- The number of partitions that will be created, especially if the table to be converted is defined with DATA CAPTURE CHANGES and subscribed to data replication.
- The distribution of data across the partitions.
- The data set size for each partition. The maximum size of the data set for the PBR partition is inherited from the original PBG partition, so you must choose limit keys for partitions to ensure the data ranges can fit in the corresponding partitions. You might need to consider increasing the data set size for the PBG partitions to accommodate the data being distributed differently into converted PBR partitions.
You can use the RUNSTATS utility to collect useful statistics for planning the range-partitioning scheme.
If the table to be converted is defined with DATA CAPTURE CHANGES and subscribed to data replication, consider taking one of following actions before starting the conversion:
1. Use an ALTER TABLE statement with DATA CAPTURE NONE clause, so that the number of PBR partitions is no longer enforced to be equal or greater than the maximum number of partitions for the PBG.
2. Run the REORG utility for the PBG table space and specify DROP_PART YES to drop empty partitions, and then use an ALTER TABLESPACE statement with MAXPARTITIONS clause to lower the maximum number of partitions.
Issue an ALTER TABLE statement that specifies an ALTER PARTITIONING TO PARTITION BY RANGE clause.
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));
```
This statement is a pending definition change if the data sets of the table space are already created.
If the alteration is a pending change, run the following utilities:
1. Run the REPORT RECOVERY utility on the table space and keep the report for future reference if you need to access data from image copies taken before the conversion is materialized.
  Although recovery to a point-in-time before the REORG that materializes the PBG to PBR conversion is prohibited, you can still run the UNLOAD utility on the old image copies of the table space or partitions created prior to REORG materialization for data mining or recovery purposes, and the LOAD utility can be used to reload the data.
2. Run the REORG utility with SHRLEVEL REFERENCE or CHANGE clause to materialize the pending change.
Optional: Consider creating partitioned indexes on the table to support parallelism processing advantages.