ALTER TABLESPACE

The ALTER TABLESPACE statement changes the description of a table space at the current server.

Invocation for ALTER TABLESPACE

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for ALTER TABLESPACE

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
Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

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.

Notes:

¹ FL 508 If you specify DROP PENDING CHANGES, DSSIZE, SEGSIZE, PAGENUM, or MOVE TABLE, 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.
³ DSSIZE can be specified at the partition level only for a table space with relative page numbering (PAGENUM RELATIVE).
⁴ The specified DSSIZE value must be greater than or equal to the current DSSIZE value for the partition.

using-block:

free-block:

gbpcache-block:

move-table-clause:

Description for ALTER TABLESPACE

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 ALTER 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 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 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. For more information about the relationship between DSSIZE, MAXPARTITIONS, buffer pool page size and table space size, see Maximum number of partitions and table space size.

Depending on the situation when this clause is specified, Db2 might process the ALTER statement as a pending data definition change, which means the current object definition and data do not reflect the alteration at the time that the statement is issued. Instead, the altered object is placed in an advisory REORG-pending (AREOR) state, and a subsequent reorganization of the altered object with an appropriate utility materializes the changes to the catalog and data. For more information, see Pending data definition changes.

When pending definition changes are specified for the BUFFERPOOL, DSSIZE, MAXPARTITIONS, or SEGSIZE attributes of partition-by-growth (PBG) table spaces, the number of partitions is determined based on the amount of existing data at the time the pending change is applied, and partition growth can occur. 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.

Start of change For an immediate change for which 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. In addition, Db2 automatically closes required data sets. The description of the table space takes effect the next time the data sets of the table space are opened. 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.
The table space contains an archive-enabled table or an archive 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 a partition in the table space.

Start of change Do not specify COMPRESS for a table space in a work file database. End of change

YES

FL 509 Specifies that data compression is used for the table space or partition. The rows are not compressed until the LOAD or REORG utility is run on a table in the table space or partition, or until the total row data size reaches the compression data threshold while an insert operation is performed.

If a keyword for the compression algorithm is not specified, the default compression algorithm is used:

The data compression algorithm is determined by the TS_COMPRESSION_TYPE subsystem parameter.
If the table space is a LOB table space, the following requirements must also be met:
- Db2 12 must be at function level 500 or higher.
- The zEDC hardware and software must be available and configured in the z/OS® system. For more information, see Requirements for zEnterprise Data Compression.
  In data sharing, system performance can degrade dramatically if a member accesses compressed LOB data and the zEDC hardware and software are not configured in z/OS.
- The LOB table space must be associated with a base table that is in a universal table space.
- The total length of the entire LOB must be larger than the defined data page size, otherwise the LOB is not compressed.

FIXEDLENGTH

FL 509 Specifies the fixed-length data compression algorithm.

FIXEDLENGTH must not be specified for LOB table spaces. Any LOB table spaces that are implicitly created for LOB columns in this table space are defined as if COMPRESS YES had been specified without a compression algorithm. LOB compression is managed by zEnterprise® data compression (zEDC) hardware, which must be available on the z/OS system.

HUFFMAN

FL 509 Specifies the Huffman data compression algorithm. See Using Huffman compression to compress your data for requirements to enable Huffman compression.

HUFFMAN must be specified only for universal table spaces and must not be specified for table spaces that contain tables defined with ORGANIZE BY HASH.

HUFFMAN must not be specified for LOB table spaces. Any LOB table spaces that are implicitly created for LOB columns in this table space are defined as if COMPRESS YES had been specified without a compression algorithm. LOB compression is managed by zEnterprise data compression (zEDC) hardware, which must be available on the z/OS system.

NO

Specifies no data compression. Inserted rows are not compressed. Updated rows are decompressed. The dictionary used for data compression is erased when the LOAD REPLACE, LOAD RESUME NO, or REORG utility is run. For more information about the dictionary and data compression, see Compressing your data.

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.

The DROP PENDING CHANGES clause also resets advisory REORG-pending (AREOR) status except for tables that are converting to hash access.

DSSIZE integer G

Specifies the maximum data set size in gigabytes. DSSIZE can be specified only for these types of table spaces:

