DB2 Version 9.7 for Linux, UNIX, and Windows

Creating partitioned tables

Partitioned tables use a data organization scheme in which table data is divided across multiple storage objects, called data partitions or ranges, according to values in one or more table partitioning key columns of the table. Data from a given table is partitioned into multiple storage objects based on the specifications provided in the PARTITION BY clause of the CREATE TABLE statement. These storage objects can be in different table spaces, in the same table space, or a combination of both.

About this task

You can create a partitioned table by using the CREATE TABLE statement.

To create a partitioned table from the command line, issue the CREATE TABLE statement:

CREATE TABLE <NAME> (<column_name>  <data_type>  <null_attribute>) IN
	<table space list> PARTITION BY RANGE (<column expression>) 
	STARTING FROM <constant> ENDING <constant> EVERY <constant>

For example, the following statement creates a table where rows with a ≥ 1 and a ≤ 20 are in PART0 (the first data partition), rows with 21 ≤ a ≤ 40 are in PART1 (the second data partition), up to 81 ≤ a ≤ 100 are in PART4 (the last data partition).

CREATE TABLE foo(a INT) 
	PARTITION BY RANGE (a) (STARTING FROM (1) 
	ENDING AT (100) EVERY (20))

Before you begin

To create a table, the privileges held by the authorization ID of the statement must include at least one of the following authorities or privileges:

CREATETAB authority on the database and USE privilege on all the table spaces used by the table, as well as one of:
- IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the table does not exist
- CREATEIN privilege on the schema, if the schema name of the table refers to an existing schema
DBADM authority