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
>>-ALTER--STOGROUP--storagegroup-name--------------------------->
.-------------------------------------------------------.
| .-,--------------. |
V V | (1) |
>----+-ADD---'storage-path'-+------------------------+-----+---><
| .-,--------------. |
| V | |
+-DROP---'storage-path'-+-----------------------+
+-OVERHEAD--number-of-milliseconds--------------+
+-DEVICE READ RATE--number-megabytes-per-second-+
+-DATA TAG--+-integer-constant-+----------------+
| '-NONE-------------' |
'-SET AS DEFAULT--------------------------------'
Notes:
- 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 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.
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 either an absolute path or the letter
name of a drive (Windows operating
systems only). 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
(/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.
- 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
- Example 1: Add drives D and E to the storage group named
COMPLIANCE.
ALTER STOGROUP COMPLIANCE ADD 'D:\', 'E:\'
- Example 2: 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
- Example 3: 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'
- Example 4: Remove paths /db2/filesystem1 and /db2/filesystem2 from
storage group TESTDATA.
ALTER STOGROUP TESTDATA DROP '/db2/filesystem1', '/db2/filesystem2'