Partitioning data in Db2 tables

All Db2 base tables that are created in universal table spaces use either partition-by growth or partition-by-range data partitioning.

Before you begin

Consider whether you want to create the table space and database for your table or let Db2 create them for you implicitly.

For more information, see Implementing Db2 table spaces.

About this task

Data partitions are useful because they support partition-level utility operations and parallelism capabilities for improved performance.

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 let Db2 manage size-based table partitions based on data growth, or you can specify partitions based on ranges of data values.

Size-based data partitions

Size-based partitions are best when the data in a table is expected to exceed 64 GB, or when a table does not have a suitable partitioning key. Partition-by-growth table spaces can grow up to 128 TB, depending on the buffer pool page size used, and the MAXPARTITIONS and DSSIZE values specified when the table space is created.

If you use size-based partitions, the table resides in partition-by-growth (PBG) table space. For more information, see Creating partition-by-growth table spaces.

Range-based data partitions

If you use partitions based on ranges of data values, the table resides in a partition-by-range (PBR) table space. For more information, see Creating partition-by-range table spaces.

If you do not specify how to partition the data when you create a table, Db2 uses size-based partitions and implicitly creates a partition-by-growth table space by default.

Tip:

When converting or replacing existing tables in deprecated non-UTS table spaces, the type of partitioning to use depends on the existing table space type. For more information, see Converting deprecated table spaces to the UTS types.

Procedure

Begin general-use programming interface information. To control how the data in a table is partitioned, use the following approaches in the CREATE TABLE statement:

Specify the PARTITION BY SIZE clause to create a table with data partitioned based on data size.
If you specify the name of a table space in the IN clause, it must identify an existing PBG table space. If you omit the table space name Db2 implicitly creates a PBG table space for the table.
The following example creates a table with partitions based on data growth, which resides in an implicitly created partition-by-growth table space:
```
  CREATE TABLE TS02TB
       (C1 SMALLINT,
        C2 DECIMAL(9,2),
        C3 CHAR(4))
     PARTITION BY SIZE EVERY 4G
     IN DATABASE DSNDB04;
```
FL 504 If you omit the PARTITION BY clause, the table is also created with size based partitions, and if the IN clause specifies a table space name, it must identify an existing PBG table space.

Specify a PARTITION BY RANGE clause to identify one or more columns that define the partitioning key, and specify the limit key values in the PARTITION part-num ENDING AT clause.

If you specify the name of a table space in the IN clause, it must identify an existing PBR table space. If you omit the table space name, Db2 implicitly creates a PBR table space for the table.

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));

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;

What to do next

You might eventually need to add or modify the data partitions. For more information, see Adding partitions and Altering partitions.