ALTER DATABASE PARTITION GROUP statement

The ALTER DATABASE PARTITION GROUP statement is used to add one or more database partitions to a database partition group, or drop one or more database partitions from a database partition group.

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 authorization ID of the statement must have SYSCTRL or SYSADM authority.

Syntax

Read syntax diagramSkip visual syntax diagramALTER DATABASE PARTITION GROUPdb-partition-name ,ADDDBPARTITIONNUMDBPARTITIONNUMSdb-partitions-clausedb-partition-optionsDROPDBPARTITIONNUMDBPARTITIONNUMSdb-partitions-clause
db-partitions-clause
Read syntax diagramSkip visual syntax diagram( ,db-partition-number1TOdb-partition-number2 )
db-partition-options
Read syntax diagramSkip visual syntax diagramLIKE DBPARTITIONNUMdb-partition-numberWITHOUT TABLESPACES

Description

db-partition-name
Names the database partition group. This is a one-part name. It is an SQL identifier (either ordinary or delimited). It must be a database partition group described in the catalog. IBMCATGROUP and IBMTEMPGROUP cannot be specified (SQLSTATE 42832).
ADD DBPARTITIONNUM
Specifies the specific database partition or partitions to add to the database partition group. DBPARTITIONNUMS is a synonym for DBPARTITIONNUM. Any specified database partition must not already be defined in the database partition group (SQLSTATE 42728).
DROP DBPARTITIONNUM
Specifies the specific database partition or partitions to drop from the database partition group. DBPARTITIONNUMS is a synonym for DBPARTITIONNUM. Any specified database partition must already be defined in the database partition group (SQLSTATE 42729).
db-partitions-clause
Specifies the database partition or partitions to be added or dropped.
db-partition-number1
Specify a specific database partition number.
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).
db-partition-options
LIKE DBPARTITIONNUM db-partition-number
Specifies that the containers for the existing table spaces in the database partition group will be the same as the containers on the specified db-partition-number. The specified database partition must be a partition that existed in the database partition group before this statement, and that is not included in a DROP DBPARTITIONNUM clause of the same statement.

For table spaces that are defined to use automatic storage (that is, table spaces that were created with the MANAGED BY AUTOMATIC STORAGE clause of the CREATE TABLESPACE statement, or for which no MANAGED BY clause was specified at all), the containers will not necessarily match those from the specified partition. Instead, containers will automatically be assigned by the database manager based on the storage paths that are associated with the database, and this might or might not result in the same containers being used. The size of each table space is based on the initial size that was specified when the table space was created, and might not match the current size of the table space on the specified partition.

WITHOUT TABLESPACES
Specifies that the containers for existing table spaces in the database partition group are not created on the newly added database partition or partitions. The ALTER TABLESPACE statement using the db-partitions-clause or the MANAGED BY AUTOMATIC STORAGE clause must be used to define containers for use with the table spaces that are defined on this database partition group. If this option is not specified, the default containers are specified on newly added database partitions for each table space defined on the database partition group.

This option is ignored for table spaces that are defined to use automatic storage (that is, table spaces that were created with the MANAGED BY AUTOMATIC STORAGE clause of the CREATE TABLESPACE statement, or for which no MANAGED BY clause was specified at all). There is no way to defer container creation for these table spaces. Containers will automatically be assigned by the database manager based on the storage paths that are associated with the database. The size of each table space will be based on the initial size that was specified when the table space was created.

Rules

  • Each database partition specified by number must be defined in the db2nodes.cfg file (SQLSTATE 42729).
  • Each db-partition-number listed in the db-partitions-clause must be for a unique database partition (SQLSTATE 42728).
  • A valid database partition number is between 0 and 999 inclusive (SQLSTATE 42729).
  • A database partition cannot appear in both the ADD and DROP clauses (SQLSTATE 42728).
  • There must be at least one database partition remaining in the database partition group. The last database partition cannot be dropped from a database partition group (SQLSTATE 428C0).
  • If neither the LIKE DBPARTITIONNUM clause nor the WITHOUT TABLESPACES clause is specified when adding a database partition, the default is to use the lowest database partition number of the existing database partitions in the database partition group (say it is 2) and proceed as if LIKE DBPARTITIONNUM 2 had been specified. For an existing database partition to be used as the default, it must have containers defined for all the table spaces in the database partition group (column IN_USE of SYSCAT.DBPARTITIONGROUPDEF is not 'T').
  • The ALTER 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

  • When a database partition is added to a database partition group, a catalog entry is made for the database partition (see SYSCAT.DBPARTITIONGROUPDEF). The distribution map is changed immediately to include the new database partition, along with an indicator (IN_USE) that the database partition is in the distribution map if either:
    • no table spaces are defined in the database partition group or
    • no tables are defined in the table spaces defined in the database partition group and the WITHOUT TABLESPACES clause was not specified.
    The distribution map is not changed and the indicator (IN_USE) is set to indicate that the database partition is not included in the distribution map if either:
    • Tables exist in table spaces in the database partition group or
    • Table spaces exist in the database partition group and the WITHOUT TABLESPACES clause was specified (unless all of the table spaces are defined to use automatic storage, in which case the WITHOUT TABLESPACES clause is ignored)

    To change the distribution map, the REDISTRIBUTE DATABASE PARTITION GROUP command must be used. This redistributes any data, changes the distribution 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 database partition is dropped from a database partition group, the catalog entry for the database partition (see SYSCAT.DBPARTITIONGROUPDEF) is updated. If there are no tables defined in the table spaces defined in the database partition group, the distribution map is changed immediately to exclude the dropped database partition and the entry for the database partition in the database partition group is dropped. If tables exist, the distribution map is not changed and the indicator (IN_USE) is set to indicate that the database partition is waiting to be dropped. The REDISTRIBUTE DATABASE PARTITION GROUP command must be used to redistribute the data and drop the entry for the database partition from the database partition group.
  • 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

Example

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

  • Example 1: Assume that you want to add database partitions 3 and 6 to a database partition group called MAXGROUP, and have table space containers like those on database partition 2. The statement is as follows: 
       ALTER DATABASE PARTITION GROUP MAXGROUP
     ADD DBPARTITIONNUMS (3,6)LIKE DBPARTITIONNUM 2
  • Example 2: Assume that you want to drop database partition 1 and add database partition 6 to database partition group MEDGROUP. You will define the table space containers separately for database partition 6 using ALTER TABLESPACE. The statement is as follows: 
       ALTER DATABASE PARTITION GROUP MEDGROUP
     ADD DBPARTITIONNUM(6)WITHOUT TABLESPACES
     DROP DBPARTITIONNUM(1)