By Frances Villafuerte with Long Tu, Regina Liu, and Paul McWilliams.
Db2 13 introduces the capability to convert tables in partition-by-growth (PBG) tables spaces to partition-by-range (PBR) with a pending definition change, and little to no impact on your applications. This new capability is especially useful if you have PBG table spaces that have grown too large, which can result in performance problems and management difficulties.
Starting in Db2 13 at function level 500 or higher, you can complete this conversion by issuing an ALTER TABLE statement with the ALTER PARTITIONING TO PARTITION BY RANGE clause. Then you use the REORG TABLESPACE utility to materialize the pending definition change.
In Db2 12 and earlier, converting PBG table spaces to PBR requires a long list of activities to plan for:
- Drop and re-create the table, table space, and other objects.
- Unload the data from the original table and load the new table.
- Modify applications for the authority and security changes needed for the new objects.
- Plan for an outage period while the objects are being dropped and re-created and the data is being moved.
In Db2 13, you do still need to plan for the REORG to materialize the changes, but the new conversion capability maximizes the availability of the altered objects by eliminating the need to unload the data, drop and re-create the objects (table, indexes, and table spaces), regenerate the security authorizations, re-create views, and so forth. Note that all indexes created on the table remain unchanged by this conversion.
When PBG table spaces are best
PBG table spaces were originally introduced to solve the 64 GB limitation of multi-table segmented (non-UTS) table spaces, and most customers convert to PBG when moving away from the deprecated segmented table spaces.
PBG table spaces also remain a good option for flexible scalability because Db2 automatically adds new partitions to PBG table spaces as the data in the table grows. However, that flexibility has limits, and PBG table spaces are most suitable for small to medium table spaces.
When to consider converting from PBG to PBR
PBR tables spaces with relative page numbers (RPN) can continue to provide high flexibility and scalability for future growth. For example, you might want to consider using this conversion if you encounter the following situations for PBG table space that has grown too large:
- Insert and query performance degradation: when performance degradation occurs for large tables in PBG tables spaces, the size of the table space is often a major cause. Db2 13 also introduces insert enhancements that can help with some large PBG table spaces—especially when most partitions are full—but not all.
- Difficulty completing REORG to maintain data clustering: entire table space REORGs can be essential for applications that depend on the clustering sequence of the data being in good order. However, when a PBG table space grows too large and contains a massive number of partitions, it is often difficult to complete the REORG in the requested available time frame.
- Problems associated with very large non-partitioned indexes (NPI): performance can suffer for index access when it must traverse the deep and wide NPI index trees that can result with a large number of partitions.
- Lack of parallelism features support for utilities: internal utility parallelism capabilities are not fully supported for PBG table spaces.
- Limited support for partition-level utility operations: The partition-level utility operations available for tables in PBR spaces with relative page numbering can greatly simplify object maintenance.
Partition limit keys are essential
PBG table spaces are often used for tables that do not have a suitable partitioning key. However, the partitions of a PBR table space are defined based on the limit key, so defining a suitable partition key for the converted PBR RPN table space is essential. If the current table does not have an existing column or columns suitable to be used for partitioning key ranges for the conversion, you might need to reconsider the design of your table, such as adding new columns to the table or altering existing columns for this purpose. The main considerations for determining a suitable limit key are:
- The number of partitions to be created. The converted PBR table spaces must have as many or more partitions than the original PBG table space.
- The data set size of each partition. The maximum size is inherited from the original PBG partition, so you must ensure that the data ranges can fit in each partition.
- The distribution of data across the partitions.
The converted PBR table space can have more than the original number of partitions in the starting PBG table space. So, you can use the conversion opportunity to evaluate the volume of the data and plan the limit key ranges accordingly to achieve high availability of utility operations with manageable partition sizes.
If you do add new columns, it is best to use REORG to materialize the values of the new column and complete any required application changes before completing this conversion.
In summary, in Db2 13 you can now convert your PBG table spaces that have grown unmanageably large, by using an efficient process that reduces the cost and time to adopt the changes in today’s high demand business model!
For more about this new capability, see: