ALTER STOGROUP statement

The ALTER STOGROUP statement is used to alter the definition of a storage 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 privileges held by the authorization ID of the statement must include SYSCTRL or SYSADM authority.

Syntax

Read syntax diagramSkip visual syntax diagramALTERSTOGROUPstoragegroup-name ADD,'storage-path'DROP,'storage-path'OVERHEADnumber-of-millisecondsDEVICE READ RATEnumber-megabytes-per-secondDATA TAGinteger-constantNONESET AS DEFAULT1
Notes:
  • 1 Each clause can be specified only once.

Description

storagegroup-name
Identifies the storage group to be altered; storagegroup-name must identify a storage group that exists at the current server (SQLSTATE 42704). This is a one-part name.
ADD
Specifies that one or more new storage paths are to be added to the specified storage group.
storage-path
A string constant that specifies containers the location where automatic storage table spaces are to be created. The format of the string depends on the operating system, as illustrated in the following table:
Operating system Format of storage path string
Linux®
AIX®
Solaris
HP-UX		 An absolute path
Windows The letter name of a drive

The string can include database partition expressions to specify database partition number information in the storage path. For predictable performance, ensure the storage paths added to a storage group have similar media characteristics.

The maximum length of a storage path is 175 characters (SQLSTATE 54036).

A storage path being added must be valid according to the naming rules for paths, and must be accessible (SQLSTATE 57019). Similarly, in a partitioned database environment, the storage path must exist and be accessible on every database partition (SQLSTATE 57019).
DROP
Specifies that one or more storage paths are to be removed from the given storage group. If table spaces are actively using a storage path being dropped, then the state of the storage path is changed from "In Use" to "Drop Pending" and future use of the storage path will be prevented.

The DROP storage-path clause is not supported in a Db2® pureScale® environment (SQLSTATE 56038).

storage-path
A string constant that specifies the storage path from which storage groups are to be dropped. The format of the string depends on the operating system, as illustrated in the following table:
Operating system Format of storage path string
Linux
AIX
Solaris
HP-UX		 An absolute path
Windows The letter name of a drive

The string can include database partition expressions to specify database partition number information in the storage path.

A storage path being dropped must currently exist in the storage group (SQLSTATE 57019) and cannot already be in the "Drop Pending" state (SQLSTATE 55073).

OVERHEAD number-of-milliseconds
Specifies the I/O controller usage and disk seek and latency time. This value is used to determine the cost of I/O during query optimization. The value of number-of-milliseconds is any numeric literal (integer, decimal, or floating point). If this value is not the same for all storage paths, set the value to a numeric literal which represents the average for all storage paths that belong to the storage group.
DEVICE READ RATE number-megabytes-per-second
Represents the device specification for the read transfer rate in megabytes per second. This value is used to determine the cost of I/O during query optimization. The value of number-megabytes-per-second is any numeric literal (integer, decimal, or floating point). If this value is not the same for all storage paths, set the value to a numeric literal which represents the average for all storage paths that belong to the storage group.
DATA TAG integer-constant or DATA TAG NONE
Specifies a tag for the data in a given storage group. This value can be used as part of a WLM configuration in a work class definition or referenced within a threshold definition. For more information, see the CREATE WORK CLASS SET, ALTER WORK CLASS SET, CREATE THRESHOLD, and ALTER THRESHOLD statements.
integer-constant
Valid values for integer-constant are integers from 1 to 9.
NONE
If NONE is specified, there is no data tag.
SET AS DEFAULT
Specifies that the storage group being altered is designated as the default storage group. There can be only one storage group designated as the default storage group. There is no affect to the existing table spaces using that storage group. The designated default storage group is used by automatic storage table spaces when no storage group is specified at table space creation and a database managed table space is converted to automatic storage managed during redirected restore.

Rules

  • A storage group must have at least one storage path. Dropping all storage paths from the storage group is not permitted (SQLSTATE 428HH).
  • The ALTER STOGROUP statement cannot be executed while a database partition server is being added (SQLSTATE 55071).
  • A storage group can have up to 128 defined storage paths (SQLSTATE 5U009).
  • A transaction can have at most one ALTER STOGROUP statement per storage group. In the case of the default storage group, there can be at most one ALTER DATABASE statement or one ALTER STOGROUP statement on the default storage group (SQLSTATE 25502).

