Universal Table Spaces were introduced in DB2 for z/OS V9 and comprise of two new types: Partitioned By Range and Partitioned By Growth. Universal Partitioned By Range table spaces are a strategic table space type which will, over time, replace partitioned tables that were created prior to V9, hereafter termed “Classic Partitioned” table spaces.
What are the advantages of Partitioned By Range table spaces over “Classic Partitioned” table spaces?
Partitioned By Range table spaces have a segmented structure which allows for better space management and faster performance on mass deletes. Since PBR table spaces are the strategic direction for range partitioning, starting with DB2 for z/OS V9 “Classic Partitioned” table spaces do not support all new functions included in V9, whereas Partitioned By Range table spaces do provide support for all new enhancements. Some of the features not supported by “Classic Partitioned” table spaces include:
- Inline LOBs
- Hash Tables
- ALTER SEGSIZE or DSSIZE or BUFFERPOOL(with different page size)
If you want to take advantage of the above benefits, you need to migrate from Classic Partitioned table spaces to Partitioned By Range table spaces.
Migrating Classic Partitioned table spaces to Partitioned By Range (PBR) table spaces
This technique for migration involves ALTER statements and a REORG TABLESPACE. Also, if the classic partitioned table uses index-controlled partitioning, then the table must first be converted to table-controlled partitioning. Care should be taken to not drop or recreate any objects during this process so that plans and packages are not invalidated, the same access paths can be used, and performance degradation is avoided.
Step 1: Check whether the table has Index-controlled partitioning or Table-controlled partitioning
SELECT columns LIMITKEY and LIMITKEY_INTERNAL from SYSTABLEPART.
LIMITKEY contains the external format values of the limit keys for each partition.
LIMITKEY_INTERNAL contains the internal DB2 format of the LIMITKEY values
If each LIMITKEY_INTERNAL row has a value, then partitioning is table controlled and steps 1A-1C can be skipped. If it does not contain a value, then index-controlled partitioning is used and the table needs to be converted to table-controlled partitioning.
Step 1A: Ensure that the old partitioning index is converted correctly
Make sure that zparm IX_TB_PART_CONV_EXCLUDE is set to YES.
Reason: DB2 will by default use all the columns in the old partitioning index as partitioning columns in the new PBR tablespace. When the “Classic” partitioning index has more columns than are needed to define the limit keys, this can cause performance problems. To avoid this performance degradation, set IX_TB_PART_CONV_EXCLUDE=YES. This ensures that if the index key used for partitioning is a subset of index columns, then only that same subset of columns will be used for table partitioning as well. This setting became the default via APAR PM90893, but check your zparm to ensure the correct setting.
Step 1B: If your table is index-controlled, then find the clustering index to be used in the conversion from index controlled partitioning to table controlled partitioning.
The internal format limit keys will be needed for the conversion to a PBR table space and they can be found in the partitioning index catalog entries (see SYSINDEXPART column LIMITKEY where SYSINDEXES column CLUSTERED='Y’). This clustering index will be used in step 1C.
Step 1C: Convert Index-controlled partitioning to Table controlled partitioning
There is no explicit command to ask DB2 to convert an index from index-controlled partitioning to table-controlled partitioning. However, this can be done implicitly by using function available in DB2 V8 and above. The method shown here is one that does not change the table at all.
We will use the first ALTER statement to change the partitioning index identified in step 1B. Then explicit clustering will be set on again with a second alter statement. Both statements can be issued in a single commit scope.
- ALTER INDEX indexname NOT CLUSTER;
A warning message with SQLCODE = 20272 will be issued indicating the change
- ALTER INDEX indexname CLUSTER
The explicit clustering is then set on again with this second alter statement
Step 2: Request a pending Partition By Range conversion in the next REORG
“Classic partitioned” table spaces do not have segments, therefore they have an implicit SEGSIZE = 0. Partition By Range table spaces do have segments and need a legitimate SEGSIZE (4, 8, 16…). The segment size change can be requested with an ALTER statement to the desired number of pages per segment. The alter request will be kept in catalog table SYSIBM.SYSPENDINGDDL to be applied during REORG. This is an example for table space DB1.TSP to be converted to a PBR that uses 16 pages per segment.
ALTER TABLESPACE DB1.TSP SEGSIZE 16;
Step 3: REORG to materialize the table space with a Partition By Range format.
REORG of a whole table space must be performed to process the request from SYSPENDINGDDL. Upon completion, the SYSTABLESPACE TYPE column will have a value of 'R' indicating a Partitioned By Range Table Space.