DB2 Version 9.7 for Linux, UNIX, and Windows

ALTER TABLESPACE statement

The ALTER TABLESPACE statement is used to modify an existing table space.

You can modify a tablespace in the following ways:

Add a container to, or drop a container from a DMS table space; that is, a table space created with the MANAGED BY DATABASE option.
Modify the size of a container in a DMS table space.
Lower the high water mark for a DMS table space through extent movement.
Add a container to an SMS table space on a database partition 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.
Modify the file system caching policy for a table space.
Enable or disable auto-resize for a DMS or automatic storage table space.
Rebalance a regular or large automatic storage 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 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 SYSCTRL or SYSADM authority.

Syntax


>>-ALTER TABLESPACE--tablespace-name---------------------------->

   .-----------------------------------------------------------------------------------------------------------.   
   V                                                                                                           |   
>----+-ADD--+-+--------------------------+--| database-container-clause |--+-----------------------------+-+-+-+-><
     |      | '-TO STRIPE SET--stripeset-'                                 '-| on-db-partitions-clause |-' | |     
     |      '-| system-container-clause |--| on-db-partitions-clause |-------------------------------------' |     
     +-BEGIN NEW STRIPE SET--| database-container-clause |--+-----------------------------+------------------+     
     |                                                      '-| on-db-partitions-clause |-'                  |     
     +-DROP--| drop-container-clause |--+-----------------------------+--------------------------------------+     
     |                                  '-| on-db-partitions-clause |-'                                      |     
     +-REDUCE--+-------------------------------+--+-----------------------------+----------------------------+     
     |         +-| database-container-clause |-+  '-| on-db-partitions-clause |-'                            |     
     |         +-| all-containers-clause |-----+                                                             |     
     |         +-MAX---------------------------+                                                             |     
     |         +-STOP--------------------------+                                                             |     
     |         '-integer--+---------+----------'                                                             |     
     |                    +-K-------+                                                                        |     
     |                    +-M-------+                                                                        |     
     |                    +-G-------+                                                                        |     
     |                    '-PERCENT-'                                                                        |     
     +-+-EXTEND-+--+-| database-container-clause |-+--+-----------------------------+------------------------+     
     | '-RESIZE-'  '-| all-containers-clause |-----'  '-| on-db-partitions-clause |-'                        |     
     +-REBALANCE---------------------------------------------------------------------------------------------+     
     +-PREFETCHSIZE--+-AUTOMATIC-------+---------------------------------------------------------------------+     
     |               +-number-of-pages-+                                                                     |     
     |               '-integer--+-K-+--'                                                                     |     
     |                          +-M-+                                                                        |     
     |                          '-G-'                                                                        |     
     +-BUFFERPOOL--bufferpool-name---------------------------------------------------------------------------+     
     +-OVERHEAD--number-of-milliseconds----------------------------------------------------------------------+     
     +-TRANSFERRATE--number-of-milliseconds------------------------------------------------------------------+     
     +-+-FILE SYSTEM CACHING----+----------------------------------------------------------------------------+     
     | '-NO FILE SYSTEM CACHING-'                                                                            |     
     +-DROPPED TABLE RECOVERY--+-ON--+-----------------------------------------------------------------------+     
     |                         '-OFF-'                                                                       |     
     +-SWITCH ONLINE-----------------------------------------------------------------------------------------+     
     +-AUTORESIZE--+-NO--+-----------------------------------------------------------------------------------+     
     |             '-YES-'                                                                                   |     
     +-INCREASESIZE--integer--+-PERCENT-+--------------------------------------------------------------------+     
     |                        '-+-K-+---'                                                                    |     
     |                          +-M-+                                                                        |     
     |                          '-G-'                                                                        |     
     +-MAXSIZE--+-integer--+-K-+-+---------------------------------------------------------------------------+     
     |          |          +-M-+ |                                                                           |     
     |          |          '-G-' |                                                                           |     
     |          '-NONE-----------'                                                                           |     
     +-CONVERT TO LARGE--------------------------------------------------------------------------------------+     
     +-LOWER HIGH WATER MARK--+------+-----------------------------------------------------------------------+     
     |                        '-STOP-'                                                                       |     
     '-MANAGED BY AUTOMATIC STORAGE--------------------------------------------------------------------------'     