Notes

  • Adding new storage paths: When adding new storage paths:
    • Existing REGULAR and LARGE table spaces using this storage group will not initially use these new paths. The database manager might choose to create new table space containers on these paths only if an out-of-space condition occurs. You can issue ALTER TABLESPACE REBALANCE statements for existing table spaces to stripe them over the newly added storage paths.
    • Existing temporary table spaces managed by automatic storage do not automatically use new storage paths. The database must be stopped normally then restarted for containers in these table spaces to use the new storage path or paths. As an alternative, the temporary table spaces can be dropped and re-created. When created, these table spaces automatically use all storage paths that have sufficient free space.
  • Calculation of free space: When free space is calculated for a storage path on a database partition, the database manager checks for the existence of the following directories or mount points within the storage path, and will use the first one that is found. 
    <storage path>/<instance name>/NODE####/<database name>
<storage path>/<instance name>/NODE####
<storage path>/<instance name>
<storage path>
    Where:
    • <storage path> is a storage path associated with the database.
    • <instance name> is the instance under which the database resides.
    • NODE#### corresponds to the database partition number (for example, NODE0000 or NODE0001).
    • <database name> is the name of the database.
  • Isolating multiple database partitions under one storage path: File systems can be mounted at a point beneath the storage path, and the database manager will recognize that the actual amount of free space available for table space containers might not be the same amount that is associated with the storage path directory itself.

    Consider an example in which two logical database partitions exist on one physical computer, and there is a single storage path (/dbdata). Each database partition will use this storage path, but you might want to isolate the data from each partition within its own file system. In this case, a separate file system can be created for each partition and it can be mounted at /dbdata/<instance>/NODE####. When creating containers on the storage path and determining free space, the database manager will not retrieve free space information for /dbdata, but instead will retrieve it for the corresponding /dbdata/<instance>/NODE#### directory.

  • Dropping a storage path that is in use by one or more table spaces: When dropping a storage path that is in use by one or more table spaces, the state of the path changes from "In Use" to "Drop Pending". Future growth on the path will not occur.

    Before the path can be fully removed from the storage group, each affected table space must be rebalanced (using the REBALANCE clause of the ALTER TABLESPACE statement) so that its container data is moved off the storage path. Rebalance is supported only for REGULAR and LARGE table spaces. Drop and re-create temporary table spaces to have their containers removed from the dropped path. When the path is no longer in use by any table space, it will be physically removed from the database.

    For a partitioned database environment, the path is maintained independently on each partition. When a path is no longer in use on a given database partition, it will be physically removed from that partition. Other partitions might still show the path as being in the "Drop Pending" state. The list of automatic storage table spaces using drop pending storage paths can be determined by issuing the following SQL statement: 
    SELECT DISTINCT TBSP_NAME, TBSP_ID, TBSP_CONTENT_TYPE
  FROM TABLE(MON_GET_TABLESPACE(NULL,-2)) AS T
  WHERE TBSP_PATHS_DROPPED = 1
  • Dropping a storage path that was added to a storage group multiple times: It is possible for a given storage path to be added to a storage group multiple times. When using the DROP clause, specifying that particular path once will drop all instances of the path from the storage group.

Examples

  1. Add drives D and E to the storage group named COMPLIANCE.
    ALTER STOGROUP COMPLIANCE ADD 'D:\', 'E:\'
  2. Add storage paths to the storage group named COMPLIANCE.
    ALTER STOGROUP COMPLIANCE ADD '/db/filesystem3', '/db/filesystem4'
  3. Change the data tag for the OPERATIONAL storage group and designate it as the default storage group.
    ALTER STOGROUP OPERATIONAL DATA TAG 3 SET AS DEFAULT
  4. Add a storage path that uses a database partition expression to differentiate the storage paths on each of the database partitions.
    ALTER STOGROUP TESTDATA ADD '/dataForPartition $N'
  5. Remove paths /db/filesystem1 and /db/filesystem2 from storage group TESTDATA.
    ALTER STOGROUP TESTDATA DROP '/db/filesystem1', '/db/filesystem2'