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.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package. If the statement is dynamically prepared, the privilege set is the union of the privilege sets that are held by each authorization ID and role of the process.

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

Notes:

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.

Start of change A table space cannot be altered if it is associated with an accelerator-only table or a directory table. End of change

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

If the table space is a partition-by-growth (UTS) table space, the page size must be valid depending on the values that are in effect for the MAXPARTITIONS and DSSIZE options of the table space. If the table space is a partition-by-range (UTS) table space, the page size must be valid depending on the values that are in effect for the current number of partitions and the DSSIZE option of the 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.

Start of 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. End of change

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.

Start of change 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. End of change

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.

For a table space in a work file database, DB2 uses CLOSE NO regardless of the value specified

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

Therefore, the DSSIZE value specifies the maximum size of a partition of a universal table space or the maximum size of any data set in a LOB table space. When DSSIZE is specified, no other options are allowed in the same ALTER TABLESPACE statement. End of change

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

Start of change 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. End of change

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

Start of change 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. End of 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.

Start of change 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. End of change

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.

Start of change 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. End of change

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 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. Start of change

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. End of change

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.

Start of change 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. End of change

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

Start of change 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. End of change

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.

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 alias¹ 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. Start of change

integer must be a positive integer, 0, or -1. End of change

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.

Start of change Use SYSTEM only for a LOB table space. End of change

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

The changes specified by these clauses affect only the identified partition.

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. End of change

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. End of change

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

Example 1: Alter table space DSN8S10D in database DSN8D10A. BP2 is the buffer pool associated with the table space. PAGE is the level at which locking is to take place.

   ALTER TABLESPACE DSN8D10A.DSN8S10D
     BUFFERPOOL BP2
     LOCKSIZE PAGE;

Example 2: Alter table space DSN8S10E in database DSN8D10A. The table space is partitioned. Indicate that the data sets of the table space are not to be closed when there are no current users of the table space. Also, change all of the partitions so that DB2 will use a formula to determine any secondary space allocations, and change partition 1 to use a PCTFREE value of 20.

   ALTER TABLESPACE DSN8D10A.DSN8S10E
     CLOSE NO
     SECQTY -1
     ALTER PARTITION 1 PCTFREE 20;

Example 3: The following statement changes the maximum number of partitions in a partition-by-growth table space:

ALTER TABLESPACE TS01DB.TS01TS
  MAXPARTITIONS 30;

¹ The alias of an integrated catalog facility catalog