database-container-clause

      .-,---------------------------------------------------.      
      V                                                     |      
|--(----+-FILE---+--'container-string'--+-number-of-pages-+-+--)--|
        '-DEVICE-'                      '-integer--+-K-+--'        
                                                   +-M-+           
                                                   '-G-'           

drop-container-clause

      .-,------------------------------.      
      V                                |      
|--(----+-FILE---+--'container-string'-+--)---------------------|
        '-DEVICE-'                            

system-container-clause

      .-,------------------.      
      V                    |      
|--(----'container-string'-+--)---------------------------------|

on-db-partitions-clause

|--ON--+-DBPARTITIONNUM--+-------------------------------------->
       '-DBPARTITIONNUMS-'   

      .-,--------------------------------------------------.      
      V                                                    |      
>--(----db-partition-number1--+--------------------------+-+--)--|
                              '-TO--db-partition-number2-'        

all-containers-clause

           .-CONTAINERS-.                           
|--(--ALL--+------------+--+-number-of-pages-+--)---------------|
                           '-integer--+-K-+--'      
                                      +-M-+         
                                      '-G-'

Description

tablespace-name

Names the table space. This is a one-part name. It is a long SQL identifier (either ordinary or delimited).

ADD

Specifies that one or more new containers are to be added to the table space.

TO STRIPE SET stripeset

Specifies that one or more new containers are to be added to the table space, and that they will be placed into the given stripe set.

BEGIN NEW STRIPE SET

Specifies that a new stripe set is to be created in the table space, and that one or more containers are to be added to this new stripe set. Containers that are subsequently added using the ADD option will be added to this new stripe set unless TO STRIPE SET is specified.

DROP

Specifies that one or more containers are to be dropped from the table space.

REDUCE

For non-automatic storage table spaces, specifies that existing containers are to be reduced in size. The size specified is the size by which the existing container is decreased. If the all-containers-clause is specified, all containers in the table space will decrease by this size. If the reduction in size will result in a table space size that is smaller than the current high water mark, an attempt will be made to reduce the high water mark before attempting to reduce the containers. For non-automatic storage table spaces, the REDUCE clause must be followed by a database-container-clause or an all-containers-clause.

For automatic storage table spaces, specifies that the current high water mark is to be reduced, if possible, and that the size of the table space is to be reduced to the new high water mark. For automatic storage table spaces, the REDUCE clause must not be followed by a database-container-clause, an all-containers-clause, or an on-db-partitions-clause.

Note: The REDUCE option with the MAX, numeric value, PERCENT, or STOP clauses, and the LOWER HIGH WATER MARK option including the STOP clause, are only available for database managed, and automatic storage managed, table spaces with the reclaimable storage attribute. Moreover, these options must be specified and run without any other options, including each other.

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.

all-containers-clause

Extends, reduces, or resizes all of the containers in a DMS table space. The table space must identify a DMS table space that already exists at the application server.

MAX

For automatic storage table spaces with reclaimable storage, specifies that the maximum number of extents should be moved to the beginning of the table space to lower the high water mark. Additionally, the size of the table space will be reduced to the new high water mark. This does not apply to non-automatic storage table spaces.

STOP

For automatic storage table spaces with reclaimable storage, interrupts the extent movement operation if in progress. This option is not available for non-automatic storage table spaces.

integer [K | M | G] or integer PERCENT

For automatic storage table spaces with reclaimable storage, specifies the numeric value by which the table space is to be reduced through extent movement. The value can be expressed in several ways:

An integer specified without K, M, G, or PERCENT indicates that the numeric value is the number of pages by which the table space is to be reduced.
An integer specified with K, M, or G indicates the reduction size in kilobytes, megabytes, or gigabytes, respectively. The value is first converted from bytes to number of pages based on the page size of the table space.
An integer specified with PERCENT indicates the number of extents to move, as a percentage of the current size of the table space.

Once extent movement is complete, the table space size is reduced to the new high water mark. This option is not available for non-automatic storage table spaces.

on-db-partitions-clause

Specifies one or more database partitions for the corresponding container operations.

EXTEND

Specifies that existing containers are to be increased in size. The size specified is the size by which the existing container is increased. If the all-containers-clause is specified, all containers in the table space will increase by this size.

RESIZE

