You are currently on IBM Systems Media’s archival website. Click here to view our new website.

Bookmark and Share

Recent Posts

DB2 12 Table Space with Relative Page Numbers

January 24, 2017

In my career as a DBA, I’ve done a lot work working around table space size limits. Managing this involves resizing, rebalancing, adding partitions, and much more. With this perspective, I can assure you that the dramatically increased partition sizes allowed in DB2 12 are a big deal.

Prior to DB2 12 partition size was limited to 256 GB. The maximum limit on the number of partitions ranged from 4,096 to 64, which is based on page and partition size (DSSIZE value). These limitations existed because data pages were sequentially formatted based on the table space, not based on each data partition.

However, DB2 12 introduces Partition-By-Range Relative-Page-Number (PBR RPN), which provides many structural differences compared to the previous format. For starters, absolute page numbers have been replaced. DB2 12 features relative page numbers (RPN), which represent page numbers without the need for space-consuming embedded partition numbers. With these PBR changes, the maximum partition size is now 1 TB, and the max table size soars from 16 TB (4K page) to 4 PB, and is designed to go even higher.

With DB2 12 the PBR table space can be in either the new RPN format or in absolute (ABS) format. The format selected is determined by either the PAGENUM value used on the CREATE or ALTER TABLE SPACE statement, or by the PAGESET_PAGENUM system parameter. The latter is used as the default when PAGENUM isn't specified on the CREATE or ALTER tablespace statement. To continue using the old format, use PAGENUM ABSOLUTE; otherwise use PAGENUM RELATIVE for RPN.

To convert existing PBRs from absolute to relative,  issue an ALTER TABLESPACE dbname.tsname PAGENUM RELATIVE statement. This conversion is done at the table space level rather than the partition level.

Admittedly, this conversion could be a pain point, since if you couldn't ALTER the DSSIZE and REORG a table space before because of the size of all the partitions, you'd still have the same issue. You'll also need to ask your storage administrator to provide enough disk storage to allow for this one-time conversion. But once completed, you'll be able to perform alters and then REORG at the partition level and benefit from the greater availability and scalability of PBR RPN.

Posted January 24, 2017| Permalink