The
ALTER DATABASE statement adds new storage paths to, or
removes existing storage paths from, the collection of paths
that are used for automatic storage table spaces.
An automatic storage table space is a table space that
has been created using automatic storage; that is, the MANAGED BY
AUTOMATIC STORAGE clause has been specified on the CREATE TABLESPACE
statement, or no MANAGED BY clause has been specified at all. If a
database is enabled for automatic storage, container and space management
characteristics of its table spaces can be completely determined by
the database manager. If the database is not
currently enabled for automatic storage then the act of adding storage
paths will enable it.
Important: This statement is deprecated
and might be removed in a future release. Use the CREATE STOGROUP
or ALTER STOGROUP statements instead.
Invocation
The 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 either SYSADM or SYSCTRL
authority.
Syntax
>>-ALTER DATABASE--+---------------+---------------------------->
'-database-name-'
.-----------------------------------------------.
| .-,--------------. |
V (1) V | |
>----------+-ADD STORAGE ON----'storage-path'-+--+-+-----------><
| .-,--------------. |
| V | |
'-DROP STORAGE ON----'storage-path'-+-'
Notes:
- Each clause can be specified only once.
Description
- database-name
- An optional value specifying the name of the database that is
to be altered. If specified, the value must match the name of the
database to which the application is currently connected (not the
alias that the client might have cataloged); otherwise, an error is
returned (SQLSTATE 42961).
- ADD STORAGE ON
- Specifies that one or more new storage paths are
to be added to the collection of storage paths that
are used for automatic storage table spaces.
- 'storage-path'
- A
string constant that specifies the location where containers for 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 |
String format |
Linux AIX® Solaris HP-UX |
An absolute path. |
Windows |
The letter name of a drive. |
- DROP STORAGE ON
- Specifies that one or more storage paths are to be removed from
the collection of storage paths that are used for automatic storage
table spaces. 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.
- 'storage-path'
- A
string constant that specifies the location from which storage paths
are to be removed. The format of the string depends on the operating
system, as illustrated in the following table:
Operating system |
String format |
Linux AIX Solaris HP-UX |
An absolute path. |
Windows |
The letter name of a drive. |
Rules
- For a database that
is running on Version 10.1 or
later, the operations of this statement are applied to the default storage group for the database. If no
storage group is defined for the database, the name IBMSTOGROUP is used.
- 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).
- A storage path being dropped must currently
exist in the database (SQLSTATE 57019) and cannot already be in the "Drop
Pending" state (SQLSTATE 55073).
- A database enabled for automatic storage must
have at least one storage path. Dropping all storage paths from the
database is not permitted (SQLSTATE 428HH).
- The ALTER DATABASE statement cannot be executed
while a database partition server is being added (SQLSTATE 55071).
- DROP
STORAGE ON cannot be specified in a DB2® pureScale®
environment (SQLSTATE 56038).
Notes
- When adding new storage paths:
- Existing regular and large table spaces using automatic storage
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.
- 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 recreated. When created, these table spaces
automatically use all storage paths that have sufficient free space.
- Adding storage paths to the database to enable
automatic storage will not cause the database to convert existing
non-automatic storage enabled table spaces to use automatic storage.
- Although ADD STORAGE and DROP STORAGE are
logged operations, whether they are redone
during a rollforward operation depends on how the database was restored.
If the restore operation does not redefine the storage paths that
are associated with the database, the log record that contains the
storage path change is redone, and the
storage paths that are described in the log record are added or dropped during the rollforward operation.
However, if the storage paths are redefined during the restore
operation, the rollforward operation will not redo ADD
STORAGE or DROP STORAGE log records, because it is assumed that
you have already set up the storage paths.
- 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
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
machine, 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.
- In
general, the same storage paths must be used for each partition in
a partitioned database environment. One exception to this is the case
in which database partition expressions are used within the storage
path. Doing this allows the database partition number to be reflected
in the storage path, such that the resulting path name is different
on each partition.
- 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 database, 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 only supported for regular and
large table spaces. Temporary table spaces should be dropped and recreated
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, 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 may 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 A.TBSP_NAME, A.TBSP_ID, A.TBSP_CONTENT_TYPE
FROM TABLE(MON_GET_TABLESPACE(NULL,-2)) AS A
WHERE A.TBSP_PATHS_DROPPED = 1
- When dropping a storage path that was originally
specified using a database partition expression, the same storage
path string, including the database partition expression, must be
used in the drop. If a database partition expression was specified
then this path string can be found in the "Path with db partition
expression" element (db_storage_path_with_dpe) of a database snapshot.
This element is not shown if a database partition expression was not
included in the original path specified.
- It is possible for a given storage path to be
added to a database multiple times. When using the DROP STORAGE ON
clause, specifying that particular path once will drop all instances
of the path from the database.
Examples
- Add
two paths under the /db directory (/db/filesystem1 and /db/filesystem2)
and a third path named /filesystem3 to the space
for automatic storage table spaces that is associated with the currently
connected database.
ALTER DATABASE ADD STORAGE ON '/db/filesystem1', '/db/filesystem2',
'/filesystem3'
- Add
drives D and E to the space for
automatic storage table spaces that is associated with the SAMPLE
database.
ALTER DATABASE SAMPLE ADD STORAGE ON 'D:', 'E:\'
- Add directory F:\DBDATA and drive G to
the space for automatic storage table spaces that is associated with
the currently connected database.
ALTER DATABASE ADD STORAGE ON 'F:\DBDATA', 'G:'
- Add a storage path that uses a database partition expression to
differentiate the storage paths on each of the database partitions.
ALTER DATABASE ADD STORAGE ON '/dataForPartition $N'
The
storage path that would be used on database partition 0 is /dataForPartition0;
on database partition 1, it would be /dataForPartition1;
and so on.
- Add
storage paths to a database that is not automatic storage enabled,
for the purposes of enabling automatic storage for the database.
CREATE DATABASE MYDB AUTOMATIC STORAGE NO
CONNECT TO MYDB
ALTER DATABASE ADD STORAGE ON '/db/filesystem1', '/db/filesystem2'
Database
MYDB is now enabled for automatic storage.
- Remove
paths /db/filesystem1 and /db/filesystem2 from
the currently connected database.
ALTER DATABASE DROP STORAGE ON '/db/filesystem1', '/db/filesystem2'
After the storage is dropped successfully, use the ALTER TABLESPACE
statement with the REBALANCE clause for each table space that was
using these storage paths to rebalance the table space.
- A storage path
with a database partition expression (/dataForPartition $N) was previously
added to the database and now it is to be removed.
ALTER DATABASE DROP STORAGE ON '/dataForPartition $N'
After
the storage is dropped successfully, use the ALTER TABLESPACE statement
with the REBALANCE clause for each table space that was using these
storage paths to rebalance the table space.