SQL Reference

ALTER NODEGROUP

The ALTER NODEGROUP statement is used to:

add one or more partitions or nodes to a nodegroup
drop one or more partitions from a nodegroup.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. 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 authority.

Syntax

>>-ALTER NODEGROUP--nodegroup-name------------------------------>
 
      .-,------------------------------------------------------------------------.
      V                                                                          |
>---------+-ADD--+-NODE--+---| nodes-clause |--+----------------------------+-+--+>
          |      '-NODES-'                     +-LIKE NODE----node-number---+ |
          |                                    '-WITHOUT TABLESPACES--------' |
          '-DROP--+-NODE--+---| nodes-clause |--------------------------------'
                  '-NODES-'
 
>--------------------------------------------------------------><
 
nodes-clause
 
         .-,--------------------------------------.
         V                                        |
|---(-------node-number1--+--------------------+--+--)----------|
                          '-TO--node-number2---'

Description

nodegroup-name

Names the nodegroup. This is a one-part name. It is an SQL identifier (either ordinary or delimited). It must be a nodegroup described in the catalog. IBMCATGROUP and IBMTEMPGROUP cannot be specified (SQLSTATE 42832).

ADD NODE

Specifies the specific partition or partitions to add to the nodegroup. NODES is a synonym for NODE. Any specified partition must not already be defined in the nodegroup (SQLSTATE 42728).

DROP NODE

Specifies the specific partition or partitions to drop from the nodegroup. NODES is a synonym for NODE. Any specified partition must already be defined in the nodegroup (SQLSTATE 42729).

nodes-clause

Specifies the partition or partitions to be added or dropped.

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).

LIKE NODE node-number

Specifies that the containers for the existing table spaces in the nodegroup will be the same as the containers on the specified node-number. The partition specified must be a partition that existed in the nodegroup prior to this statement and is not included in a DROP NODE clause of the same statement.

WITHOUT TABLESPACES

Specifies that the default table spaces are not created on the newly added partition or partitions. The ALTER TABLESPACE using the FOR NODE clause must be used to define containers for use with the table spaces that are defined on this nodegroup. If this option is not specified, the default containers are specified on newly added partitions for each table space defined on the nodegroup.

Rules

Each partition or node specified by number must be defined in the db2nodes.cfg file (SQLSTATE 42729). See Data Partitioning Across Multiple Partitions for information about this file.
Each node-number listed in the ON NODES clause must be for a unique partition (SQLSTATE 42728).
A valid partition number is between 0 and 999 inclusive (SQLSTATE 42729).
A partition cannot appear in both the ADD and DROP clauses (SQLSTATE 42728).
There must be at least one partition remaining in the nodegroup. The last partition cannot be dropped from a nodegroup (SQLSTATE 428C0).
If neither the LIKE NODE clause nor the WITHOUT TABLESPACES clause is specified when adding a partition, the default is to use the lowest partition number of the existing partitions in the nodegroup (say it is 2) and proceed as if LIKE NODE 2 had been specified. For an existing partition to be used as the default it must have containers defined for all the table spaces in the nodegroup (column IN_USE of SYSCAT.NODEGROUPDEF is not 'T').

Notes

When a partition or node is added to a nodegroup, a catalog entry is made for the partition (see SYSCAT.NODEGROUPDEF). The partitioning map is changed immediately to include the new partition along with an indicator (IN_USE) that the partition is in the partitioning map if either:
- no table spaces are defined in the nodegroup or
- no tables are defined in the table spaces defined in the nodegroup and the WITHOUT TABLESPACES clause was not specified.
The partitioning map is not changed and the indicator (IN_USE) is set to indicate that the partition is not included in the partitioning map if either:
- tables exist in table spaces in the nodegroup or
- table spaces exist in the nodegroup and the WITHOUT TABLESPACES clause was specified.
To change the partitioning map, the REDISTRIBUTE NODEGROUP command must be used. This redistributes any data, changes the partitioning map, and changes the indicator. Table space containers need to be added before attempting to redistribute data if the WITHOUT TABLESPACES clause was specified.
When a partition is dropped from a nodegroup, the catalog entry for the partition (see SYSCAT.NODEGROUPDEF) is updated. If there are no tables defined in the table spaces defined in the nodegroup, the partitioning map is changed immediately to exclude the dropped partition and the entry for the partition in the nodegroup is dropped. If tables exist, the partitioning map is not changed and the indicator (IN_USE) is set to indicate that the partition is waiting to be dropped. The REDISTRIBUTE NODEGROUP command must be used to redistribute the data and drop the entry for the partition from the nodegroup.

Example

Assume that you have a six-partition database that has the following partitions: 0, 1, 2, 5, 7, and 8. Two partitions are added to the system with partition numbers 3 and 6.

Assume that you want to add both partitions or nodes 3 and 6 to a nodegroup called MAXGROUP and have the table space containers like those on partition 2. The statement is as follows:
```
 ALTER  NODEGROUP MAXGROUP
 ADD NODES (3,6) LIKE NODE 2
```
Assume that you want to drop partition 1 and add partition 6 to nodegroup MEDGROUP. You will define the table space containers separately for partition 6 using ALTER TABLESPACE. The statement is as follows:
```
ALTER NODEGROUP MEDGROUP
 ADD NODE(6) WITHOUT TABLESPACES
 DROP NODE(1)
```

[ Top of Page | Previous Page | Next Page ]