DB2 Version 9.7 for Linux, UNIX, and Windows

Table partitioning keys

A table partitioning key is an ordered set of one or more columns in a table. The values in the table partitioning key columns are used to determine in which data partition each table row belongs.

To define the table partitioning key on a table use the CREATE TABLE statement with the PARTITION BY clause.

Choosing an effective table partitioning key column is essential to taking full advantage of the benefits of table partitioning. The following guidelines can help you to choose the most effective table partitioning key columns for your partitioned table.

Define range granularity to match data roll-out. It is most common to use week, month, or quarter.
Define ranges to match the data roll-in size. It is most common to partition data on a date or time column.
Partition on a column that provides advantages in partition elimination.

Supported data types

Table 1 shows the data types (including synonyms) that are supported for use as a table partitioning key column:

Table 1. Supported data types
Data type column 1	Data type column 2
SMALLINT	INTEGER
INT	BIGINT
FLOAT	REAL
DOUBLE	DECIMAL
DEC	DECFLOAT
NUMERIC	NUM
CHARACTER	CHAR
VARCHAR	DATE
TIME	GRAPHIC
VARGRAPHIC	CHARACTER VARYING
TIMESTAMP	CHAR VARYING
CHARACTER FOR BIT DATA	CHAR FOR BIT DATA
VARCHAR FOR BIT DATA	CHARACTER VARYING FOR BIT DATA
CHAR VARYING FOR BIT DATA	User defined types (distinct)

Unsupported data types

The following data types can occur in a partitioned table, but are not supported for use as a table partitioning key column:

User defined types (structured)
LONG VARCHAR
LONG VARCHAR FOR BIT DATA
BLOB
BINARY LARGE OBJECT
CLOB
CHARACTER LARGE OBJECT
DBCLOB
LONG VARGRAPHIC
REF
Varying length string for C
Varying length string for Pascal
XML

If you choose to automatically generate data partitions using the EVERY clause of the CREATE TABLE statement, only one column can be used as the table partitioning key. If you choose to manually generate data partitions by specifying each range in the PARTITION BY clause of the CREATE TABLE statement, multiple columns can be used as the table partitioning key, as shown in the following example:

CREATE TABLE sales (year INT, month INT)
   PARTITION BY RANGE(year, month) 
   (STARTING FROM (2001, 1) ENDING (2001,3) IN tbsp1,
   ENDING (2001,6) IN tbsp2, ENDING (2001,9) 
   IN tbsp3, ENDING (2001,12) IN tbsp4,
   ENDING (2002,3) IN tbsp5, ENDING (2002,6) 
   IN tbsp6, ENDING (2002,9) IN tbsp7,
   ENDING (2002,12) IN tbsp8)

This results in eight data partitions, one for each quarter in year 2001 and 2002.

Note:

When multiple columns are used as the table partitioning key, they are treated as a composite key (which are similar to composite keys in an index), in the sense that trailing columns are dependent on the leading columns. Each starting or ending value (all of the columns, together) must be specified in 512 characters or less. This limit corresponds to the size of the LOWVALUE and HIGHVALUE columns of the SYSCAT.DATAPARTITIONS catalog view. A starting or ending value specified with more than 512 characters will result in error SQL0636N, reason code 9.
Table partitioning is multicolumn not multidimension. In table partitioning, all columns used are part of a single dimension.

Generated columns

Generated columns can be used as table partitioning keys. This example creates a table with twelve data partitions, one for each month. All rows for January of any year will be placed in the first data partition, rows for February in the second, and so on.

Example 1

CREATE TABLE monthly_sales (sales_date date,
	sales_month int GENERATED ALWAYS AS (month(sales_date)))
    	PARTITION BY RANGE (sales_month)
    	(STARTING FROM 1 ENDING AT 12 EVERY 1);

Note:

You cannot alter or drop the expression of a generated column that is used in the table partitioning key. Adding a generated column expression on a column that is used in the table partitioning key is not permitted. Attempting to add, drop or alter a generated column expression for a column used in the table partitioning key results in error (SQL0270N rc=52).
Data partition elimination will not be used for range predicates if the generated column is not monotonic, or the optimizer can not detect that it is monotonic. In the presence of non-monotonic expressions, data partition elimination can only take place for equality or IN predicates. For a detailed discussion and examples of monotonicity see Considerations when creating MDC tables.