Specifies that the size of existing containers is to be changed. The size specified is the new size for the container. If the all-containers-clause is specified, all containers in the table space will be changed to this size. If the operation affects more than one container, these containers must all either increase in size, or decrease in size. It is not possible to increase some while decreasing others (SQLSTATE 429BC).

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.

drop-container-clause

Drops one or more containers from a DMS table space. The table space must identify a DMS table space that already exists at the application server.

system-container-clause

Adds one or more containers to an SMS table space on the specified database partitions. 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 database partitions for the table space (SQLSTATE 42921).

on-db-partitions-clause

Specifies one or more database partitions for the corresponding container operations.

all-containers-clause

Extends, reduces, or resizes all of the containers in a DMS table space. The table space must identify a DMS table space that already exists at the application server.

REBALANCE: For regular and large automatic storage table spaces, initiates the creation of containers on recently added storage paths, the drop of containers from storage paths that are in the "Drop Pending" state, or both. During the rebalance, data is moved into containers on new paths, and moved out of containers on dropped paths. The rebalance runs asynchronously in the background and does not affect the availability of data.

PREFETCHSIZE

Specifies to read 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.

AUTOMATIC

Specifies that the prefetch size of a table space is to be updated automatically; that is, the prefetch size will be managed by DB2® database manager.

A DB2 database will update the prefetch size automatically whenever the number of containers in a table space changes (following successful execution of an ALTER TABLESPACE statement that adds or drops one or more containers). The prefetch size is also automatically updated at database startup.

Automatic updating of the prefetch size can be turned off by specifying a numeric value in the PREFETCHSIZE clause.

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 page size is used to determine the number of pages value for prefetch size.

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 database partition group 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.

FILE SYSTEM CACHING or NO FILE SYSTEM CACHING

Specifies whether or not I/O operations will be cached at the file system level. Connections to the database must be terminated before a new caching policy takes effect. Note that I/O access to long or LOB data is buffered for both SMS and DMS containers.

FILE SYSTEM CACHING: All I/O operations in the target table space will be cached at the file system level.
NO FILE SYSTEM CACHING: All I/O operations will bypass the file system level cache.
Note: On AIX, Solaris, Linux, and Windows operating systems, you must format the disk device to have a disk sector size of 512 bytes. On HP-UX operating systems, the disk sector size must be 1024 bytes.

DROPPED TABLE RECOVERY

Specifies whether or not tables that have been dropped from tablespace-name can be recovered using the RECOVER DROPPED TABLE ON option of the ROLLFORWARD DATABASE command. For partitioned tables, dropped table recovery is always on, even if dropped table recovery is turned off for non-partitioned tables in one or more table spaces.

ON: Specifies that dropped tables can be recovered.
OFF: Specifies that dropped tables cannot be recovered.

SWITCH ONLINE

Specifies that table spaces in OFFLINE state are to be brought online if their containers have become accessible. If the containers are not accessible, an error is returned (SQLSTATE 57048).

AUTORESIZE

Specifies whether or not the auto-resize capability of a database managed space (DMS) table space or an automatic storage table space is to be enabled. Auto-resizable table spaces automatically increase in size when they become full.

NO: Specifies that the auto-resize capability of a DMS table space or an automatic storage table space is to be disabled. If the auto-resize capability is disabled, any values that have been previously specified for INCREASESIZE or MAXSIZE will not be kept.
YES: Specifies that the auto-resize capability of a DMS table space or an automatic storage table space is to be enabled.

INCREASESIZE integer PERCENT or INCREASESIZE integer K | M | G

Specifies the amount, per database partition, by which a table space that is enabled for auto-resize will automatically be increased when the table space is full, and a request for space has been made. The integer value must be followed by:

PERCENT to specify the amount as a percentage of the table space size at the time that a request for space is made. When PERCENT is specified, the integer value must be between 0 and 100 (SQLSTATE 42615).
K (for kilobytes), M (for megabytes), or G (for gigabytes) to specify the amount in bytes

Note that the actual value used might be slightly smaller or larger than what was specified, because the database manager strives to maintain consistent growth across containers in the table space.

MAXSIZE integer K | M | G or MAXSIZE NONE

Specifies the maximum size to which a table space that is enabled for auto-resize can automatically be increased.

