DB2 10.5 for Linux, UNIX, and Windows

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 diagram
>>-ALTER DATABASE PARTITION GROUP--db-partition-name------------>

   .-,------------------------------------------------------------------------------------.   
   V                                                                                      |   
>----+-ADD--+-DBPARTITIONNUM--+--| db-partitions-clause |--+--------------------------+-+-+-><
     |      '-DBPARTITIONNUMS-'                            '-| db-partition-options |-' |     
     '-DROP--+-DBPARTITIONNUM--+--| db-partitions-clause |------------------------------'     
             '-DBPARTITIONNUMS-'                                                              

db-partitions-clause

      .-,--------------------------------------------------.      
      V                                                    |      
|--(----db-partition-number1--+--------------------------+-+--)--|
                              '-TO--db-partition-number2-'        

db-partition-options

|--+-LIKE DBPARTITIONNUM----db-partition-number---+-------------|
   '-WITHOUT 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

Notes

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.