A partition-by-growth or partition-by-range 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

To specify a value greater than 4G, the data sets for the table space must be associated with a DFSMS data class that has been specified with extended format and extended addressability.

Start of change DSSIZE that is specified at the table space level is applied to each of the partitions of the table space. End of change

integer can be separated from G by 0 or more spaces.

The accepted values that you can specify depend on the type of table space.

Partition-by-growth

Specify a power-of-two value in the range 1–256 G.

Partition-by-range with relative page numbers

Specify any value 1–1024 G. The following values determine the DSSIZE value for each partition, in the order of precedence shown:

The DSSIZE value in the PARTITION clause for that partition.
The DSSIZE value specified at the table space level, not inside a PARTITION clause.
The default value 4G.

Partition-by-range with absolute page numbers

Specify a power-of-two value 1–256 G.

INSERT ALGORITHM level

Specifies the algorithm that is used when rows are inserted into tables in this table space. The insert algorithm level is used only where applicable (MEMBER CLUSTER UTS). Altering the insert algorithm for a table space occurs immediately.

1: Specifies the basic insert algorithm is used.
2: Specifies that insert algorithm 2 is used.

Important: The insert algorithm level can be changed only to level 1 or 2. Level 0, which is supported by CREATE TABLESPACE, is not supported by ALTER TABLESPACE.

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.

integer: Specifies the number of locks allowed before escalating, in the range 0–2147483647.
Zero (0) indicates that the number of locks on the table or table space are not counted and escalation does not occur.
SYSTEM: FL 507 Specifies that Db2 determines the maximum number of locks that a program can hold simultaneously in the table space from the SYSIBMADM.MAX_LOCKS_PER_TABLESPACE built-in global variable.
For related information see SYSIBMADM.MAX_LOCKS_PER_TABLESPACE.

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. For more information. see Altering the logging attribute of a table space.

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. For more information, see Altering the logging attribute of a table space.

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. For more information about the relationship between DSSIZE, MAXPARTITIONS, buffer pool page size and table space size, see Maximum number of partitions and table space size.

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

Start of change FL 508If MAXPARTITIONS is specified on a simple or segmented (non-UTS) table space, the table space is converted to a partition-by-growth table space that can grow to a maximum number of integer partitions. If pending definition changes to move tables from the table space exist, integer must be 1. 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. End of change

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 table space from a simple or segmented (non-UTS) table space.

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

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

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.

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.

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. For more information, see -STOP DATABASE (Db2), -START DATABASE (Db2).

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 0–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 more information, see CURRENT TEMPORAL SYSTEM_TIME special register.

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

FOR UPDATE smallint

Specifies the percentage of space to reserve as free space on each page, for use by subsequent UPDATE operations. The smallint value is an integer in the range -1 to 99. FOR UPDATE -1 specifies that 5% of free space is reserved initially, and the amount of free spaces is calculated automatically based on certain real-time statistics values. The first record on each page is always loaded without restriction.

If this value is not specified, the value of the PCTFREE_UPD subsystem parameter is used.

The value is recorded in the PCTFREE_UPD column of the SYSIBM.SYSTABLEPART catalog table.

The FOR UPDATE smallint values do not apply to LOB table spaces, XML table spaces, or table spaces that use hash organization.

The sum of the values for PCTFREE smallint and FOR UPDATE smallint must be less than or equal to 99.

If FOR UPDATE smallint is not specified and the sum of PCTFREE smallint and the PCTFREE_UPD subsystem parameter value is greater than or equal to 99, Db2 uses a smaller value for PCTFREE_UPD.

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 more information, see CURRENT TEMPORAL SYSTEM_TIME special register.

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 ALTER 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 user-managed data sets for the table space.

The data sets are VSAM linear data sets cataloged in the integrated catalog facility catalog that catalog-name identifies. For more information about catalog-name values, see Naming conventions.

More than one Db2 subsystem can share the integrated catalog facility catalogs with the current server. To avoid the chance of those subsystems attempting to assign the same name to different data sets, specify a catalog-name value that is not used by the other Db2 subsystems.

The VCAT clause must not be specified if the table space is a partition-by-growth table space.

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.
Omission of the SECQTY clause is an implicit specification of the default value.
Omission of the ERASE clause is an implicit specification of ERASE NO.

