ALTER DATABASE statement
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.
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
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-UXAn 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
toDrop 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-UXAn 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
toDrop 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
andE
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 driveG
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.
The storage path that would be used on database partition 0 isALTER DATABASE ADD STORAGE ON '/dataForPartition $N'
/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.
Database MYDB is now enabled for automatic storage.CREATE DATABASE MYDB AUTOMATIC STORAGE NO CONNECT TO MYDB ALTER DATABASE ADD STORAGE ON '/db/filesystem1', '/db/filesystem2'
- Remove
paths
/db/filesystem1
and/db/filesystem2
from the currently connected database.
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.ALTER DATABASE DROP STORAGE ON '/db/filesystem1', '/db/filesystem2'
- A storage path
with a database partition expression (/dataForPartition $N) was previously
added to the database and now it is to be removed.
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.ALTER DATABASE DROP STORAGE ON '/dataForPartition $N'