ALTER TABLESPACE
The ALTER TABLESPACE statement changes the description of a table space at the current server.
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 implicitly or explicitly specified.
Authorization
The privilege set that is defined below must include at least one of the following:
- Ownership of the table space
- DBADM authority for its database
- SYSADM or SYSCTRL authority
- System DBADM
If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.
If BUFFERPOOL or USING STOGROUP is specified, additional privileges might be required, as explained in the description of those clauses.
Syntax
.---------------------------------. V (1) | (2) >>-ALTER TABLESPACE--+----------------+-table-space-name--------+-BUFFERPOOL--bpname------+-+------> '-database-name.-' +-CCSID--ccsid-value------+ +-+-CLOSE YES-+-----------+ | '-CLOSE NO--' | +-+-COMPRESS YES-+--------+ | '-COMPRESS NO--' | +-DROP PENDING CHANGES----+ +-DSSIZE--integer--G------+ +-+-LOCKMAX SYSTEM-+------+ | '-LOCKMAXinteger-' | +-+-LOCKSIZE ANY--------+-+ | +-LOCKSIZE TABLESPACE-+ | | +-LOCKSIZE TABLE------+ | | +-LOCKSIZE PAGE-------+ | | +-LOCKSIZE ROW--------+ | | '-LOCKSIZE LOB--------' | +-+-LOGGED-----+----------+ | '-NOT LOGGED-' | +-MAXROWS--integer--------+ +-MAXPARTITIONS--integer--+ +-+-MEMBER CLUSTER YES-+--+ | '-MEMBER CLUSTER NO--' | +-SEGSIZE--integer--------+ +-+-TRACKMOD YES-+--------+ | '-TRACKMOD NO--' | +-| using-block |---------+ +-| free-block |----------+ '-| gbpcache-block |------' >--+--------------------------------------------------------------+->< | .----------------------------------------------------------. | | | .------------------------. | | | V V | (2) | | '---ALTER PARTITION--integer----+-| using-block |----+-+-----+-' +-| free-block |-----+ +-| gbpcache-block |-+ +-+-COMPRESS YES-+---+ | '-COMPRESS NO--' | '-+-TRACKMOD YES-+---' '-TRACKMOD NO--'
- If you specify DROP PENDING CHANGES, DSSIZE, or SEGSIZE, no other clauses can be specified in the same ALTER TABLESPACE statement.
- The same clause must not be specified more than one time in a single ALTER TABLESPACE statement. For example, if TRACKMOD YES is specified at the table space level, it must not also be specified after ALTER PARTITION.
using-block:
.---------------------------------------. V | >>---+-+-USING VCAT--catalog-name------+-+-+------------------->< | '-USING STOGROUP--stogroup-name-' | +-PRIQTY--integer-------------------+ +-SECQTY--integer-------------------+ '-+-ERASE YES-+---------------------' '-ERASE NO--'
free-block:
.---------------------------. V | >>---+-FREEPAGE--integer-----+-+------------------------------->< | .-PCTFREE 5---------. | '-+-PCTFREE--smallint-+-'
gbpcache-block:
>>-+-GBPCACHE CHANGED-+---------------------------------------->< +-GBPCACHE ALL-----+ +-GBPCACHE SYSTEM--+ '-GBPCACHE NONE----'
Description
- database-name.table-space-name
- Identifies the table space that is to be altered. The name must identify a table space that
exists at the current server. Omission of database-name is an implicit
specification of DSNDB04.
If you identify a partitioned table space, you can use the PARTITION clause.
A table space cannot be altered if it is associated with an accelerator-only table or a directory table.
- BUFFERPOOL bpname
- Identifies the buffer pool that
is to be used for the table space. bpname must identify an activated
buffer pool.
The privilege set must include SYSADM or SYSCTRL authority or the USE privilege for the buffer pool.
If bpname specifies a buffer pool with a smaller page size than the current page size, the maximum record size of all tables in the table space must fit in the smaller page size.
If bpname specifies a buffer pool with a different page size, the table space must be one of the following types:- A universal table space (excluding XML table spaces)
- A table space for which a pending definition change will convert the table space to a universal table space
- A LOB table space
The buffer pool change is a pending change to the definition of the table space if the data sets of the table space are already created and if one of the following conditions is true:
- Pending definition changes already exist for the table space or any associated indexes.
- The specified buffer pool has a different page size than the buffer pool that is currently being used for the table space.
Otherwise, the change is considered an immediate change.
For an immediate change where the page size of both buffer pools is the same, the table space and all data sets are immediately available. The data sets do not need to be closed and reopened for the table space to be available.
If the change is considered an immediate change, the change to the description of the table space takes effect the next time the data sets of the table space are opened. The data sets can be closed and reopened by using a STOP DATABASE command to stop the table space followed by a START DATABASE command to start the table space.
If the change is a pending change to the definition of the table space, the changes are not reflected in the definition or data at the time the ALTER TABLESPACE statement is issued. Instead, the entire table space is placed in an advisory REORG-pending state (AREOR). A subsequent reorganization of the entire table space will apply the pending definition changes to the definition and data of the table space.
When the pending page size change is applied, if the table space is a universal table space that uses partition-by-growth organization, the number of partitions is determined based on the amount of existing data and the new page size value. Changing the page size to be larger can cause automatic creation of additional partitions. If LOB columns exist, additional LOB table spaces and auxiliary objects are implicitly created for the newly-created partitions independent of whether SQLRULES (DB2®) or SQLRULES (STD) is in effect or whether the table space was explicitly or implicitly created. The new LOB objects inherit the buffer pool attribute and authorization from the existing LOB objects.
- CCSID ccsid-value
- Identifies
the CCSID value to be used for the table space. ccsid-value must
identify a CCSID value that is compatible with the current value of
the CCSID for the table space. See ALTER DATABASE for
a list that shows the CCSID to which a given CCSID can be changed
and details about changing it.
Do not specify CCSID for a LOB table space, a table space that is implicitly created for an XML column, or a table space in a work file database.
The CCSID of a table space cannot be changed if any of the following conditions are true:
- The table space contains any table that has an index that contains expressions.
- The table space contains a system-period temporal table or a history table.
- CLOSE
- When
the limit on the number of open data sets is reached, specifies the
priority in which data sets are closed.
- YES
- Eligible for closing before CLOSE NO data sets. This is the default unless the table space is in a work file database.
- NO
- Eligible for closing after all eligible CLOSE YES data sets are closed.
- COMPRESS
- Specifies
whether data compression applies to the rows of the table space or
partition.
Do not specify COMPRESS for a LOB table space or a table space in a work file database.
- YES
- Specifies data compression. The rows are not compressed until the LOAD or REORG utility is run on the table in the table space or partition, or until an insert operation is performed through the INSERT statement or the MERGE statement.
- NO
- Specifies no data compression. Inserted rows will not be compressed. Updated rows will be decompressed. The dictionary used for compression will be erased when the LOAD REPLACE, LOAD RESUME NO, or REORG utility is run. See Managing DB2 Performance for more information about the dictionary and data compression.
- DROP PENDING CHANGES
- Drops pending changes to the definition
of the table space and any objects within the table space. Pending
changes to the definition of the table space or any object within
the table space must exist.
When the DROP PENDING CHANGES clause is specified, no other options are allowed in the same ALTER TABLESPACE statement.
- DSSIZE integer G
-
Specifies the maximum size, in gigabytes. DSSIZE can be specified only for these types of table spaces:
- A universal table space
- A table space for which a pending definition change will convert the table space to a universal table space
- A LOB table space
The following are valid values for integer:
- integer value
- Meaning
- 1
- 1 gigabyte
- 2
- 2 gigabytes
- 4
- 4 gigabytes
- 8
- 8 gigabytes
- 16
- 16 gigabytes
- 32
- 32 gigabytes
- 64
- 64 gigabytes
- 128G
- 128 gigabytes
- 256G
- 256 gigabytes
If integer is greater than 4, the data sets for the table space must be associated with a DFSMS data class that has been specified with an extended format and extended addressability.
If the table space is a partition-by-growth (UTS) table space, the DSSIZE value must be valid depending on the values that are in effect for the MAXPARTITIONS option and the page size of the table space.
If the table space is a partition by range universal table space, the DSSIZE value must be valid depending on the values that are in effect for the current number of partitions and the page size of the table space.
The DSSIZE value must be valid depending on the maximum PIECESIZE of any associated non-partitioned secondary indexes.
The change to the DSSIZE is a pending change to the definition of the table space if the data sets of the table space are already created and if one of the following conditions is true:
- Pending definition changes already exist for the table space or its associated indexes.
- The specified DSSIZE value is different than the value that is currently being used for the table space.
Otherwise, the change takes effect immediately.
If the change is a pending change to the definition of the table space, the changes are not reflected in the definition or data at the time the ALTER TABLESPACE statement is issued. Instead, the entire table space is placed in an advisory REORG-pending state (AREOR). A subsequent reorganization of the entire table space will apply the pending definition changes to the definition and data of the table space.
If the table space is a partition-by-growth (UTS) table space with the pending DSSIZE change is applied, the number of partitions is determined based on the amount of existing data in the table space and the new DSSIZE value. Changing the DSSIZE value to be smaller might cause automatic growth of additional partitions. If LOB columns exist, additional LOB table spaces and auxiliary objects are implicitly created for the newly-grown partitions independently of whether SQLRULES(DB2) or SQLRULES(STD) is in effect or whether the table space was explicitly or implicitly created. The new LOB objects inherit the buffer pool attribute and authorization from the existing LOB objects.
- LOCKMAX
- Specifies the maximum number of
page, row, or LOB locks an application process can hold simultaneously in the table space. If a
program requests more than that number, locks are escalated. The page, row, or LOB locks are
released and the intent lock on the table space or segmented (non-UTS) table is promoted to S or X
mode. If you specify LOCKMAX a for table space in a work file database, DB2 ignores the value because these types of locks are not used.
For an application that uses Sysplex query parallelism, a lock count is maintained on each member.
- integer
- Specifies the number of locks allowed before escalating, in the range 0 to 2 147 483 647.
Zero (0) indicates that the number of locks on the table or table space are not counted and escalation does not occur.
- SYSTEM
- Indicates that the value of field LOCKS PER TABLE(SPACE) on installation panel DSNTIPJ specifies the maximum number of page, row, or LOB locks a program can hold simultaneously in the table or table space.
If you change LOCKSIZE and omit LOCKMAX, the following results occur:LOCKSIZE Resultant LOCKMAX TABLESPACE or TABLE 0 PAGE, ROW, or LOB Unchanged ANY SYSTEM If the lock size is TABLESPACE or TABLE, LOCKMAX must be omitted, or its operand must be 0.
- LOCKSIZE
- Specifies the size of
locks used within the table space and, in some cases, also the threshold
at which lock escalation occurs. Do not specify LOCKSIZE for a table
space in a work file database.
- ANY
- Specifies that DB2 can use
any lock size.
In most cases, DB2 uses LOCKSIZE PAGE LOCKMAX SYSTEM for non-LOB table spaces and LOCKSIZE LOB LOCKMAX SYSTEM for LOB table spaces. However, when the number of locks acquired for the table space exceeds the maximum number of locks allowed for a table space (an installation parameter), the page or LOB locks are released and locking is set at the next higher level. If the table space is segmented, the next higher level is the table. If the table space is not segmented, the next higher level is the table space.
- TABLESPACE
- Specifies table space locks.
- TABLE
- Specifies table locks. Use TABLE only for a segmented (non-UTS) table space. Do not use TABLE for a universal table space.
- PAGE
- Specifies page locks. Do not use PAGE for a LOB table space.
- ROW
- Specifies row locks. Do not use ROW for a LOB table space.
- LOB
- Specifies LOB locks. Use LOB only for a LOB table space.
The LOCKSIZE change affects a dynamic SQL statement if the statement is prepared and executed after the change. The LOCKSIZE change affects a static SQL statement if the statement is executed after the change.
- LOGGED or NOT LOGGED
- Specifies
whether changes that are made to the data in the specified table space
are recorded in the log.
- LOGGED
- Specifies that changes that are made to the data in the specified
table space are recorded in the log. This applies to all tables in
the specified table space and to all indexes of those tables. Table
spaces and indexes that are created for XML columns inherit the logging
attribute from the associated base table space. Auxiliary indexes
inherit the logging attribute from the associated base table space.
This can affect the logging attribute of associated LOB table spaces.
See Notes for more
information.
If the base table space is in informational copy-pending status (meaning updates have been made to the table space) when you change from NOT LOGGED to LOGGED, the base table space is placed in copy-pending status. All indexes of tables in the table space are unchanged from their current state; that is, if an index is currently in informational copy-pending status, it will remain in information copy-pending status.
Specifying LOGGED for a LOB table space requires that the base table space also specifies the LOGGED parameter.
LOGGED cannot be specified for XML table spaces. The logging attribute of an XML table space is inherited from its base table space.
LOGGED cannot be specified for table spaces in DSNDB06 (the DB2 catalog) or in a work file database.
- NOT LOGGED
- Specifies that changes that are made to data in the specified
table space are not recorded in the log. This applies to all tables
in the specified table space and to all indexes of those tables. Table
spaces and indexes that are created for XML columns inherit the logging
attribute from the associated base table space. Auxiliary indexes
inherit the logging attribute from the associated base table space.
This parameter can affect the logging attribute of associated LOB
table spaces. See Notes for
more information.
NOT LOGGED prevents undo and redo information from being recorded in the log for the base table space; however, control information for the specified base table space will continue to be recorded in the log. For a LOB table space, changes to system pages and to auxiliary indexes are logged.
NOT LOGGED is mutually exclusive with the DATA CAPTURE CHANGES parameter of CREATE TABLE and ALTER TABLE. NOT LOGGED will not be applied to the table space if any table in the table space specifies DATA CAPTURE CHANGES.
NOT LOGGED cannot be specified for XML table spaces.
NOT LOGGED cannot be specified for table spaces in the following databases:
- DSNDB06 (the DB2 catalog)
- a work file database
- MAXROWS integer
- Specifies
the maximum number of rows that DB2 will
consider placing on each data page. The integer can range from 1 through
255.
The change takes effect immediately for new rows added. However, the space class settings for some pages might be incorrect and could cause unproductive page visits. It is highly recommended to reorganize the table space after altering MAXROWS.
After ALTER TABLESPACE with MAXROWS is run, the table space is placed into an advisory REORG-pending status. Run the REORG TABLESPACE utility to remove the status.
Do not specify MAXROWS for a LOB table space, a table space that is implicitly created for an XML column, a table space in a work file database, or the DB2 catalog table spaces that are listed under SQL statements allowed on the catalog.
- MAXPARTITIONS integer
- Specifies that the table space
is partition-by-growth. integer specifies the maximum number of partitions
to which the table space can grow or shrink. integer must be in the range
of 1 to 4096, depending on the value that is in effect for DSSIZE and the page size of the table
space, and must not be less than the number of physical partitions that are already allocated for
the table space. See CREATE TABLESPACE for
more information about how DSSIZE and the page size are related.
MAXPARTITIONS can be specified only for a simple table space that contains only one table, a segmented (non-UTS) table space that contains only one table, or a partitioned-by-growth (UTS) table space. The table space must have DB2-managed data sets.
Although physical data sets are not defined when the MAXPARTITIONS value is issued, there can be storage and cpu overhead. If an increase in the number of partitions is expected by using the MAXPARTITONS clause, be aware that specifying an value larger than necessary, such as 4096 (the maximum value), as a default for all of your partition-by-growth table spaces can cause larger than expected storage requests.
The change to the value of MAXPARTITIONS is a pending change to the definition of the table space if the data sets of the table space are already created and one of the following conditions is true:
- Pending changes to the definition of the table space or associated indexes already exist.
- The table space is converted from a simple table space to a partition-by-growth (UTS) table space.
- The table space is converted from a segmented (non-UTS) table space to a partition-by-growth (UTS) table space.
Otherwise, the change is an immediate change.
If the change is a pending change to the definition of the table space, the changes are not reflected in the definition or data at the time the ALTER TABLESPACE statement is issued. Instead, the entire table space is placed in an advisory REORG-pending state (AREOR). A subsequent reorganization of the entire table space will apply the pending definition changes to the definition and data of the table space.
If MAXPARTITIONS is specified on a simple or segmented (non-UTS) table space, the table space is converted to a partition-by-growth (UTS) table space that can grow to a maximum number of integer partitions. The SEGSIZE is set to the default of 32 if the SEGSIZE prior to conversion is less than 32. Otherwise, the value of SEGSIZE is inherited from the original table space. The DSSIZE is set to the default 4 gigabytes.
If the data sets of the table space are not defined, the number of partitions is set to 1 during the conversion to a partition-by-growth (UTS) table space from a simple or segmented (non-UTS) table space.
If the data sets of the table space are created, the number of partitions is determined based on amount of existing data at the time the pending change to the definition of the table space is applied. Partition growth can happen. If LOB columns exist, additional LOB table spaces and auxiliary objects are implicitly created for the newly-grown partitions, regardless of whether SQLRULES(DB2) or SQLRULES(STD) is in effect or whether the table space was explicitly or implicitly created. The new LOB objects inherit the buffer pool attribute and thereby authorization from the existing LOB objects.
If the table space is defined with LOCKSIZE TABLE, the lock size will be reset to LOCKSIZE TABLESPACE during conversion to a partition-by-growth (UTS) table space.
- MEMBER CLUSTER YES or MEMBER CLUSTER NO
- Specifies whether the table space uses the MEMBER CLUSTER page set structure. The
MEMBER CLUSTER clause can be specified only for a partition-by-growth or partition-by-range (UTS)
table space, or for a table space for which a pending definition change will convert the table space
to a universal table space. The change to MEMBER CLUSTER is a pending change to the definition of the table space if the data sets are already created and if one of the following conditions is true:
- Pending definition changes already exist for the table space or any associated indexes.
- The specified MEMBER CLUSTER value is different than the value that is currently being used for the table space.
If the change is a pending change to the definition of the table space, the table is placed in advisory REORG-pending state (AREOR). Running a utility like REORG with SHRLEVEL(CHANGE) or SHRLEVEL(REFERENCE) on the entire table space resets this state.
- MEMBER CLUSTER YES
- Specifies that the MEMBER CLUSTER page set structure is to be used for the specified table space
when the table space is already defined as a partition-by-growth or partition-by-range (UTS) table
space.
MEMBER CLUSTER YES cannot be specified for LOB, workfile, or XML table spaces, or for table spaces that are organized for hash access.
- MEMBER CLUSTER NO
- Specifies that the table space does not use the MEMBER CLUSTER page set structure when the table
space is already defined as a partition-by-growth or partition-by-range (UTS) table space. If the
universal table space is already defined to use the MEMBER CLUSTER page set structure, specifying
MEMBER CLUSTER NO on the ALTER TABLESPACE statement removes the MEMBER
CLUSTER page set structure from the table space.
MEMBER CLUSTER NO is the default.
- SEGSIZE integer
- Specifies
that the table space is a universal table space, where integer specifies
the number of pages that are to be assigned to each segment of the table space.
integer must be a multiple of 4 between 4 and 64 (inclusive). When SEGSIZE
is specified, no other options are allowed in the same ALTER TABLESPACE statement.
SEGSIZE can be specified only for a universal table space or a partitioned table space that uses table-controlled partitioning.
The change to the value of SEGSIZE is a pending change to the definition of the table space if the data sets of the table space are already created and one of the following conditions is true:
- Pending definition changes already exist for the table space or any of its associated indexes.
- The specified SEGSIZE value for a universal table space is different than the existing value.
- The table space is converted from a partitioned (non-UTS) table space to a partition-by-range (UTS) table space.
Otherwise, the change is an immediate change.
If the change is a pending change to the definition of the table space, the changes are not reflected in the definition or data at the time the ALTER TABLESPACE statement is issued. Instead, the entire table space is placed in an advisory REORG-pending state (AREOR). A subsequent reorganization of the entire table space will apply the pending definition changes to the definition and data of the table space.
If the existing FREEPAGE value (the number of pages to be left free) is greater than or equal to the new SEGSIZE value, the number of pages is adjusted to be one less than the new SEGSIZE value.
If the table space is a partitioned table space, the partitioned table space is converted to a partition-by-range (UTS) table space with a segment size specified by integer. The MEMBER CLUSTER attribute is inherited from the original table space. The number of partitions is inherited from the original table space. If the original DSSIZE attribute has a value of 0, the DSSIZE is set to the original maximum partition size. Otherwise, the DSSIZE attribute is inherited from the original table space.
If the table space is a partition-by-growth (UTS) table space when the pending SEGSIZE change is applied, the number of partitions is determined based on the amount of existing data in the table space and the new SEGSIZE value. Changing the SEGSIZE value to be smaller might cause automatic growth of additional partitions. If LOB columns exist, additional LOB table spaces and auxiliary objects are implicitly created for the newly-grown partitions independently of whether SQLRULES(DB2) or SQLRULES(STD) is in effect or whether the table space was explicitly or implicitly created. The new LOB objects inherit the buffer pool attribute and authorization from the existing LOB objects.
- TRACKMOD
- Specifies
whether DB2 tracks modified
pages in the space map pages of the table space or partition. Do not
specify TRACKMOD for a LOB table space or a table space in a work
file database.
For the changed TRACKMOD option to take effect, the table space or partition needs to be stopped and restarted. The table space or partition can be stopped and restarted by running the STOP DATABASE command followed by the START DATABASE command, or by running the REORG utility on the table space or partition. See -STOP DATABASE (DB2) and -START DATABASE (DB2) or REORG TABLESPACE for information.
- YES
- DB2 tracks changed pages in the space map pages to improve the performance of incremental image copy. For data sharing, changing TRACKMOD to YES causes additional SCA (shared communication area) storage to be used until after the next full or incremental image copy is taken or until TRACKMOD is set back to NO.
- NO
- DB2 does not track changed pages in the space map pages. It uses the LRSN value in each page to determine whether a page has been changed.
- FREEPAGE integer
- Specifies
how often to leave a page of free space when the table space is loaded
or reorganized. One free page is left after every integer pages; integer can
range from 0 to 255. FREEPAGE 0 leaves no free pages. Do not specify
FREEPAGE for a LOB table space, a table space that is implicitly created for an XML
column, or a table space in a work file database.
If the table space is segmented, the number of pages left free must be less than the SEGSIZE value. If the number of pages to be left free is greater than or equal to the SEGSIZE value, then the number of pages is adjusted downward to one less than the SEGSIZE value.
This change to the description of the table space or partition has no effect until data in the table space or partition is loaded or reorganized. For XML table spaces, this change has no effect until data in the table space is reorganized.
Related information: - PCTFREE smallint
- Specifies
what percentage of each page to leave as free space when the table
space is loaded or reorganized. The default value is PCTFREE 5, which
specifies that 5% of the space on each data page is reserved as free
space. The first record on each page is loaded without restriction.
When additional records are loaded, at least integer percent
of free space is left on each page. integer can
range from 0 to 99. Do
not specify PCTFREE for a LOB table space, a table space that is implicitly
created for an XML column, or a table space in a work file database.
This change to the description of the table space or partition has no effect until data in the table space or partition is loaded or reorganized. For XML table spaces, this change has no effect until data in the table space is reorganized.
Related information: - USING
- Specifies whether a data set for the table space or partition is
managed by the user or is managed by the DB2 system. If the
table space is partitioned, USING applies to the data set for the partition that is identified in
the PARTITION clause. If the table space is a partition-by-growth table space, USING can be
specified only at the table space level. If the table space is not partitioned, USING applies to
every data set that is eligible for the table space. (A nonpartitioned table space can have more
than one data set if PRIQTY+118
×
SECQTY
is at least 2 gigabytes.) If the USING clause is specified, the table space or partition must be in the stopped state when the ALTER TABLESPACE statement is executed. See Altering storage attributes to determine how and when changes take effect. Do not specify the USING clause if the table space is in a work file database.
- VCAT catalog-name
- Specifies a
user-managed data set with a name that starts with catalog-name. The VCAT clause must not be specified if the table space is a partition-by-growth table
space. You must specify the catalog name in the form of an SQL identifier. You must specify an
alias1 if the name of the integrated
catalog facility catalog is longer than eight characters. When the new description of the table
space is applied, the integrated catalog facility catalog must contain an entry for the data set
that conforms to the DB2 naming conventions set forth in
DB2 Administration Guide.
One or more DB2 subsystems could share integrated catalog facility catalogs with the current server. To avoid the chance of having one of those subsystems attempt to assign the same name to different data sets, select a value for catalog-name that is not used by the other DB2 subsystems.
- STOGROUP stogroup-name
- Specifies
a DB2-managed data set that resides on a volume of the
identified storage group. stogroup-name must identify a storage group that
exists at the current server and the privilege set must include SYSADM authority, SYSCTRL authority,
or the USE privilege for the storage group. When the new description of the table space is applied,
the description of the storage group must include at least one volume serial number, each volume
serial number must identify a volume that is accessible to z/OS® for dynamic allocation of the data set, and all identified volumes must be of the same
device type. Furthermore, the integrated catalog facility catalog used for the storage group must
not contain an entry for the data set.
If you specify USING STOGROUP and the current data set for the table space or partition is managed by DB2:
- Omission of the PRIQTY clause is an implicit specification of the current PRIQTY value.
- Omission of the SECQTY clause is an implicit specification of the current SECQTY value.
- Omission of the ERASE clause is an implicit specification of the current ERASE rule.
If you specify USING STOGROUP to convert from user-managed data sets to DB2-managed data sets:
- Omission of the PRIQTY clause is an implicit specification of the default value. For information on how DB2 determines the default value, see Rules for primary and secondary space allocation.
- Omission of the SECQTY clause is an implicit specification of the default value. For information on how DB2 determines the default value, see Rules for primary and secondary space allocation.
- Omission of the ERASE clause is an implicit specification of ERASE NO.
- PRIQTY integer
- Specifies the minimum primary space allocation for a DB2-managed data set of the table space or partition. integer must be a positive integer,
or -1. This clause can be specified only if the data set is managed
by DB2, and if one of the following
is true:
- USING STOGROUP is specified.
- A USING clause is not specified.
In general, when you specify PRIQTY with a positive integer value, the primary space allocation is at least n kilobytes, where n is the value of integer. However, the following exceptions exist:
For non-LOB table spaces, the exceptions are:
- For 4KB page sizes, if integer is greater than 0 and less than 12, n is 12.
- For 8KB page sizes, if integer is greater than 0 and less than 24, n is 24.
- For 16KB page sizes, if integer is greater than 0 and less than 48, n is 48.
- For 32KB page sizes, if integer is greater than 0 and less than 96, n is 96.
- For any page size, if integer is greater than 67108864, n is 67108864.
For LOB table spaces, the exceptions are:
- For 4KB page sizes, if integer is greater than 0 and less than 200, n is 200.
- For 8KB page sizes, if integer is greater than 0 and less than 400, n is 400.
- For 16KB page sizes, if integer is greater than 0 and less than 800, n is 800.
- For 32KB page sizes, if integer is greater than 0 and less than 1600, n is 1600.
- For any page size, if integer is greater than 67108864, n is 67108864.
The maximum value allowed for PRIQTY is 64GB (67108864 kilobytes).
If you specify PRIQTY with a value of -1, DB2 uses a default value for the primary space allocation. For information on how DB2 determines the default value for primary space allocation, see Rules for primary and secondary space allocation.
If PRIQTY is omitted and USING STOGROUP is specified, the value of PRIQTY is its current value. (However, if the current data set is being changed from being user-managed to DB2-managed, the value is its default value. See the description of USING STOGROUP.)
If you specify PRIQTY, and do not specify a value of -1, DB2 specifies the primary space allocation to access method services using the smallest multiple of p KB not less than n, where p is the page size of the table space. The allocated space can be greater than the amount of space requested by DB2. For example, it could be the smallest number of tracks that will accommodate the request. To more closely estimate the actual amount of storage, see DEFINE CLUSTER command.
At least one of the volumes of the identified storage group must have enough available space for the primary quantity. Otherwise, the primary space allocation will fail.
See Altering storage attributes to determine how and when changes to PRIQTY take effect.
- SECQTY integer
- Specifies the minimum secondary space allocation for a DB2-managed data set of the table space or partition. integer must
be a positive integer, 0, or -1. This clause can be specified
only if the data set is managed by DB2,
and if one of the following is true:
- USING STOGROUP is specified.
- A USING clause is not specified.
If you specify SECQTY with a value of -1, DB2 uses a default value for the secondary space allocation.
If USING STOGROUP is specified and SECQTY is omitted, the value of SECQTY is its current value. (However, if the current data set is being changed from being user-managed to DB2-managed, the value is its default value. See the description of USING STOGROUP.)
For information on the actual value that is used for secondary space allocation, whether you specify a value or DB2 uses a default value, see Rules for primary and secondary space allocation.
If you specify SECQTY, and do not specify a value of -1, DB2 specifies the secondary space allocation to access method services using the smallest multiple of p KB not less than integer, where p is the page size of the table space. The allocated space can be greater than the amount of space requested by DB2. For example, it could be the smallest number of tracks that will accommodate the request. To more closely estimate the actual amount of storage, see the description of the DEFINE CLUSTER command for z/OS DFSMS Access Method Services for catalogs.
See Altering storage attributes to determine how and when changes to SECQTY take effect.
- ERASE
- Indicates whether the DB2-managed data sets for the table space or partition are to be erased before they are deleted during the execution of a utility or an SQL statement that drops the table space.
- NO
- Does not erase the data sets. Operations involving data set deletion will perform better than ERASE YES. However, the data is still accessible, though not through DB2.
- YES
- Erases the data sets. As a security measure, DB2 overwrites all data in the data sets with zeros before they are deleted.
This clause can be specified only if the data set is managed by DB2, and if one of the following is true:
- USING STOGROUP is specified.
- A USING clause is not specified.
If you specify ERASE, the table space or partition must be in the stopped state when the ALTER TABLESPACE statement is executed. If you specify ERASE for a partitioned table space, you must also specify the ALTER PARTITION clause. See Altering storage attributes to determine how and when changes take effect.
- GBPCACHE
- In
a data sharing environment, specifies what pages of the table space
or partition are written to the group buffer pool in a data sharing
environment. In a non-data-sharing environment, you can specify GBPCACHE
for a table space other than one in a work file database, but it is
ignored. Do not specify GBPCACHE for a table space in a work file
database in either environment (data sharing or not). In addition,
you cannot alter the GBPCACHE value of some DB2 catalog table spaces; for a list of these
table spaces, see SQL statements allowed on the catalog.
- CHANGED
- When there is inter-DB2 R/W interest on the table space or partition, updated pages are written to the group buffer pool. When there is no inter-DB2 R/W interest, the group buffer pool is not used. Inter-DB2 R/W interest exists when more than one member in the data sharing group has the table space or partition open, and at least one member has it open for update.
If the table space is in a group buffer pool that is defined to be used only for cross-invalidation (GBPCACHE NO), CHANGED is ignored and no pages are cached to the group buffer pool.
- ALL
- Indicates that pages are to be cached in the group buffer pool
as they are read in from DASD.
Exception: In the case of a single updating DB2 when no other DB2 subsystems have any interest in the page set, no pages are cached in the group buffer pool.
If the table space is in a group buffer pool that is defined to be used only for cross-invalidation (GBPCACHE NO), ALL is ignored and no pages are cached to the group buffer pool.
- SYSTEM
- Indicates that only changed system pages within the LOB table
space are to be cached to the group buffer pool. A system page is
a space map page or any other page that does not contain actual data
values.
Use SYSTEM only for a LOB table space.
- NONE
- Indicates that no pages are to be cached to the group buffer pool. DB2 uses the group buffer pool only
for cross-invalidation.
If you specify NONE, the table space or partition must not be in recover pending status when the ALTER TABLESPACE statement is executed.
If you specify GBPCACHE in a data sharing environment, the table space or partition must be in the stopped state when the ALTER TABLESPACE statement is executed.
- ALTER PARTITION integer
- Specifies that the
identified partition of the table space is to be changed. For
a table space that has n partitions, you must specify an integer
in the range 1 to n. You must not use this clause for a nonpartitioned
table space, for a LOB table space, or a partition-by-growth
table space. At least one of the following clauses must be specified:
- COMPRESS
- ERASE
- FREEPAGE
- GBPCACHE
- PCTFREE
- PRIQTY
- SECQTY
- TRACKMOD
- USING
Do not specify the following clauses for ALTER PARTITION for partitions of a table space that is implicitly created for an XML column.
- CCSID
- FREEPAGE
- MAXROWS
- PCTFREE
Notes
- ALTER TABLESPACE and INSERT statements in the same commit scope:
- You might encounter problems when an ALTER TABLESPACE statement is followed by an INSERT statement in the same commit scope. If that happens, add a COMMIT statement between the ALTER TABLESPACE and INSERT statements.
- Running utilities:
- You cannot execute the ALTER TABLESPACE statement while a DB2 utility has control of the table space.
- Altering more than one partition:
- To change FREEPAGE, PCTFREE, USING, PRIQTY, SECQTY, COMPRESS, ERASE, or GBPCACHE for more than one partition, you must use separate ALTER TABLESPACE statements.
- Altering storage attributes:
- The USING, PRIQTY, SECQTY, and ERASE clauses define the storage
attributes of the table space or partition. If you specify USING or
ERASE when altering storage attributes, the table space or partition
must be in the stopped state when the ALTER TABLESPACE statement is
executed. You can use a STOP DATABASE…SPACENAM… command
to stop the table space or partition.
If the catalog name changes, the changes take effect after you move the data and start the table space or partition using the START DATABASE…SPACENAM… command. The catalog name can be implicitly or explicitly changed by the ALTER TABLESPACE statement. The catalog name also changes when you move the data to a different device. See the procedures for moving data in DB2 Administration Guide.
Changes to the secondary space allocation (SECQTY) take effect the next time DB2 extends the data set; however, the new value is not reflected in the integrated catalog until you use the REORG, RECOVER, or LOAD REPLACE utility on the table space or partition. The changes to the other storage attributes take effect the next time the page set is reset. For a non-LOB table space, the page set is reset when you use the REORG, RECOVER, or LOAD REPLACE utilities on the table space or partition. For a LOB table space, the page set is reset when RECOVER is run on the LOB table space or LOAD REPLACE is run on its associated base table space. If there is not enough storage to satisfy the primary space allocation, a REORG might fail. If you change the primary space allocation parameters or erase rule, you can have the changes take effect earlier if you move the data before you start the table space or partition.
- Recommended GBPCACHE setting for LOB table spaces:
- For LOB table spaces, use the GBPCACHE CHANGED option instead of the GBPCACHE SYSTEM option. Due to the usage patterns of LOBs, the use of GBPCACHE CHANGED can help avoid excessive and synchronous writes to disk and the group buffer pool.
- Altering table spaces for tables that use hash organization:
- Certain attributes of the table space, such as buffer pool and page size, might affect performance of tables that use hash organization. Changes related to the hash organization of a table will be validated and might generate error messages as described in CREATE TABLE and ALTER TABLE.
- Altering the logging attribute of a table space:
- If the logging attribute (specified with the LOGGED or NOT LOGGED
parameter) of a table space is altered frequently, the size of SYSIBM.SYSCOPY
might need to be increased.
The logging attribute of the table space cannot be altered if the table space has been updated in the same unit of recovery.
A full image copy of the table space should be taken:- Before altering a table space to NOT LOGGED
- After altering a table space to LOGGED
If a table space has data changes after an image copy is taken (the table space is in informational COPY-pending state), and the table space is altered from NOT LOGGED to LOGGED, the table space is marked COPY-pending and a full image copy of the table space must be taken.
An XML table space with the LOGGED logging attribute has its logging attribute altered to NOT LOGGED when the logging attribute of the associated base table space is altered from LOGGED to NOT LOGGED. When this happens, the logging attribute of the XML table space is said to be linked to the logging attribute of the base table space. When the logging attribute of the base table space is altered back to LOGGED, all logging attributes that are linked for the associated XML table spaces are altered back to LOGGED, and all of these links are dissolved.
A LOB table space with the LOGGED logging attribute has its logging attribute altered to NOT LOGGED when the logging attribute of the associated base table space is altered from LOGGED to NOT LOGGED. When this happens, the logging attribute of the LOB table space is said to be linked to the logging attribute of the base table space. When the logging attribute of the base table space is altered back to LOGGED, all logging attributes that are linked for the associated LOB table spaces are altered back to LOGGED, and all of these links are dissolved.
You can dissolve the link between these logging attributes by altering the logging attribute of the LOB table space to NOT LOGGED, even though it has already been implicitly given this logging attribute. After such an alter, the logging attribute of the LOB table space is unaffected when the logging attribute of the base table is altered back to LOGGED. A LOB table space with the NOT LOGGED logging attribute does not have this attribute changed in any way if the logging attribute of the associated base table space is altered from LOGGED to NOT LOGGED. When altered in this way, the logging attributes of the LOB table space and the base table space are not linked. If the base table space is altered back to LOGGED, the logging attribute of any LOB table spaces that are not linked to the logging attribute of the base table space remain unchanged.
- Altering table spaces for DB2 catalog tables:
- For details on altering options on catalog tables, see SQL statements allowed on the catalog.
- Invalidation of packages:
- All of the packages that refer to that table space are invalidated
when any of the following conditions are true:
- The SBCS CCSID attribute of a table space is changed.
- When increasing the MAXPARTITIONS attribute of a table space.
- The SEGSIZE attribute of a partitioned table space is changed to convert the table space to a partition-by-range (UTS) table space.
- The DSSIZE attribute of a partitioned table space is changed
- Pending changes to the definition of a table space:
- Issuing the ALTER TABLESPACE statement with certain options can
cause a pending change to the definition of a table space. When an
ALTER TABLESPACE statement that causes pending changes to the definition
is executed, semantic validation and authorization checking are performed.
However, changes to the table space definition and data are not applied
and the table space is placed in advisory REORG-pending state (AREOR).
The pending changes are recorded in the SYSIBM.SYSPENDINGDDL catalog
table. The REORG utility that specifies SHRLEVEL CHANGE or REFERENCE
should be run on the table space to apply the pending changes to the
definition and data of the table space. When the pending changes are
applied, dependent packages are invalidated, the corresponding entries
in the SYSIBM.SYSPENDINGDDL catalog table are removed, and the advisory
REORG-pending state is removed.The following ALTER TABLESPACE options can cause pending changes to the definition of the table space under certain conditions:
- BUFFERPOOL
- DSSIZE
- MAXPARTITIONS
- MEMBER CLUSTER
- SEGSIZE
The changes that are caused by all other options occur when the ALTER TABLESPACE statement is executed.
- Restrictions on ALTER TABLESPACE statements that cause pending changes:
- ALTER TABLESPACE statements that cause pending changes have the
following restrictions:
- Options that cause pending changes cannot be specified with options that take effect immediately
- Options that cause pending changes cannot be specified for the
following objects:
- The catalog
- System objects
- Objects in a workfile database
- The DROP PENDING CHANGES clause cannot be specified for a catalog table space
- If the DROP PENDING CHANGES clause is specified, no other clauses can be specified on the ALTER TABLESPACE statement
- If there are pending changes to the table space, you cannot use ALTER TABLESPACE to change from a DB2-managed data set to a user-managed data set
- If there are pending changes to the table space, you cannot specify
the following clauses:
- FREEPAGE
- ALTER PARTITION FREEPAGE
- CCSID
- If the table space, or any table it contains is in an incomplete state, you cannot specify options that cause pending changes
- Alternative syntax and synonyms:
- For compatibility with previous releases of DB2, the following keywords are supported:
- You can specify the LOCKPART clause, but it has no effect. DB2 treats all partitioned table spaces as if they were defined as LOCKPART YES. LOCKPART YES specifies the use of selective partition locking. When all the conditions for selective partition locking are met, DB2 locks only the partitions that are accessed. When the conditions for selective partition locking are not met, DB2 locks every partition of the table space.
- When altering the partitions of a partitioned table space, the ALTER keyword that precedes PARTITION keyword is optional and if ALTER keyword is omitted, then you can specify PART as a synonym for PARTITION.
- You can specify LOG YES as a synonym for LOGGED and LOG NO as a synonym for NOT LOGGED.
Examples
ALTER TABLESPACE DSN8D10A.DSN8S10D
BUFFERPOOL BP2
LOCKSIZE PAGE;
ALTER TABLESPACE DSN8D10A.DSN8S10E
CLOSE NO
SECQTY -1
ALTER PARTITION 1 PCTFREE 20;
ALTER TABLESPACE TS01DB.TS01TS
MAXPARTITIONS 30;