SQL Reference

CREATE NODEGROUP

The CREATE NODEGROUP statement creates a new nodegroup within the database and assigns partitions or nodes to the nodegroup, and records the nodegroup definition in the catalog.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be prepared dynamically. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The authorization ID of the statement must have SYSCTRL or SYSADM or authority.

Syntax

>>-CREATE NODEGROUP--nodegroup-name----------------------------->
 
      .-ON ALL NODES-------------------------------------------------------.
>-----+--------------------------------------------------------------------+>
      |                    .-,--------------------------------------.      |
      |                    V                                        |      |
      '-ON--+-NODES-+---(-----node-number1--+--------------------+--+---)--'
            '-NODE--'                       '-TO--node-number2---'
 
>--------------------------------------------------------------><
 

Description

nodegroup-name

Names the nodegroup. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The nodegroup-name must not identify a nodegroup that already exists in the catalog (SQLSTATE 42710). The nodegroup-name must not begin with the characters "SYS" or "IBM" (SQLSTATE 42939).

ON ALL NODES

Specifies that the nodegroup is defined over all partitions defined to the database (db2nodes.cfg file) at the time the nodegroup is created.

If a partition is added to the database system, the ALTER NODEGROUP statement should be issued to include this new partition in a nodegroup (including IBMDEFAULTGROUP). Furthermore, the REDISTRIBUTE NODEGROUP command must be issued to move data to the partition. Refer to the Administrative API Reference or the Command Reference for more information.

ON NODES

Specifies the specific partitions that are in the nodegroup. NODE is a synonym for NODES.

node-number1

Specify a specific partition number. ⁷²

TO node-number2

Specify a range of partition numbers. The value of node-number2 must be greater than or equal to the value of node-number1 (SQLSTATE 428A9). All partitions between and including the specified partition numbers are included in the nodegroup.

Rules

• Each partition or node specified by number must be defined in the db2nodes.cfg file (SQLSTATE 42729).
• Each node-number listed in the ON NODES clause must be appear at most once (SQLSTATE 42728).
• A valid node-number is between 0 and 999 inclusive (SQLSTATE 42729).

Notes

• This statement creates a partitioning map for the nodegroup (Refer to Data Partitioning Across Multiple Partitions for more information) . A partitioning map identifier (PMAP_ID) is generated for each partitioning map. This information is recorded in the catalog and can be retrieved from SYSCAT.NODEGROUPS and SYSCAT.PARTITIONMAPS. Each entry in the partitioning map specifies the target partition on which all rows that are hashed reside. For a single-partition nodegroup, the corresponding partitioning map has only one entry. For a multiple partition nodegroup, the corresponding partitioning map has 4 096 entries, where the partition numbers are assigned to the map entries in a round-robin fashion, by default.

Example

Assume that you have a partitioned database with six partitions defined as: 0, 1, 2, 5, 7, and 8.

• Assume that you want to create a nodegroup call MAXGROUP on all six partitions. The statement is as follows:

   CREATE NODEGROUP MAXGROUP
   ON ALL NODES
  

• Assume that you want to create a nodegroup MEDGROUP on partitions 0, 1, 2, 5, 8. The statement is as follows:

  CREATE NODEGROUP MEDGROUP 
   ON NODES (0 TO 2, 5, 8)
  

• Assume that you want to create a single-partition nodegroup MINGROUP on partition (or node) 7. The statement is as follows:

  CREATE NODEGROUP MINGROUP
   ON NODE (7)
  

  Note:

  The singular form of the keyword NODES is also accepted.

⁷²

node-name of the form 'NODEnnnnn' may be specified for compatibility with the previous version.