SQL Reference

ALTER TABLESPACE

The ALTER TABLESPACE statement is used to modify an existing tablespace in the following ways.

Add a container to a DMS tablespace (that is, one created with the MANAGED BY DATABASE option).
Increase the size of a container in the DMS tablespace (that is, one created with the MANAGED BY DATABASE option)
Add a container to a SMS tablespace on a partition (or node) that currently has no containers.
Modify the PREFETCHSIZE setting for a tablespace.
Modify the BUFFERPOOL used for tables in the tablespace.
Modify the OVERHEAD setting for a tablespace.
Modify the TRANSFERRATE setting for a tablespace.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The authorization ID of the statement must have SYSCTRL or SYSADM authority.

Syntax

>>-ALTER--TABLESPACE--tablespace-name--------------------------->
 
      .-----------------------------------------------------------------------------------------.
      V                                                                                         |
>---------+-ADD--+-| database-container-clause |--+----------------------+-+-----------------+--+>
          |      |                                '-| on-nodes-clause |--' |                 |
          |      '-| system-container-clause |--| on-nodes-clause |--------'                 |
          |              (1)                                                                 |
          +--+-EXTEND-+---------+-| database-container-clause |-+---+----------------------+-+
          |  '-RESIZE-'         '-| all-containers-clause |-----'   '-| on-nodes-clause |--' |
          +-PREFETCHSIZE--+-number-of-pages-+------------------------------------------------+
          |               '-integer--+-K-+--'                                                |
          |                          +-M-+                                                   |
          |                          '-G-'                                                   |
          +-BUFFERPOOL--bufferpool-name------------------------------------------------------+
          +-OVERHEAD--number-of-milliseconds-------------------------------------------------+
          +-TRANSFERRATE--number-of-milliseconds---------------------------------------------+
          +-DROPPED TABLE RECOVERY--+-ON--+--------------------------------------------------+
          |                         '-OFF-'                                                  |
          '-SWITCH ONLINE--------------------------------------------------------------------'
 
>--------------------------------------------------------------><
 
database-container-clause
 
       .-,------------------------------------------------------------.
       V                                                              |
|---(------+-FILE---+--'--container-string--'----+-number-of-pages-+--+---)-->
           '-DEVICE-'                            '-integer--+-K-+--'
                                                            +-M-+
                                                            '-G-'
 
>---------------------------------------------------------------|
 
system-container-clause
 
       .-,-------------------------.
       V                           |
|---(-----'--container-string--'---+---)------------------------|
 
on-nodes-clause
 
|---ON----+-NODE--+--(------------------------------------------>
          '-NODES-'
 
      .-,--------------------------------------.
      V                                        |
>--------node-number1--+--------------------+--+--)-------------|
                       '-TO--node-number2---'
 
all-containers-clause
 
            .-CONTAINERS--.
|---(--ALL--+-------------+---+-number-of-pages-+---)-----------|
                              '-integer--+-K-+--'
                                         +-M-+
                                         '-G-'

Notes:

ADD, EXTEND, and RESIZE clauses cannot be specified in the same statement.

Description

tablespace-name: Names the tablespace. This is a one-part name. It is a long SQL identifier (either ordinary or delimited).
ADD: ADD specifies that a new container is to be added to the tablespace.
EXTEND: EXTEND specifies that existing containers are being increased in size. The size specified is the size by which the existing container is increased. If the all-containers-clause is specified, then all containers in the tablespace will increase by this size.
RESIZE: RESIZE specifies that the size of existing containers is being changed (container sizes can only be increased). The size specified is the new size for the container. If the all-containers-clause is specified, then all containers in the tablespace will be changed to this size.
database-container-clause: Adds one or more containers to a DMS tablespace. The tablespace must identify a DMS tablespace that already exists at the application server. See the description of container-clause on page ***.
system-container-clause: Adds one or more containers to an SMS tablespace on the specified partitions or nodes. The tablespace must identify an SMS tablespace that already exists at the application server. There must not be any containers on the specified partitions for the tablespace. (SQLSTATE 42921). See the description of system-containers on page ***.
on-nodes-clause: Specifies the partition or partitions for the added containers. See the description of on-nodes-clause on page ***.
all-containers-clause: Extends or resizes all of the containers in a DMS tablespace. The tablespace must identify a DMS tablespace that already exists at the application server.