For more information, see Rules for primary and secondary space allocation.

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.

To determine how and when changes to PRIQTY take effect, see Altering storage attributes.

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.

To determine how and when changes to SECQTY take effect, see Altering storage attributes.

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.

PAGENUM

Specifies the type of page numbering that is used when you alter a partition-by-range table space.

RELATIVE: Indicates that internal page numbering is kept as a 4-byte value without a partition number. The page number is a relative page from the start of the partition, and the partition number is kept only in the header page.

The table space cannot be altered to relative page numbering if the table has truncated limit key values.

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

MOVE TABLE table-name TO TABLESPACE dbname.new-tsname

FL 508 Specifies that the table that is specified by table-name is to be moved to another table space. new-tsname specifies the target table space to which the table is to be moved, and dbname specifies the database that contains the target table space. Omission of dbname is an implicit specification of DSNDB04. The privilege set must include SYSADM or SYSCTRL authority or the USE privilege for the buffer pool and storage group that are used by the target table space.

The table that is specified by table-name must meet the following requirements:

Exist at the current server
Be defined in the source table space
Not specify a view
If the alteration is a pending change, be in a complete state
Not have an OBID value of 1

The source table space that is specified in database-name.table-space-name must meet the following requirements:

Not be a catalog or directory table space
Not be a table space in the work file database
Be a simple or segmented (non-UTS) table space

The target table space that is specified in dbname.new-tsname must meet the following requirements:

Not be a catalog or directory table space
Exist in the same database as the source table space
Be a universal partition-by-growth table space
Not already have data sets created
Be defined with MAXPARTITIONS 1
Have the same encoding scheme and CCSID triplet as the source table space
Be defined with a buffer pool that has a page size that can accommodate the record length of the moved table
Be defined with the same LOGGED attribute as the source table space

Taking either of the following actions before you run the REORG utility to apply a pending MOVE TABLE operation will cause the REORG job to fail:

Altering the target table space so that its attributes become invalid for a MOVE TABLE operation
Dropping and re-creating the target table space, regardless of whether the table space attributes are valid

For information on moving tables from deprecated table spaces, see Moving tables from multi-table table spaces to partition-by-growth table spaces.

Notes for ALTER TABLESPACE

ALTER TABLESPACE and insert operations in the same commit scope

You might encounter problems when an ALTER TABLESPACE statement is followed by an insert operation in the same commit scope. If that happens, add a COMMIT statement between the ALTER TABLESPACE and insert operations.

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, ERASE, or GBPCACHE for more than one partition, you must use separate ALTER TABLESPACE statements.

Rules for primary and secondary space allocation

You can specify the primary and secondary space allocation or let Db2 choose them. Having Db2 choose the values, especially the secondary space quantity, increases the possibility of reaching the maximum data set size before running out of extents. For more information, see Rules for primary and secondary space allocation.

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 in Tools for moving Db2 data.

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.

Increasing the size of a partitioned table space

Depending on the needs of your applications, you might need to increase the size of a partitioned table space by taking one of the following actions:

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.

Deprecated function: FL 504 Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.

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:

This statement might invalidate all packages that depend on target objects, and sometimes other related objects through cascading effects, depending on the clauses and keywords specified and other factors. For more information, see Changes that invalidate packages.

Pending data definition changes

The following table lists clauses and specific conditions that cause an ALTER TABLESPACE statement to be processed as a pending definition change. 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 applies the pending definition changes to the definition and data of the table space. The definition of the table space must not be in an incomplete state.

