To execute the ALTER TABLESPACE command, users must possess either DBADM authority for the database, overall SYSADM or SYSCTRL authority for the location, or be the owner of the table space affected.
Bold text indicates clauses or options that are supported only syntactically.
ALTER TABLESPACE [database-name.]tablespace-name
[BUFFERPOOL bpname]
[CLOSE {YES | NO}]
[DSETPASS password]
[PART integer]
[FREEPAGE integer]
[PCTFREE integer]
[USING {VCAT catalog-name | STOGROUP stogroup-name}]
[PRIQTY integer]
[SECQTY integer]
[ERASE {YES | NO}]
[COMPRESS {NO | YES}]
[GBPCACHE {CHANGED | ALL | SYSTEM | NONE}]
[LOCKMAX {SYSTEM | integer}]
[LOCKSIZE {ANY | TABLESPACE | TABLE | PAGE | ROW | LOB}]
[TRACKMOD {YES | NO}]
[LOG {YES | NO}]
[LOCKPART {NO | YES}]
[MAXROWS integer]
| database-name.tablespace-name | Identifies the table space to be altered. The table space must exist at the currently set XDB Server location. If the database-name qualifier is omitted, then the XDB Server assumes the table space is located in the default DSNDB04 database (within the current location). |
| bpname | A name that identifies a bufferpool. The 4K bufferpools are named BP0, BP1, BP2...BP49. The 32K bufferpools are named BP32K, BP32K1, BP32K2...BP32K9. |
| password | Consists of a short identifier. |
| integer | Specifies a whole number in the range of 1 to n. |
Although all of the information contained in the following ALTER TABLESPACE optional clauses is recorded in the XDB Server catalog tables, the XDB Server system architecture does not physically support many of these options. Refer to appropriate DB2 documentation for complete descriptions of these mainframe-specific options.
This clause has no functional equivalent withing the XDB Server operating environment. However, for DB2 compatibility purposes, the XDB Server records any of the sixty possible bufferpools (BP0...BP49, and BP32K, BP32K1...BP32K9). The first fifty bufferpools are for table spaces with 4K page sizes, while the last ten are for table spaces with 32K page sizes. Although not functionally supported, this new information is semantically checked and recorded in the XDB Server catalog tables.
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 or a TEMP database.
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 nonsegmented, the next higher level is the table space.
| TABLESPACE | Specifies table space locks. |
| TABLE | Specifies table locks. Use TABLE only for a segmented 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. |
This option is semantically checked and recorded in the XDB Server system catalog tables. See DB2 documentation for a full discussion of keyword options.
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. For a table space in a TEMP database, DB2 uses TRACKMOD NO regardless of the value specified.
| 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. |
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 table is promoted to S or X mode. If you specify LOCKMAX a for table space in a TEMP 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:
If the lock size is TABLESPACE or TABLE, LOCKMAX must be omitted, or its operand must be 0.
When the limit on the number of open data sets is reached, specifies the priority in which data sets are closed.
For a table space in a TEMP database, DB2 uses CLOSE NO regardless of the value specified.
This option is semantically checked and recorded within the XDB Server system catalog tables. See DB2 documentation for a full discussion of keyword options.
This option records a password for a DB2 subsystem dataset inside the XDB Server system catalog. To remove a password, specify a delimited string of blank characters as the password name. For example:
DSETPASS " "
XDB does not physically support partitioned table spaces, however, this clause can be used to identify a table space partition number (integer) defined within a DB2 subsystem that is being accessed through XDB Link. Do not use this clause if the DB2 table space is not partitioned.
If the DB2 table space is partitioned, the PART clause must be used if the FREEPAGE, PCTFREE, USING, PRIQTY, SECQTY, or ERASE clause appears in the ALTER TABLESPACE statement. Under these circumstances, all alterations specified in the later clause options apply specifically to the table space partition identified in the PART clause.
This option specifies the number of pages left free within a segmented table space defined on a DB2 subsystem. The integer whole number value can range from 0 to 255, but must be less than the SEGSIZE value. In situations where the number of pages free is greater than or equal to the SEGSIZE, the number of pages free is corrected downward to the SEGSIZE value minus one.
This option specifies a whole number value (integer) ranging from 0 to 99. This clause is semantically checked after which the values are recorded in the appropriate XDB Server catalog tables. See DB2 documentation for a full discussion of keyword options.
This clause specifies either a user-managed or DB2-managed dataset for a table space or partition in a DB2 subsystem. For partitioned table spaces, the USING clause is applied only to the dataset within the partition identified in the PART clause. For nonpartitioned table spaces, the USING clause is applied to every dataset that is eligible for a particular table space.
| VCAT catalog-name | Designates a user-managed dataset starting with catalog-name. The catalog-name must be a short identifier. |
| STOGROUP stogroup-name | Identifies a stogroup described in the DB2 catalog. Before applying a new description for a table space, the storage group description must include at least one volume serial number. This volume serial number is entered into the SYSIBM.SYSVOLUMES catalog table, and can be used to locate tables created in this table space on different physical paths (see CREATE STOGROUP). |
If the current dataset for the table space or partition is DB2-managed and USING STOGROUP is specified, then omitting the PRIQTY clause implicitly defines the current PRIQTY value. Additionally, omitting the SECQTY clause implicitly defines the current SECQTY value, while omitting the ERASE clause implicitly specifies the current ERASE rule.
If the current dataset for the table space or partition is user-managed and USING STOGROUP is specified, then the omission of the various clauses below implicitly specifies the following:
Indicates the replacement value for the last primary space allocation for the table space or partition dataset. This clause can be specified only under one of the following conditions:
The value integer is the primary space allocation in kilobytes. Any whole number can be specified up to a limit of 4,194,304. Values greater than 4,194,304 are reduced to 4,194,304. When specifying a number less than 12 for a table space with a page size of 4K, the system increases the number to 12. When specifying a number less than 96 for a table space with a page size of 32K, the system increases the number to 96. If PRIQTY -1 is specified, DB2 uses a default value for the primary space allocation.
If PRIQTY is specified, the primary space allocation is at least n kilobytes, where n is the value of integer with the following exceptions:
For LOB table spaces, the exceptions are:
Indicates the replacement value for the last secondary space allocation for the table space or partition dataset. This clause can be specified only under one of the following conditions:
The value integer is the secondary space allocation in kilobytes. For a dataset or table space using 4K pages, any whole number can be specified up to a limit of 131,068. Values greater than 131,068 are reduced to 131,068. The maximum allowable secondary allocation for 32K page size datasets is 131,040. If a value of zero is specified, the dataset cannot be extended. If SECQTY -1 is specified, DB2 uses a default value for the secondary space allocation.
If SECQTY is specified, the secondary space allocation is at least n kilobytes, where n is the value of integer with the following exceptions:
For LOB table spaces the exceptions are:
Do not specify the SECQTY clause for user-managed datasets.
This clause can be specified only under one of the following conditions:
Do not specify the ERASE clause for user-managed datasets.
Specifies whether data compression applies to the rows of the table space or partition. Do not specify COMPRESS for a LOB table space.
Either the option specified or the default value is recorded in the SYSIBM.SYSTABLEPART system catalog table. This option does not compress data in any way.
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 or TEMP database, but it is ignored. Do not specify GBPCAHCE for a table space in a work file database or in a TEMP database in either environment (data sharing or not).
| 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 DB2s have any interest in the page set, no pages are cached in the group buffer pool. Hiperpools are not used for table spaces or partitions that are defined with GBPCACHE ALL. 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.
SYSTEM is the default for a LOB table space. 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. |
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. You cannot alter the GBPCACHE value for the following table spaces:
Specifies whether changes to a LOB column in the table space are to be written to the log. Use LOG only for a LOB table space.
| YES | Indicates that changes to a LOB column are to be written to the log. You cannot use YES if the auxiliary table in the table space stores a LOB column that is greater than 1 gigabyte in length.
When you change the value of LOG to YES, the LOB table space is placed in copy pending status. |
| NO | Indicates that changes to a LOB column are not to be written to the log.
LOG NO has no effect on a commit or rollback operation; the consistency of the database is maintained regardless of whether the LOB value is logged. All committed changes and changes that are rolled back reflect the expected results. Even when LOG NO is specified, changes to system pages and to the auxiliary index are logged. During the log apply operation of the RECOVER utility, LPL recovery, or GPB recovery, all LOB values that were not logged are marked invalid and cannot be accessed by a SELECT or FETCH statement. Invalid LOB values can be updated or deleted. |
You can specify the LOCKPART clause, but it has no effect. Starting with Version 8, 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.
You cannot execute the ALTER TABLESPACE statement while a DB2 utility has control of the table space.
To change FREEPAGE, PCTFREE, USING, PRIQTY, SECQTY, COMPRESS, ERASE, or GBPCACHE for more than one partition, you must use separate ALTER TABLESPACE statements.
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
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
Alter table space DSN8S61E in database DSN8D61A. CLOSE NO means that the data sets of the table space are not to be closed when there are no current users of the table space.
ALTER TABLESPACE DSN8D61A.DSN8S61E
CLOSE NO;
Alter table space DSN8S61D in database DSN8D61A. BP2 is the buffer pool associated with the table space. PAGE is the level at which locking is to take place.
ALTER TABLESPACE DSN8D61A.DSN8S61D
BUFFERPOOL BP2
LOCKSIZE PAGE;