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.
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
About this task
You can create a partitioned table by using the CREATE
TABLE statement.
Procedure
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))