The
CREATE DATABASE PARTITION GROUP statement defines a new database partition
group within the database, assigns database partitions to the database
partition group, and records the database partition group definition
in the system catalog.
Invocation
This statement can be embedded
in an application program or issued interactively. It is an executable
statement that can be dynamically prepared only if DYNAMICRULES run
behavior is in effect for the package (SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include SYSCTRL or SYSADM authority.
Syntax
>>-CREATE DATABASE PARTITION GROUP--db-partition-group-name----->
.-ON ALL DBPARTITIONNUMS----------------------------------------------------------------.
>--+---------------------------------------------------------------------------------------+-><
| .-,--------------------------------------------------. |
| V | |
'-ON--+-DBPARTITIONNUMS-+--(----db-partition-number1--+--------------------------+-+--)-'
'-DBPARTITIONNUM--' '-TO--db-partition-number2-'
Description
- db-partition-group-name
- Names the database partition group. This is a one-part name. It
is an SQL identifier (either ordinary or delimited). The db-partition-group-name must
not identify a database partition group that already exists in the
catalog (SQLSTATE 42710). The db-partition-group-name must
not begin with the characters 'SYS' or 'IBM' (SQLSTATE 42939).
- ON ALL DBPARTITIONNUMS
- Specifies that the database partition group is defined over all database partitions defined to
the database (db2nodes.cfg file) at the time the database partition group is
created.
If
a database partition is added to the database system, the ALTER DATABASE PARTITION GROUP statement
should be issued to include this new database partition in a database partition group (including
IBMDEFAULTGROUP). Furthermore, the REDISTRIBUTE DATABASE PARTITION GROUP command must be issued to
move data to the database partition.
- ON DBPARTITIONNUMS
- Specifies the database partitions that are in the database partition
group. DBPARTITIONNUM is a synonym for DBPARTITIONNUMS.
- db-partition-number1
- Specify a database partition number. (A node-name of
the form NODEnnnnn can
be specified for compatibility with the previous version.)
- TO db-partition-number2
- Specify a range of database partition numbers. The value of db-partition-number2 must
be greater than or equal to the value of db-partition-number1 (SQLSTATE
428A9). All database partitions between and including the specified
database partition numbers are included in the database partition
group.
Rules
- Each database partition specified by number must be defined in
the db2nodes.cfg file (SQLSTATE 42729).
- Each db-partition-number listed
in the ON DBPARTITIONNUMS clause can appear only once (SQLSTATE 42728).
- A valid db-partition-number is between
0 and 999 inclusive (SQLSTATE 42729).
- The CREATE DATABASE PARTITION GROUP statement
might fail (SQLSTATE 55071) if an add database partition server request
is either pending or in progress. This statement might also fail (SQLSTATE
55077) if a new database partition server is added online to the instance
and not all applications are aware of the new database partition server.
Notes
- This statement creates a distribution map for the database partition
group. A distribution map identifier (PMAP_ID) is generated for
each distribution map. This information is recorded in the catalog
and can be retrieved from SYSCAT.DBPARTITIONGROUPS and SYSCAT.PARTITIONMAPS.
Each entry in the distribution map specifies the target database partition
on which all rows that are hashed reside. For a single-partition database
partition group, the corresponding distribution map has only one entry.
For a multiple partition database partition group, the corresponding
distribution map has 32768 entries, where
the database partition numbers are assigned to the map entries in
a round-robin fashion, by default.
- Syntax alternatives:
The following syntax alternatives are supported for compatibility with previous versions of DB2® and with other database products. These alternatives are
non-standard and should not be used.
- NODE can be specified in place of DBPARTITIONNUM
- NODES can be specified in place of DBPARTITIONNUMS
- NODEGROUP can be specified in place of DATABASE PARTITION GROUP
Examples
The
following examples are based on a partitioned database with six database
partitions defined as 0, 1, 2, 5, 7, and 8.
- Example 1: Assume that you want to create a database partition
group called MAXGROUP on all six database partitions. The statement
is as follows:
CREATE DATABASE PARTITION GROUP MAXGROUP ON ALL DBPARTITIONNUMS
- Example 2: Assume that you want to create a database partition
group called MEDGROUP on database partitions 0, 1, 2, 5, and 8. The
statement is as follows:
CREATE DATABASE PARTITION GROUP MEDGROUP
ON DBPARTITIONNUMS( 0 TO 2, 5, 8)
- Example 3: Assume that you want to create a single-partition
database partition group MINGROUP on database partition 7. The statement
is as follows:
CREATE DATABASE PARTITION GROUP MINGROUP
ON DBPARTITIONNUM (7)