DB2 Version 10.1 for Linux, UNIX, and Windows

CREATE STOGROUP statement

The CREATE STOGROUP statement defines a new storage group within the database, assigns storage paths to the storage group, and records the storage group definition and attributes in the catalog.

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


>>-CREATE--STOGROUP--storagegroup-name-------------------------->

      .-,--------------.      
      V                |      
>--ON---'storage-path'-+--●------------------------------------->

>--+----------------------------------+--●---------------------->
   '-OVERHEAD--number-of-milliseconds-'      

>--+-----------------------------------------------+--●--------->
   '-DEVICE READ RATE--number-megabytes-per-second-'      

>--+--------------------------------+--●------------------------>
   '-DATA TAG--+-integer-constant-+-'      
               '-NONE-------------'        

>--+----------------+--●---------------------------------------><
   '-SET AS DEFAULT-'

Description

storagegroup-name

Names the storage group. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The storagegroup-name must not identify a storage group that already exists at the current server (SQLSTATE 42710). The storagegroup-name must not begin with the characters 'SYS' (SQLSTATE 42939).

ON

Specifies storage paths to be added for the named storage group. For partitioned database environments, the same storage paths will be defined on all database partitions unless database partition expressions are used.

storage-path: A string constant that specifies either an absolute path or the letter name of a drive (Windows operating systems only) on which containers for automatic storage table spaces are to be created. The string can include database partition expressions to specify database partition number information in the storage path.
The maximum length of a 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).

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.

If the OVERHEAD clause is not specified, the OVERHEAD will be set to 6.725 milliseconds.

DEVICE READ RATE number-megabytes-per-second

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

If the DEVICE READ RATE clause is not specified, the DEVICE READ RATE will be set to the built-in default of 100 megabytes per second.

DATA TAG integer-constant or DATA TAG NONE

Specifies a tag for the data for table spaces using this storage group unless explicitly overridden by the table space definition. 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 and CREATE 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 the storage group being created is designated as the default storage group. If there is no default storage group, the first one created will be designated the default even if this clause is not specified. Since there can only be one storage group designated as the default storage group, specifying this clause removes the default attribute from the existing default storage group. Specifying a new default storage group has no affect to the storage group used by existing table spaces.

Rules

The CREATE 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 database instance can have up to 256 defined storage groups (SQLSTATE 54035).

Notes

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 (/db2data). 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 /db2data/<instance>/NODE####. When creating containers on the storage path and determining free space, the database manager will not retrieve free space information for /db2data, but instead will retrieve it for the corresponding /db2data/<instance>/NODE#### directory.
Multiple storage paths: A storage path can be added to different storage groups, or to the same storage group multiple times.
Similar media characteristics: Ensure that the storage paths added to a storage group have similar media characteristics. If the media characteristics are dissimilar, specify a value which represents an average for OVERHEAD and DEVICE READ RATE.

Examples

Example 1: Create a storage group named HIGHEND with two paths under the /db2 directory (/db2/filesystem1 and /db2/filesystem2) which are attached to Solid State Disks.
```
CREATE STOGROUP HIGHEND ON '/dbe/filesystem1', '/db2/filesystem2' 
   OVERHEAD 0.75 DEVICE READ RATE 500 
```
Example 2: Create a storage group named MIDRANGE with two drives D and E and designate it as the default storage group.
```
CREATE STOGROUP MIDRANGE ON 'D:\', 'E:\' SET AS DEFAULT 
```