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.
- Modify
the DATA TAG setting for a table space.
- Alter
a DMS table space to an automatic storage table space.
- Modify
the STOGROUP setting associated with 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 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--| add-clause |------------------------------------------------------------+-+-><
+-BEGIN NEW STRIPE SET--| db-container-clause |--+-----------------------------+-+
| '-| on-db-partitions-clause |-' |
+-DROP--| drop-container-clause |--+-----------------------------+---------------+
| '-| on-db-partitions-clause |-' |
+-REDUCE--+---------------------------+--+-----------------------------+---------+
| +-| db-container-clause |---+ '-| on-db-partitions-clause |-' |
| +-| all-containers-clause |-+ |
| +-MAX-----------------------+ |
| +-STOP----------------------+ |
| '-integer--+---------+------' |
| +-K-------+ |
| +-M-------+ |
| +-G-------+ |
| '-PERCENT-' |
+-+-EXTEND-+--+-| db-container-clause |---+--+-----------------------------+-----+
| '-RESIZE-' '-| all-containers-clause |-' '-| on-db-partitions-clause |-' |
+-REBALANCE--+---------+---------------------------------------------------------+
| +-SUSPEND-+ |
| '-RESUME--' |
+-PREFETCHSIZE--+-AUTOMATIC-------+----------------------------------------------+
| +-number-of-pages-+ |
| '-integer--+-K-+--' |
| '-M-' |
+-BUFFERPOOL--bufferpool-name----------------------------------------------------+
+-OVERHEAD--+-number-of-milliseconds-+-------------------------------------------+
| '-INHERIT----------------' |
+-TRANSFERRATE--+-number-of-milliseconds-+---------------------------------------+
| '-INHERIT----------------' |
+-+-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-' |
+-USING STOGROUP--storagegroup-name----------------------------------------------+
+-DATA TAG--+-integer-constant-+-------------------------------------------------+
| +-INHERIT----------+ |
| '-NONE-------------' |
'-MANAGED BY AUTOMATIC STORAGE---------------------------------------------------'
add-clause
|--+-+--------------------------+--| db-container-clause |--+-----------------------------+-+--|
| '-TO STRIPE SET--stripeset-' '-| on-db-partitions-clause |-' |
'-| system-container-clause |--| on-db-partitions-clause |-------------------------------'
db-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 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 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.
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 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).
- 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 there is no
active rebalance operation, 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 there is no
active rebalance operation, 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 data needed by a query before 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 the database manager.
The
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 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 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 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.
- 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 will be used.
Version 10.1For 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 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.
- 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.
- 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 will be 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 DB2Version 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 created in DB2 version
9.7 or higher
- FILE SYSTEM CACHING or NO FILE SYSTEM CACHING
- Specifies whether I/O operations will be 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, filesystem type, and in the case of SMS
tablespaces, data object type. See "file system caching configurations" for further
information. Note that once a non-default file system caching option is chosen, it is not possible
to return to the default (unspecified) behaviour. Instead, the file system caching mode must be
selected explicitly.
- FILE SYSTEM CACHING
- All I/O operations in the target table space will be 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 tablespaces 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 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 db-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.
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 associated
with the table space will be 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 using automatic storage on all database partitions.
If the table space on any database partition is not defined 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 have 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 will be
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 from 1 to 9. If an integer-constant is
specified and there is an associated storage group, the data tag specified
for the table space will override any data tag value 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.
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.
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 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).
- 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
- LOWER HIGH WATER MARK
- LOWER HIGH WATER MARK STOP
- REDUCE, unless it is specified
without any of its optional elements
- RESIZE db-container-clause
- USING
STOGROUP
- The
ADD, DROP, RESIZE, EXTEND, REDUCE, LOWER HIGH WATER MARK, and BEGIN_STRIPE_SET
clauses cannot be used in conjunction 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).
- 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 rebuilt 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 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)
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. 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 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, 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)
DB2 10.5 for Linux, UNIX, and Windows