DB2 10.5 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

Read syntax diagramSkip visual syntax diagram
>>-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 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.

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

Notes

Examples

  1. Create a storage group named HIGHEND with two paths under the /db directory (/db/filesystem1 and /db/filesystem2) which are attached to Solid State Disks.
    CREATE STOGROUP HIGHEND ON '/db/filesystem1', '/db/filesystem2' 
       OVERHEAD 0.75 DEVICE READ RATE 500 
  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 
  3. Create a storage group named MIDRANGE with two paths under the /db directory, and designate it as the default storage group.
    CREATE STOGROUP MIDRANGE ON '/db/filesystem1', '/db/filesystem2' SET AS DEFAULT