SQL Reference

ALTER TABLESPACE

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

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

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 |--------' |
          +-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---'

Description

tablespace-name: Names the table space. 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 table space.
database-container-clause: Adds one or more containers to a DMS table space. The table space must identify a DMS table space 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 table space on the specified partitions or nodes. The table space must identify an SMS table space that already exists at the application server. There must not be any containers on the specified partitions for the table space. (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 ***.

PREFETCHSIZE number-of-pages: Specifies the number of PAGESIZE pages that will be read from the table space 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 table space. The buffer pool must currently exist in the database (SQLSTATE 42704). The nodegroup of the table space 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 table space, 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 table space, 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 table space may be recovered using the RECOVER DROPPED TABLE ON option of the ROLLFORWARD command.
SWITCH ONLINE: Table spaces 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 table space are automatically rebalanced across the containers. Access to the table space is not restricted during the rebalancing.
If the table space 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 table space out of OFFLINE state. Alternatively, SWITCH ONLINE option can bring the table space up (out of OFFLINE) while the rest of the database is still up and being used.
If adding more than one container to a table space, 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 table space in separate ALTER TABLESPACE statements within a single transaction will result in 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 table space definition is transactional and the changes to the table space 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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]