CREATE DATABASE PARTITION GROUP statement

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

Read syntax diagramSkip visual syntax diagramCREATE DATABASE PARTITION GROUPdb-partition-group-name ON ALL DBPARTITIONNUMSONDBPARTITIONNUMSDBPARTITIONNUM(,db-partition-number1TOdb-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)