integer: Specifies a hard limit on the size, per database partition, to which a DMS table space or an automatic storage table space can automatically be increased. The integer value must be followed by K (for kilobytes), M (for megabytes), or G (for gigabytes). Note that the actual value used might be slightly smaller than what was specified, because the database manager strives to maintain consistent growth across containers in the table space.
NONE: Specifies that the table space is to be allowed to grow to file system capacity, or to the maximum table space size (described in "SQL and XML limits").

CONVERT TO LARGE

Modifies an existing regular DMS table space to be a large DMS table space. The table space and its contents are locked during conversion. This option can only be used on regular DMS table spaces. If an SMS table space, a temporary table space, or the system catalog table space is specified, an error is returned (SQLSTATE 560CF). You cannot convert a table space that contains a data partition of a partitioned table that has data partitions in another table space (SQLSTATE 560CF). Conversion cannot be reversed after being committed. If tables in the table space are defined with DATA CAPTURE CHANGES, consider the storage and capacity limits of the target table and table space.

LOWER HIGH WATER MARK

For both automatic storage and non-automatic storage table spaces with reclaimable storage, triggers the extent movement operation to move the maximum number of extents lower in the table space. Although the high water mark is lowered, the size of the table space is not reduced. This must be followed by an ALTER TABLESPACE REDUCE for automatic storage table spaces or ALTER TABLESPACE REDUCE with the database-container-clause or all-containers-clause for non-automatic storage table spaces.

Note: The LOWER HIGH WATER MARK option including the STOP clause, and the REDUCE option with the MAX, numeric value, PERCENT, or STOP clauses, are only available for database managed and automatic storage managed table spaces with the reclaimable storage attribute. Moreover, these options must be specified and run without any other options, including each other.

STOP: For both automatic storage and non-automatic storage table spaces with reclaimable storage, interrupts the extent movement operation if in progress.

MANAGED BY AUTOMATIC STORAGE

Enables automatic storage for a database managed (DMS) table space. Once automatic storage is enabled, no further container operations can be executed on the table space. The table space being converted cannot be using RAW (DEVICE) containers.

Rules

The BEGIN NEW STRIPE SET clause cannot be specified in the same statement as ADD, DROP, EXTEND, REDUCE, and RESIZE, unless those clauses are being directed to different database partitions (SQLSTATE 429BC).
The stripe set value specified with the TO STRIPE SET clause must be within the valid range for the table space being altered (SQLSTATE 42615).
When adding or removing space from the table space, the following rules must be followed:
- EXTEND and RESIZE can be used in the same statement, provided that the size of each container is increasing (SQLSTATE 429BC).
- REDUCE and RESIZE can be used in the same statement, provided that the size of each container is decreasing (SQLSTATE 429BC).
- EXTEND and REDUCE cannot be used in the same statement, unless they are being directed to different database partitions (SQLSTATE 429BC).
- ADD cannot be used with REDUCE or DROP in the same statement, unless they are being directed to different database partitions (SQLSTATE 429BC).
- DROP cannot be used with EXTEND or ADD in the same statement, unless they are being directed to different database partitions (SQLSTATE 429BC).
The AUTORESIZE, INCREASESIZE, or MAXSIZE clause cannot be specified for system managed space (SMS) table spaces, temporary table spaces that were created using automatic storage, or DMS table spaces that are defined to use raw device containers (SQLSTATE 42601).
The INCREASESIZE or MAXSIZE clause cannot be specified if the table space is not auto-resizable (SQLSTATE 42601).
When specifying a new maximum size for a table space, the value must be larger than the current size on each database partition (SQLSTATE 560B0).
Container operations (ADD, EXTEND, RESIZE, DROP, or BEGIN NEW STRIPE SET) cannot be performed on automatic storage table spaces, because the database manager is controlling the space management of such table spaces (SQLSTATE 42858).
Raw device containers cannot be added to an auto-resizable DMS table space (SQLSTATE 42601).
The CONVERT TO LARGE clause cannot be specified in the same statement as any other clause (SQLSTATE 429BC).
The REBALANCE clause cannot be specified with any other clause (SQLSTATE 429BC).
The REBALANCE clause is only valid for regular and large automatic storage table spaces (SQLSTATE 42601). Temporary automatic storage table spaces should be dropped and recreated to take advantage of recently added storage paths or to have their containers removed from storage paths being dropped.
Container operations and the REBALANCE clause cannot be specified if the table space is in the "DMS rebalancer is active" state (SQLSTATE 55041).
Container size limits: In DMS table spaces, a container must be at least two times the extent size pages in length (SQLSTATE 54039). The maximum size of a container is operating system dependent.

