Partition-by-range table spaces

A partition-by-range (PBR) table space is a universal table space (UTS) that has partitions based on ranges of data values. It holds data pages for a single table and has segmented space management capabilities within each partition.

Tip: Start of changePartition-by-range (PBR) table spaces with relative page numbering (RPN) are the suggested alternative for partitioned (non-UTS) table spaces, which are deprecated.End of change

In a PBR table space, the partitions are based on the boundary values that are defined for specific data columns. For example, the following figure illustrates the data pages for a table with two partitions.

Figure 1. Pages in a partition-by-range table space
Begin figure description. This figure shows a partitioned table space, where each partition contains one part of a table. End figure description.
Tip: Start of changeTo use a PBR table space for a table without a naturally suitable partitioning scheme, consider creating the table with an implicitly hidden ROWID column in the partitioning key. Any ROWID column in the partitioning key guarantees a very even distribution of data across the partitions, and an implicitly-hidden ROWID column can also be transparent to applications.End of change

Utilities and SQL statements can run concurrently on each partition. For example, a utility job can work on part of the data while allowing other applications to concurrently access data on other partitions. In that way, several concurrent utility jobs can, for example, load all partitions of a table space concurrently. Because you can work on part of your data, some of your operations on the data might require less time. Also, you can use separate jobs for mass update, delete, or insert operations instead of using one large job; each smaller job can work on a different partition. Separating the large job into several smaller jobs that run concurrently can reduce the elapsed time for the whole task.

You can create an index of any type on a table in a PBR space.

Relative and absolute page numbering

Start of changePBR table spaces can use relative page numbering (RPN) or absolute page numbering. PBR table spaces with relative page numbering (RPN) support larger partition sizes than PBR table spaces with absolute page numbering, and greater flexibility for growing the partitions. Instead of restricting partition growth to gigabytes in powers of two, PBR table spaces with relative page numbering support the growth of partitions by any number of gigabytes. DSSIZE can also be increased for individual partitions as an immediate ALTER, without requiring a REORG.End of change

Tip: For best results, use relative page numbering (RPN) for PBR table spaces. RPN allows for larger partitions and more flexibility for partition growth. Start of changeAbsolute page numbering is deprecated for PBR table spaces.End of change

Start of changeThe PAGENUM option of a CREATE TABLE or CREATE TABLESPACE statement specifies the type of page numbering that Db2 uses for a table space. If you omit the PAGENUM clause, Db2 uses the value specified for the PAGESET_PAGENUM subsystem parameter. See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).End of change

How to create PBR table spaces

Start of changeYou can explicitly create PBR table spaces by issuing CREATE TABLESPACE statements, or Db2 can create them for you, when you issue CREATE TABLE statements. For instructions, see Creating partition-by-range table spaces.End of change