ALTER TABLESPACE statement

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 that are held by the authorization ID of the statement must include SYSCTRL or SYSADM authority.

Syntax

add-clause

db-container-clause

drop-container-clause

system-container-clause

on-db-partitions-clause

all-containers-clause

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 added to the table space, and that they are placed into the provided 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. Subsequently, containers that are added by using the ADD option are 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 that is specified is the size by which the existing container is decreased. If the all-containers-clause is specified, all containers in the table space decrease by this size. If the reduction in size results in a table space size that is smaller than the current high water mark, an attempt is 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 db-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 db-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 with the STOP clause, are only available for database 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.

The MAX, STOP, integer [K | M | G], or integer PERCENT clause takes effect when the statement is processed and is not rolled back if the unit of work, in which the statement is executed, is rolled back.

db-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 is reduced to the new high water mark. This reduction 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 that is 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 that is specified with K, M, or G indicates the reduction size in kilobytes, megabytes, or gigabytes. The value is first converted from bytes to number of pages based on the page size of the table space.
• An integer that is specified with PERCENT indicates the number of extents to move, as a percentage of the current size of the table space.

After 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 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 are 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).

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

Note: The SUSPEND or RESUME clause takes effect when the statement is processed and is not rolled back if the unit of work, in which the statement is executed, is rolled back.

SUSPEND

Suspends the active rebalance operation on the specified table space. If no active rebalance operation exists, no action is taken and success is returned. The suspend state is persistent and if the database is deactivated while the rebalance is suspended, then upon database activation the rebalance operation is restarted from the suspended state. Suspending a rebalance operation when it is already suspended has no effect and success is returned.

RESUME

Resumes a previously suspended rebalance operation. If no active rebalance operation exists, no action is taken and success is returned. If the rebalance is PAUSED because of an online backup operation, then the table space rebalance is taken out of the suspended state but remains paused until the online backup is completed.

PREFETCHSIZE

Specifies to read in the data needed by a query before it is referenced by the query so that the query does not need to 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 is managed by the database manager.

The database updates 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 are read from the table space when data prefetching is being performed. The maximum value is 32767.

integer K | M

Specifies the prefetch size value as an integer value followed by K (for kilobytes) or M (for megabytes). 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 that is 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 buffer pool (SQLSTATE 42735).

OVERHEAD number-of-milliseconds or OVERHEAD INHERIT

Specifies the I/O controller overhead and disk seek and latency time. This value is used to determine the cost of I/O during query optimization. For more information on tuning, refer to Table space impact on query optimization.

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.

INHERIT

If INHERIT is specified, the table space must be defined using automatic storage and the OVERHEAD is dynamically inherited from the storage group. INHERIT cannot be specified if the table space is not defined using automatic storage (SQLSTATE 42858). If the OVERHEAD is set to undefined for the storage group and you set OVERHEAD to INHERIT, the database creation default is used.

version 10.1 For a database that was created in Db2® or later, the default I/O controller overhead and disk seek and latency time is 6.725 milliseconds.

For a database that was upgraded from a previous version of Db2 to Db2 version 10.1 or later, the default I/O controller overhead and disk seek and latency time is as follows:

• 7.5 milliseconds for a database that is created in Db2 version 9.7 or higher.

TRANSFERRATE number-of-milliseconds or TRANSFERRATE INHERIT

Specifies the time to read one page into memory. This value is used to determine the cost of I/O during query optimization. For more information on tuning, refer to Table space impact on query optimization.

number-of-milliseconds

Any numeric literal (integer, decimal, or floating point) that specifies the time to read one page (4 K or 8 K) 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.

INHERIT

If INHERIT is specified, the table space must be defined using automatic storage and the TRANSFERRATE is dynamically inherited from the storage group. INHERIT cannot be specified if the table space is not defined using automatic storage (SQLSTATE 42858). If the DEVICE READ RATE of the storage group is set to undefined and the user sets TRANSFERRATE to INHERIT, the database creation default is used.

When an automatic storage table space inherits the TRANSFERRATE setting from the storage group it is using, the DEVICE READ RATE of the storage group, which is in megabytes per second, is converted into milliseconds per page read accounting for the table space's PAGESIZE setting of the table space. The conversion formula follows:

TRANSFERRATE = (1 / DEVICE READ RATE) * 1000 / 1024000 * PAGESIZE

For a database that was created in Db2 version 10.1 or later, the default time to read one page into memory for 4 KB PAGESIZE table space is 0.04 milliseconds.

For a database that was upgraded from a previous version of Db2 to Db2 version 10.1 or later, the default time to read one page into memory is as follows:

• 0.06 milliseconds for a database that is created in Db2 version 9.7 or higher

FILE SYSTEM CACHING or NO FILE SYSTEM CACHING

Specifies whether I/O operations are buffered or non-cached at the file system level. Changes to the I/O mode are not dynamic and will take effect on the next database activation. The default I/O mode is determined based on operating system, file system type, and in the case of SMS table spaces, data object type. For more information, see file system caching configurations. After a non-default file system caching option is chosen, it is not possible to return to the default (unspecified) behavior. Instead, the file system caching mode must be selected explicitly.

FILE SYSTEM CACHING

All I/O operations in the target table space are cached at the file system level.

NO FILE SYSTEM CACHING

Specifies that all I/O operations are to bypass the file system-level cache. LOB and Long field data in SMS table spaces are excepted.

Note: You must format the disk device to have a disk sector size according to the following table:

Operating system	Disk sector size
AIX®	512 bytes
Solaris	512 bytes
HP-UX	1024 bytes
Linux®	512 bytes
Windows	512 bytes