Notes

Each container definition requires 53 bytes plus the number of bytes necessary to store the container name. The combined length of all container definitions for the table space cannot exceed 208 kilobytes (SQLSTATE 54034).
Default container operations are container operations that are specified in the ALTER TABLESPACE statement, but that are not explicitly directed to a specific database partition. These container operations are sent to any database partition that is not listed in the statement. If these default container operations are not sent to any database partition, because all database partitions are explicitly mentioned for a container operation, a warning is returned (SQLSTATE 01589).
Once space has been added or removed from a table space, and the transaction is committed, the contents of the table space may be rebalanced across the containers. Access to the table space is not restricted during 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).
Any attempts to extend, reduce, resize, or drop containers that do not exist will raise an error (SQLSTATE 428B2).
When extending, reducing, or resizing a container, the container type must match the type that was used when the container was created (SQLSTATE 428B2).
An attempt to change container sizes in the same table space, using separate ALTER TABLESPACE statements but within a single transaction, will raise an error (SQLSTATE 55041).
In a partitioned database if more than one database partition resides on the same physical node, the same device or specific path cannot be specified for such database partitions (SQLSTATE 42730). For this environment, either specify a unique container-string for each database 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.
The REDUCE, RESIZE, or DROP option attempts to free unused extents, if necessary, for DMS table spaces, and the REDUCE option attempts to free unused extents for automatic storage table spaces. The removal of unused extents allows the table space high water mark to be reduced to a value that accurately represents the amount of space used, which, in turn, enables larger reductions in table space size.
Conversion to large DMS table spaces: After conversion, it is recommended that you issue the COMMIT statement and then increase the storage capacity of the table space.
- If the table space is enabled for auto-resize, the MAXSIZE table space attribute should be increased, unless it is already set to NONE.
- If the table space is not enabled for auto-resize:
  - Enable auto-resize by issuing the ALTER TABLESPACE statement with the AUTORESIZE YES option, or
  - Add more storage by adding stripe sets, extending the size of existing containers, or both
Indexes for tables in a converted table space must be reorganized or rebuilt before they can support large record identifiers (RIDs).
- The indexes can be reorganized using the REORG INDEXES ALL command (without the CLEANUP ONLY clause). Specify the ALLOW NO ACCESS option for partitioned tables.
- Alternatively, the tables can be reorganized (not INPLACE), which will rebuild all indexes and enable the tables to support more than 255 rows per page.
To determine which tables do not yet support large RIDs, use the ADMIN_GET_TAB_INFO table function.
The rebalance of an automatic storage table space that has containers on a storage path in the "Drop Pending" state will drop those containers. New containers may need to be created to hold the data being moved off the dropped containers. There must be sufficient free space on the other storage paths in the database to allow those containers to be created, otherwise an error is returned SQLSTATE 57011. The actual amount of free space required depends on many factors, including the location of the high-water mark extent and the stripe sets being altered. However, to ensure that the operation will be successful, there should be at least enough free space on the remaining storage paths as there is space being consumed by the containers being dropped.
If the REBALANCE clause is specified but the data server determines that there is no need to create new containers or drop existing ones, a rebalance does not occur and the statement succeeds with a warning (SQLSTATE 01690).
In addition to adding containers on recently added paths, the REBALANCE operation may also be used to add containers on existing storage paths. Each stripe set in the table space is examined and storage paths that are not in use by a particular stripe set are identified. For each storage path identified, if there is sufficient free space on it then a new container will be created. The container will have the same size as the other containers in the stripe set. This would be beneficial if a given storage path ran out of space, table spaces stopped using it (by creating stripe sets on the other paths), and more storage was given to the path. In this case, no new paths have been added, but the rebalance will attempt to include that storage path in stripe sets where it wasn't included before.
Auto-resize can still occur while a rebalance of an automatic storage table space is in progress.
When a DMS table space is enabled for automatic storage by the MANAGED BY AUTOMATIC STORAGE clause, that table space will have one or more stripe sets of user-defined (non-automatic storage) containers and one or more stripe sets of automatic storage containers. Rebalancing the table space (using the REBALANCE clause) removes all of the user-defined containers. The database manager might extend existing automatic storage containers or create new automatic storage containers to hold the data being moved from the user-defined containers.
Syntax alternatives: The following are supported for compatibility with previous versions of DB2 and with other database products. These alternatives are non-standard and should not be used.
- NODE can be specified in place of DBPARTITIONNUM
- NODES can be specified in place of DBPARTITIONNUMS

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 table space TS1, then resize the containers so that all of the containers have 2000 pages. (Three different ALTER TABLESPACE statements that will accomplish this resizing are shown.)

   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 table space by 1000 pages.

   ALTER TABLESPACE DATA_TS
     EXTEND (ALL 1000)

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

   ALTER TABLESPACE INDEX_TS
     RESIZE (ALL 100 M)

