ALTER DATABASE statement

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

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®	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	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

1. 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'

2. 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:\'

3. 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:'

4. 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.
5. 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.
6. 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.
7. 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.