Creating partition-by-range table spaces

You can create a partition-by-range (PBR) table space to create partitions based on data value ranges and use segmented space management capabilities within each partition.

About this task

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. PBR table spaces can use absolute or relative page numbering. Start of change Absolute page number offers the most flexibility for maximum partition size. End of change

In a PBR table space, the partitions are based on the boundary values that are defined for specific data columns.

Tip: To 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.

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.

PBR table spaces can use relative page numbering (RPN) or absolute page numbering. PBR spaces with relative page numbering support larger partition sizes than PBR table spaces with absolute page numbering, and greater flexibility in growing your 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. The 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. The default for PAGESET_PAGENUM is ABSOLUTE. See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).

Tip: Partition-by-range (PBR) table spaces with relative page numbers (RPN) are the suggested alternative for partitioned (non-UTS) table spaces, which are deprecated.

Procedure

To create a partition-by-range table space, use one of the following approaches:

Issue a CREATE TABLE statement and specify the PARTITION BY RANGE clause.

The following example creates a table with partitions based on ranges of data values in the ACCTNUM column, which resides in an implicitly created PBR table space:

CREATE TABLE TB01 (                     
   ACCT_NUM         INTEGER,            
   CUST_LAST_NM     CHAR(15),           
   LAST_ACTIVITY_DT VARCHAR(25),        
   COL2             CHAR(10),           
   COL3             CHAR(25),           
   COL4             CHAR(25),           
   COL5             CHAR(25),           
   COL6             CHAR(55),           
   STATE            CHAR(55))           
 IN DBB.TS01                            
                                        
  PARTITION BY RANGE (ACCT_NUM)               
   (PARTITION 1 ENDING AT (199),        
    PARTITION 2 ENDING AT (299),        
    PARTITION 3 ENDING AT (399),        
    PARTITION 4 ENDING AT (MAXVALUE));

Issue a CREATE TABLESPACE statement that specifies the NUMPARTS clause and omits the MAXPARTITIONS clause.
The following example creates a partition-by-range table space, TS1, in database DSN8D12A using storage group DSN8G120. The table space has 16 pages per segment and has 55 partitions.
```
CREATE TABLESPACE TS1
     IN DSN8D12A
     USING STOGROUP DSN8G120
     NUMPARTS 55
     SEGSIZE 16
     LOCKSIZE ANY;   
```

To create a table without a naturally suitable partitioning scheme in a PBR table space, consider creating the table with an implicitly hidden ROWID column in the partitioning key.

The ROWID column in the partitioning key guarantees a very even distribution of data across the partitions. An implicitly-hidden ROWID column can also be transparent to applications.

For example, the following CREATE TABLE statement creates the TB02 table in a PBR table space with 16 partitions based on the implicitly-hidden ROWID column named ROW_ID.

CREATE TABLE TB02 ( 
   CLIENT VARGRAPHIC(3) NOT NULL,
   WI_ID VARGRAPHIC(12) NOT NULL,
   LENGTH SMALLINT, 
   DATA VARCHAR(1000),
   ROW_ID ROWID NOT NULL
   IMPLICITLY HIDDEN GENERATED ALWAYS)
                                       
  PARTITION BY (ROW_ID)
   (PARTITION 1 ENDING AT (X'0FFF'),
    PARTITION 2 ENDING AT (X'1FFF'),
    PARTITION 3 ENDING AT (X'2FFF'),
    PARTITION 4 ENDING AT (X'3FFF'),
    PARTITION 5 ENDING AT (X'4FFF'),
    PARTITION 6 ENDING AT (X'5FFF'),
    PARTITION 7 ENDING AT (X'6FFF'),
    PARTITION 8 ENDING AT (X'7FFF'),
    PARTITION 9 ENDING AT (X'8FFF'),
    PARTITION 10 ENDING AT (X'9FFF'),
    PARTITION 11 ENDING AT (X'AFFF'),
    PARTITION 12 ENDING AT (X'BFFF'),
    PARTITION 13 ENDING AT (X'CFFF'),
    PARTITION 14 ENDING AT (X'DFFF'),
    PARTITION 15 ENDING AT (X'EFFF'),
    PARTITION 16 ENDING AT (MAXVALUE))
 CCSID UNICODE;