Example 6: Add three new containers. Extend the first container, and resize the second.

   ALTER TABLESPACE TS0
     ADD (FILE 'cont2' 2000, FILE 'cont3' 2000)
     ADD (FILE 'cont4' 2000)
     EXTEND (FILE 'cont0' 100)
     RESIZE (FILE 'cont1' 3000)

Example 7: Table space TSO exists on database partitions 0, 1 and 2. Add a new container to database partition 0. Extend all of the containers on database partition 1. Resize a container on all database partitions other than the ones that were explicitly specified (that is, database partitions 0 and 1).

   ALTER TABLESPACE TS0
     ADD (FILE 'A' 200) ON DBPARTITIONNUM (0)
     EXTEND (ALL 200) ON DBPARTITIONNUM (1)
     RESIZE (FILE 'B' 500)

The RESIZE clause is the default container clause in this example, and will be executed on database partition 2, because other operations are being explicitly sent to database partitions 0 and 1. If, however, there had only been these two database partitions, the statement would have succeeded, but returned a warning (SQL1758W) that default containers had been specified but not used.

Example 8: Enable the auto-resize option for table space DMS_TS1, and set its maximum size to 256 megabytes.

   ALTER TABLESPACE DMS_TS1
     AUTORESIZE YES MAXSIZE 256 M

Example 9: Enable the auto-resize option for table space AUTOSTORE1, and change its growth rate to 5%.

   ALTER TABLESPACE AUTOSTORE1
     AUTORESIZE YES INCREASESIZE 5 PERCENT

Example 10: Change the growth rate for an auto-resizable table space named MY_TS to 512 kilobytes, and set its maximum size to be as large as possible.

   ALTER TABLESPACE MY_TS
     INCREASESIZE 512 K MAXSIZE NONE

Example 11: Enable automatic storage for database managed table space DMS_TS10

   ALTER TABLESPACE DMS_TS10 
     MANAGED BY AUTOMATIC STORAGE

Example 12: An ALTER DATABASE statement removed the paths /db2/filesystem1 and /db2/filesystem2 from the currently connected database. The table spaces named PRODTS1, PRODTS2, and PRODTS3 were the only table spaces using the removed paths. Rebalance these table spaces. Three ALTER TABLESPACE statements must be used.

     ALTER TABLESPACE PRODTS1 REBALANCE
     ALTER TABLESPACE PRODTS2 REBALANCE
     ALTER TABLESPACE PRODTS3 REBALANCE

Example 13: Enable automatic storage for database managed table space DATA1 and remove all of the existing non-automatic storage containers from the table space. The first statement must be committed before the second statement can be run.

   ALTER TABLESPACE DATA1 MANAGED BY AUTOMATIC STORAGE
   ALTER TABLESPACE DATA1 REBALANCE

Example 14: Trigger extent movement for an automatic storage table space with reclaimable storage attribute, to reduce the size of the containers by 10MB.

   ALTER TABLESPACE DMS_TS1 REDUCE 10 M

Example 15: Trigger extent movement for a non-automatic storage table space with reclaimable storage attribute and subsequently reduce the size of each container by 10MB.

   ALTER TABLESPACE TBSP1 LOWER HIGH WATER MARK
   ALTER TABLESPACE TBSP1 REDUCE (ALL CONTAINERS 10 M)