PREFETCHSIZE number-of-pages: Specifies the number of PAGESIZE pages that will be read from the tablespace when data prefetching is being performed. The prefetch size value can also be specified as an integer value followed by K (for kilobytes), M (for megabytes), or G (for gigabytes). If specified in this way, the floor of the number of bytes divided by the pagesize is used to determine the number of pages value for prefetch size. Prefetching reads in data needed by a query prior to it being referenced by the query, so that the query need not wait for I/O to be performed.
BUFFERPOOL bufferpool-name: The name of the buffer pool used for tables in this tablespace. The buffer pool must currently exist in the database (SQLSTATE 42704). The nodegroup of the tablespace must be defined for the bufferpool (SQLSTATE 42735).
OVERHEAD number-of-milliseconds: Any numeric literal (integer, decimal, or floating point) that specifies the I/O controller overhead and disk seek and latency time, in milliseconds. The number should be an average for all containers that belong to the tablespace, if not the same for all containers. This value is used to determine the cost of I/O during query optimization.
TRANSFERRATE number-of-milliseconds: Any numeric literal (integer, decimal, or floating point) that specifies the time to read one page (4K or 8K) into memory, in milliseconds. The number should be an average for all containers that belong to the tablespace, if not the same for all containers. This value is used to determine the cost of I/O during query optimization.
DROPPED TABLE RECOVERY: Dropped tables in the specified tablespace may be recovered using the RECOVER DROPPED TABLE ON option of the ROLLFORWARD command.
SWITCH ONLINE: tablespaces in OFFLINE state are brought online if the containers have become accessible. If the containers are not accessible an error is returned (SQLSTATE 57048).

Notes

Guidance on choosing optimal values for the PREFETCHSIZE, OVERHEAD, and TRANSFERRATE parameters, and information on rebalancing is provided in the Administration Guide.
Once the new container has been added and the transaction is committed, the contents of the tablespace are automatically rebalanced across the containers. Access to the tablespace is not restricted during the rebalancing.
If the tablespace is in OFFLINE state and the containers have become accessible, the user can disconnect all applications and connect to the database again to bring the tablespace out of OFFLINE state. Alternatively, SWITCH ONLINE option can bring the tablespace up (out of OFFLINE) while the rest of the database is still up and being used.
If adding more than one container to a tablespace, it is recommended that they be added in the same statement so that the cost of rebalancing is incurred only once. An attempt to add containers to the same tablespace in separate ALTER TABLESPACE statements within a single transaction will result in an error (SQLSTATE 55041).
A tablespace cannot have container sizes changed and have new containers added in the same ALTER TABLESPACE statement (SQLSTATE 429BC). When changing the size of more than one container, the EXTEND clause and the RESIZE clause cannot be used simultaneously in one statement (SQLSTATE 429BC).
RESIZE can not be used to decrease container sizes. Any attempt to specify a smaller size for a container will raise an error (SQLSTATE 560B0).
Any attempts to extend or resize containers that do not exist will raise an error (SQLSTATE 428B2).
When extending or resizing a container, the container type must match the type that was used when the container was created (SQLSTATE 428B2).
Once a container has been extended or resized, and the transaction is committed, the contents of the tablespace are automatically rebalanced across the containers. Access to the table space is not restricted during the rebalance.
If extending multiple containers in a tablespace, it is recommended that the containers be changed in the same statement, so the cost of rebalancing is incurred only once. This is also true for resizing multiple containers. An attempt to change container sizes in the same tablespace, using separate ALTER TABLESPACE statements but within a single transaction, will raise an error (SQLSTATE 55041).
In a partitioned database if more than one partition resides on the same physical node, then the same device or specific path cannot be specified for such partitions (SQLSTATE 42730). For this environment, either specify a unique container-string for each partition or use a relative path name.
Although the tablespace definition is transactional and the changes to the tablespace definition are reflected in the catalog tables on commit, the buffer pool with the new definition cannot be used until the next time the database is started. The buffer pool in use, when the ALTER TABLESPACE statement was issued, will continue to be used in the interim.

Examples

Example 1: Add a device to the PAYROLL table space.

   ALTER TABLESPACE PAYROLL
      ADD (DEVICE '/dev/rhdisk9' 10000)

Example 2: Change the prefetch size and I/O overhead for the ACCOUNTING table space.

   ALTER TABLESPACE ACCOUNTING
      PREFETCHSIZE 64
      OVERHEAD 19.3

Example 3: Create a tablespace TS1, then resize the containers so that all of the containers have 2000 pages (three different ALTER TABLESPACES which will accomplish this resizing are provided).

   CREATE TABLESPACE TS1
      MANAGED BY DATABASE
      USING (FILE '/conts/cont0' 1000,
              DEVICE '/dev/rcont1' 500,
              FILE 'cont2' 700)
   ALTER TABLESPACE TS1
      RESIZE (FILE '/conts/cont0' 2000,
               DEVICE '/dev/rcont1' 2000,
               FILE 'cont2' 2000)

  ALTER TABLESPACE TS1
      RESIZE (ALL 2000)

  ALTER TABLESPACE TS1
      EXTEND (FILE '/conts/cont0' 1000,
               DEVICE '/dev/rcont1' 1500,
               FILE 'cont2' 1300)

Example 4: Extend all of the containers in the DATA_TS tablespace by 1000 pages.

   ALTER TABLESPACE DATA_TS
      EXTEND (ALL 1000)

Example 5: Resize all of the containers in the INDEX_TS tablespace to 100 megabytes (MB).

   ALTER TABLESPACE INDEX_TS
      RESIZE (ALL 100 M)

[ Top of Page | Previous Page | Next Page ]