Clause or option	Pending definition change used if...
BUFFERPOOL	The data sets of the table space are already created, and any of the following conditions are true: Pending definition changes already exist for the table space or any objects within the base table space. The specified buffer pool has a different page size than the buffer pool that is currently being used for the table space.
DSSIZE	The data sets of the table space are already created, and any of the following conditions are true: Pending definition changes already exist for the table space or for any objects in the table space. The table space uses relative page numbering, and the DSSIZE value that is specified at the table space level is smaller than the value that is currently being used for one or more of the partitions in the table space. The table space uses absolute page numbering, and the specified DSSIZE value is different than the value that is currently being used for the table space.
MAXPARTITIONS	If the data sets of the table space are already created and the table space is not a PBG table space.
MEMBER CLUSTER	If the data sets of the table space are already created and any of the following conditions are true: Pending definition changes already exist for the table space or any objects in the table space. The MEMBER CLUSTER attribute is changed to a different value.
FL 508 MOVE TABLE	The data sets of the altered table space are already created.
PAGENUM	The change to the PAGENUM attribute 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 PAGENUM attribute is different from the value that is currently being used for the table space.
SEGSIZE	The data sets of the table space are already created, and any of the following conditions are true: Pending definition changes already exist for the definition of the table space or any objects in the table space. 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 table space.

For more information, see Pending data definition changes.

Restrictions for pending data definition changes

The following restrictions apply to ALTER TABLESPACE, ALTER TABLE, and ALTER INDEX statements that result in pending data definition changes:

Options that cause pending changes cannot be specified with options that take effect immediately.
Options that cause pending changes cannot be specified for the Db2 catalog, other system objects, or objects in a work file database.
The DROP PENDING CHANGES clause of the ALTER TABLESPACE statement cannot be specified for a catalog table space.
If the table space, or any table it contains is in an incomplete state, you cannot specify options that cause pending changes.
For ALTER INDEX, options that cause pending changes cannot be specified if the definition of the table space or table on which the index is defined is not complete.

Also, many alter operations are restricted for a table space that has existing pending data definition changes for the table space, the table it contains, or indexes on the table. For more information, see Restrictions for pending data definition changes.

Maximum number of partitions and table space size

For partition-by-range table spaces created with relative page numbering, the maximum number of partitions is 4096. End of change

For partition-by-range table spaces with absolute page numbering or partition-by growth table spaces, the following tables show how the maximum number of partitions and the total table space size depend on the buffer pool page size and DSSIZE value, with 5-byte extended addressability (EA) storage.¹

4 KB page size

Table 1. Maximum number of partitions and table space size by DSSIZE, with 4 KB page size
DSSIZE	Maximum number of partitions	Total table space size
1G	4096	4 TB
2G	4096	8 TB
4G	4096	16 TB
8G	2048	16 TB
16G	1024	16 TB
32G	512	16 TB
64G	256	16 TB
128G	128	16 TB
256G	64	16 TB

8 KB page size

Table 2. Maximum number of partitions and table space size by DSSIZE, with 8 KB page size
DSSIZE	Maximum number of partitions	Total table space size
1G	4096	4TB
2G	4096	8TB
4G	4096	16TB
8G	4096	32TB
16G	2048	32TB
32G	1024	32TB
64G	512	32TB
128G	256	32TB
256G	128	32TB

16 KB page size

Table 3. Maximum number of partitions and table space size by DSSIZE, with 16 KB page size
DSSIZE	Maximum number of partitions	Total table space size
1G	4096	4 TB
2G	4096	8 TB
4G	4096	16 TB
8G	4096	32 TB
16G	4096	64 TB
32G	2048	64 TB
64G	1024	64 TB
128G	512	64 TB
256G	256	64 TB

32 KB page size

Table 4. Maximum number of partitions and table space size by DSSIZE, with 32 KB page size
DSSIZE	Maximum number of partitions	Total table space size
1G	4096	4 TB
2G	4096	8 TB
4G	4096	16 TB
8G	4096	32 TB
16G	4096	64 TB
32G	4096	128 TB
64G	2048	128 TB
128G	1024	128 TB
256G	512	128 TB

Notes:

For 5-byte non-EA storage, the maximum values for 4 KB page size are DSSIZE 4 G, 4096 partitions, and 16 TB for total table space.

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 the PARTITION keyword is optional and if the 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 for ALTER TABLESPACE

Example 1

Alter table space DSN8S12D in database DSN8D12A. BP2 is the buffer pool associated with the table space. PAGE is the level at which locking is to take place.

   ALTER TABLESPACE DSN8D12A.DSN8S12D
     BUFFERPOOL BP2
     LOCKSIZE PAGE;

Example 2

Alter table space DSN8S12E in database DSN8D12A. 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 DSN8D12A.DSN8S12E
     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;