DROPPED TABLE RECOVERY

Specifies whether tables that have been dropped from tablespace-name can be recovered by 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 become accessible. If the containers are not accessible, an error is returned (SQLSTATE 57048).

AUTORESIZE

Specifies whether 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 were previously specified for INCREASESIZE or MAXSIZE are not 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 is automatically increased, in the case that the table space is full and a request for space was 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 in the range 0 - 100 (SQLSTATE 42615).
• K (for kilobytes), M (for megabytes), or G (for gigabytes) to specify the amount in bytes.

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). 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 db-container-clause or all-containers-clause for non-automatic storage table spaces.

Note: The LOWER HIGH WATER MARK option with 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.

Note: This clause takes effect when the statement is processed and is not rolled back if the unit of work, in which the statement is executed, is rolled back.

STOP

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

USING STOGROUP

Associates a table space with a different storage group. The data that is associated with the table space is moved from its current storage group to the specified storage group. This clause only applies to automatic storage table spaces unless specified with the MANAGED BY AUTOMATIC STORAGE clause (SQLSTATE 42858).

For automatic storage table spaces, an implicit REBALANCE is started at commit time. For a database-managed table space being converted to automatic storage-managed, an explicit REBALANCE statement is required.

In a partitioned database environment, to alter the storage group association of a table space, the table space must be defined by using automatic storage on all database partitions. If the table space on any database partition is not defined by using automatic storage, this command will fail unless specified with the MANAGED BY AUTOMATIC STORAGE clause (SQLSTATE 42858). However, it is not required that a table space has the same storage group association on all database partitions for this command to succeed in moving the table space on all database partitions.

storagegroup-name

Identifies the storage group in which table space data is stored. storagegroup-name must identify a storage group that exists at the current server (SQLSTATE 42704). This is a one-part name.

DATA TAG integer-constant, DATA TAG INHERIT or DATA TAG NONE

Specifies a tag for the data in the table space. This value can be used as part of a WLM configuration in a work class definition or referenced within a threshold definition. For more information, refer to the CREATE WORK CLASS SET, ALTER WORK CLASS SET, CREATE THRESHOLD, and ALTER THRESHOLD statements. This clause cannot be specified for USER or SYSTEM TEMPORARY table spaces or for the catalog table space (SQLSTATE 42858).

integer-constant

Valid values for integer-constant are integers in the range 1 - 9. If an integer-constant is specified and an associated storage group exists, the data tag that is specified for the table space overrides any data tag value that is specified for the associated storage group.

INHERIT

If INHERIT is specified, the table space must be defined using automatic storage and the DATA TAG is dynamically inherited from the storage group. INHERIT cannot be specified if the table space is not defined using automatic storage (SQLSTATE 42858).

NONE

If NONE is specified, there is no data tag.

MANAGED BY AUTOMATIC STORAGE

Enables automatic storage for a database-managed (DMS) table space. After 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.

If the USING STOGROUP clause is not included when converting from a DMS table space to an automatic storage table space, then the default storage group is specified.

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 that is 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 if the size of each container is increasing (SQLSTATE 429BC).
  • REDUCE and RESIZE can be used in the same statement if 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 re-created 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).
• The USING STOGROUP clause cannot be specified for temporary table spaces (SQLSTATE 42858).
• The following clauses are not supported in Db2 pureScale® environments:
  • ADD db-container-clause
  • BEGIN NEW STRIPE SET db-container-clause
  • DROP db-container-clause
  • REBALANCE
  • RESIZE db-container-clause
  • USING STOGROUP
• The ADD, DROP, RESIZE, EXTEND, REDUCE, LOWER HIGH WATER MARK, and BEGIN_STRIPE_SET clauses cannot be used with the MANAGED BY AUTOMATIC STORAGE clause or the USING STOGROUP clause (SQLSTATE 429BC).
• The USING STOGROUP clause cannot be specified if the table space is in the rebalancer is active state (SQLSTATE 55041).
• Container size limit: 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.
• Container definition length limit: 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).

Notes

• 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).
• After space has been added or removed from a table space, and the transaction is committed, the contents of the table space can 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 are accessible, the user can disconnect all applications and connect to the database again to bring the table space out of OFFLINE state. Alternatively, the 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 are 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 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 exists 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 that was 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, you have two choices:
    • Enable auto-resize by issuing the ALTER TABLESPACE statement with the AUTORESIZE YES option.
    • 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 rebuilt by using the REORG INDEXES ALL command with the REBUILD option. 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 might need to be created to hold the data that is 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 that is 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 is successful, there should be at least enough free space on the remaining storage paths as space is 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 can 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 sufficient free space on it exists, a new container is 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 has 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 (by 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.
• For the Db2 Developer-C Edition:
  • Altering an auto-resize table space without specifying MAXSIZE will implicitly set MAXSIZE to the remaining capacity, up to the defined storage size.
  • An attempt to resize, add, or extend the container size of all table spaces larger than the defined storage size will result in a fail.
  • Altering a table space fails if there exists a subsequent ALTER TABLESPACE that hasn’t been committed.

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

  OR

     ALTER TABLESPACE TS1
       RESIZE (ALL 2000)

  OR

     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. However, if 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 and have it use storage group sg_3.

     ALTER TABLESPACE DMS_TS10 
       MANAGED BY AUTOMATIC STORAGE
       USING STOGROUP sg_3

• Example 12: An ALTER DATABASE statement removed the paths /db/filesystem1 and /db/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, in order to reduce the size of the containers by 10 MB.

     ALTER TABLESPACE DMS_TS1 REDUCE 10 M

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

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