This Db2 for z/OS News from the Lab blog entry was originally published on 2019-06-12. It is part of the Db2 12 greatest hits series, which highlights the most popular new features and capabilities introduced by the original Db2 12 for z/OS release, at general availability in October 2016.

By Haakon Roberts and Paul McWilliams.

In Db2 12, we introduced a new capability to use relative page numbers (RPN) for partition-by-range (PBR) universal table spaces (UTS). In this post, we'll call this new type of table space "PBR RPN" for short, and we'll explain what it is and the issues that it addresses.

If you look at any Db2 data page or index page, you can see that it contains a page number, which is either 3 or 4 bytes long, depending upon the page set or partition size. For partitioned table spaces such as PBR or partition-by-growth (PBG), the page number includes the partition number. This means that the more partitions there are, the more bits are required to represent the partition, and the fewer bits are available to represent the page number within each partition. The result is a complicated relationship between the maximum partition size and the number of partitions.

Now consider a range-partitioned table space, where rows are allocated to specific partitions based upon the partitioning key. If you run out of space in a partition, then an application outage occurs because a row destined for partition 5 cannot be inserted into any other partition. The solution is to either rebalance data across partitions by changing limit key values, or to increase the partition size, which is the DSSIZE. The problem with modifying the limit key value to re-partition the data is that many customers choose a partitioning scheme that logically makes sense, such as one partition per month, and application logic may be built around such schemes.

On the other hand, increasing the DSSIZE isn’t exactly straightforward for the following reasons:

• First, it may not even be possible to increase DSSIZE due to the relationship between maximum partition size and number of partitions;
• Second, although with PBR it’s a deferred alter followed by a materializing REORG, you cannot change the DSSIZE of a single partition. The attribute applies to all partitions and the entire table space needs to be reorganized. This presents a problem because range partitioned tables are typically very large and reorganizing the entire table space can be a challenge.

These issues were our motivation for introducing PBR RPN in Db2 12. With PBR RPN, the partition number is no longer part of the page number. Incidentally, this means that you cannot look at a page number in a page of a PBR RPN page set to see which partition it belongs to. The partition number is a 2 byte value stored in the header page of the page set. The page number within each page simply denotes the relative page number within each partition, hence the name relative page numbering.

Now that the page number no longer needs to accommodate the partition number, the partition can store more pages. As a result, the maximum partition size for a PBR RPN table space is now 1 terabyte, regardless of how many partitions are defined or the page size. This in turn means that the maximum size of a table in Db2 can now be 4096 partitions times 1 Tb, or 4 petabytes. Given a 4k page size and a maximum of 255 rows per page, this roughly translates to a staggering 280 trillion rows in a single table!

Perhaps of more immediate practical value is that the DSSIZE attribute is moved to the partition level. If you want to increase the size of partition 3, for example, you can do it by altering just part 3, and it has immediate effect. No other partitions are affected, and no REORG is required at all. This is a huge availability improvement and means that managing range-partitioned table spaces is far simpler than before. Note however that decreasing the DSSIZE would still affect all partitions and require a table space-level REORG to take effect. One reason is that it is possible to decrease the DSSIZE to such an extent that the data residing in a partition no longer fits within that partition, and therefore the alteration could not be made immediate.

Indexes on PBR RPN table spaces have the same attributes, so it is possible to alter the DSSIZE of a partitioned index as an immediate alter, again up to 1Tb in size.

How does one take advantage of this new feature in Db2 12? First of all, it is of course only available in function level 500 and higher. Second, there is a new option on CREATE TABLESPACE called PAGENUM where you can indicate that you want PAGENUM RELATIVE. The same option exists for ALTER TABLESPACE, so existing classic partitioned or PBR table spaces can be migrated to PBR RPN. It is a deferred ALTER followed by a materializing REORG. Because RPN is itself a table space attribute, a REORG of the entire table space is required for the migration. Incidentally, Db2 also supports conversion back to PAGENUM ABSOLUTE if necessary.

If you are familiar with the concept of a RID (record identifier) in Db2, then for RPN table spaces the RID has increased in size to 7 bytes (the 2 byte partition number, the 4 byte page number, and 1 byte idmap entry). The mapping table format for online REORG has also been changed to accommodate this. In Db2 12 function level 100, both Db2 11 and new Db2 12 format mapping tables are accepted by the REORG utility. In function level 500 and higher, only new format mapping tables are supported. If an old format mapping table is supplied, it is ignored and the utility automatically generates a new mapping table. It is dropped at the end according to the rules introduced in Db2 11.

Another consequence of the 7-byte RID is a log record format change in Db2 12. The change is universal; it applies to all page sets whether RPN or not, and it also applies in function level 100, even though RPN is not available until function level 500 or higher. This in turn means that any third-party product that processes Db2 log records, such as log analysis products, must support the new format from function 100 onwards. Since the partition number is now separate from the page number, it is now much easier to determine from a log record the partition that it is for, and this information is now formatted by the DSN1LOGP utility.

In summary, PBR RPN represents a significant improvement in simplicity, usability, manageability, scalability and availability for the largest tables within Db2 environments. Any customer on Db2 12 should consider migrating to RPN to take advantage of the benefits described above.