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 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
- Add
drives D and E to the storage group named
COMPLIANCE.
ALTER STOGROUP COMPLIANCE ADD 'D:\', 'E:\'
- Add
storage paths to the storage group named
COMPLIANCE.
ALTER STOGROUP COMPLIANCE ADD '/db/filesystem3', '/db/filesystem4'
- 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
- 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'
- Remove
paths /db/filesystem1 and /db/filesystem2 from
storage group TESTDATA.
ALTER STOGROUP TESTDATA DROP '/db/filesystem1', '/db/filesystem2'