The ALTER TABLESPACE statement is used to modify an existing table space.
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).
The privileges held by the authorization ID of the statement must include SYSCTRL or SYSADM authority.
>>-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-'
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.
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.
ALTER TABLESPACE PAYROLL
ADD (DEVICE '/dev/rhdisk9' 10000)
ALTER TABLESPACE ACCOUNTING
PREFETCHSIZE 64
OVERHEAD 19.3
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)
ALTER TABLESPACE DATA_TS
EXTEND (ALL 1000)
ALTER TABLESPACE INDEX_TS
RESIZE (ALL 100 M)
ALTER TABLESPACE TS0
ADD (FILE 'cont2' 2000, FILE 'cont3' 2000)
ADD (FILE 'cont4' 2000)
EXTEND (FILE 'cont0' 100)
RESIZE (FILE 'cont1' 3000)
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. ALTER TABLESPACE DMS_TS1
AUTORESIZE YES MAXSIZE 256 M
ALTER TABLESPACE AUTOSTORE1
AUTORESIZE YES INCREASESIZE 5 PERCENT
ALTER TABLESPACE MY_TS
INCREASESIZE 512 K MAXSIZE NONE
ALTER TABLESPACE DMS_TS10
MANAGED BY AUTOMATIC STORAGE
ALTER TABLESPACE PRODTS1 REBALANCE
ALTER TABLESPACE PRODTS2 REBALANCE
ALTER TABLESPACE PRODTS3 REBALANCE
ALTER TABLESPACE DATA1 MANAGED BY AUTOMATIC STORAGE
ALTER TABLESPACE DATA1 REBALANCE
ALTER TABLESPACE DMS_TS1 REDUCE 10 M
ALTER TABLESPACE TBSP1 LOWER HIGH WATER MARK
ALTER TABLESPACE TBSP1 REDUCE (ALL CONTAINERS 10 M)