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

>>-ALTER DATABASE--+---------------+---------------------------->
                   '-database-name-'   

   .-----------------------------------------------.   
   |                         .-,--------------.    |   
   V  (1)                    V                |    |   
>----------+-ADD STORAGE ON----'storage-path'-+--+-+-----------><
           |                  .-,--------------. |     
           |                  V                | |     
           '-DROP STORAGE ON----'storage-path'-+-'     

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

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 either an absolute path or the letter name of a drive (Windows operating systems only).

Rules

• 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).

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 (/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.

• In general, the same storage paths must be used for each partition in a multi-partition database. 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 SYSIBMADM.SNAPTBSP A, SYSIBMADM.SNAPTBSP_PART B
    WHERE A.TBSP_ID = B.TBSP_ID AND B.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

   ALTER DATABASE ADD STORAGE ON '/db2/filesystem1', '/db2/filesystem2',
     '/filesystem3'

   ALTER DATABASE SAMPLE ADD STORAGE ON 'D:', 'E:\'

   ALTER DATABASE ADD STORAGE ON 'F:\DB2DATA', 'G:'

   ALTER DATABASE ADD STORAGE ON '/dataForPartition $N'

   CREATE DATABASE MYDB AUTOMATIC STORAGE NO
   CONNECT TO MYDB
   ALTER DATABASE ADD STORAGE ON '/db2/filesystem1', '/db2/filesystem2' 

   ALTER DATABASE DROP STORAGE ON '/db2/filesystem1', '/db2/filesystem2'

   ALTER DATABASE DROP STORAGE ON '/dataForPartition $N'