Altering a table space

Use the table space notebook to change the description of a table space. Changes pertaining to buffer pools take effect the next time the data sets of the table space are opened. The data sets can be closed and reopened by stopping the database, and then starting the database again. Changes pertaining to the LOCKSIZE take effect after the SQL statement is prepared (if necessary) and executed.

Authorities and privileges

To alter a table space:

Open the Alter Table Space window.
The Table space tab contains read-only information. You cannot modify the type or name of a table space with command ALTER.
Enter new, or change the information in the Properties tab.
Enter new, or change the information in the Partition Management tab if this table space is a partitioned table space. You cannot change a nonpartitioned table space to a partitioned table space with command ALTER.
Enter new, or change the information in the Table Space Management tab if this table space is a nonpartitioned table space. You cannot change a partitioned table space to a non-partitioned table space with command ALTER.

Related information

Properties

Enter or select from the menu a different buffer pool name. The name must identify an activated buffer pool. The buffer pool name identifies the buffer pool to be used for the table space and must be the same page size as the table space. For 4 KB and 32 KB page buffer pools, the pages sizes are 4 KB and 32 KB, respectively.
Select a lock size to be used within this table space. This can also determine the threshold at which lock escalation occurs. This is not applicable for a table space in a work file database.

The hierarchy of lock sizes starting with the largest is:

table space lock
table lock (only for segmented table spaces)
page lock and row lock

The lock sizes are:

ANY: Specifies the DB2 can use any lock size. Generally, DB2 will choose PAGE locks. When the number of locks acquired for the table space is greater than the maximum number of locks allowed for a table space (an installation parameter), the page locks are released and locking is set at the next higher level. If the table space is not a segmented table space, the next higher level is the table space.
TABLESPACE: The table space locks.
TABLE: The table locks. This is only applicable for a segmented table space.
PAGE: The page locks.
ROW: The row locks.

Select the maximum number of page or row locks that an application process can hold simultaneously in the table space. Locks are escalated if a program asks for more than that number. The page or row locks are released and the intent lock on the table space or segmented table is promoted to S or X mode. In a data sharing environment, a lock count is kept on each member.

You can select to use the value that was established at the time you installed DB2.
Or, you can specify the number of locks allowed before escalating. Enter a value in the range 0 to 2147483647. If you enter a 0, the number of locks allowed is unlimited and escalation does not occur. If the lock size is TABLESPACE or TABLE, then do not specify any value, or enter a value of 0.

The encoding scheme cannot be changed from this dialog. The encoding scheme used is the one you selected when you created the table space.
Modify the password that will be used to access system services provided by the virtual machine. (VSAM, etc.)
Modify the maximum number of rows that DB2 will consider placing on each data page. You can enter a value from 1 to 255. The change takes effect immediately for new rows added. For data already in the table space, we recommend that you reorganize the table space after you alter the table space.
Select whether to use the default CLOSE rule. This affects whether the data sets can be closed when the table space is not in use, and the limit on the number of open data sets is reached. The change to the close rule takes effect the next time the data sets of the table space are opened. If you de-select this field, no data sets can be closed. However, if DSMAX is reached and no CLOSE YES page sets exist, then even CLOSE NO page sets will be closed.
This is applicable only for partitioned table spaces. Select to use selective partition locking (SPL) when locking a partitioned table space (LOCKPART YES). If you select to use LOCKPART YES, and all conditions required for SPL are met, only the partitions accessed are locked. If LOCKPART is YES and all conditions required for SPL are not met, every partition of the table space is locked.

Partition Management (for partitioned table spaces only)

This page of the notebook gives you information about each partition in the table space.

Select, or highlight a partition to change the properties of that partition.
With a partition highlighted, select Change to access the Change Partition Management dialog.
The first field on this dialog identifies the partition for which the changes apply. The partition or table space must be in the stopped state when altering the table space.
Select whether the data set for the partition is managed by the user (VCAT). The catalog name used with this option identifies the catalog for the data set and supplies the first level qualifier for the data set name. You must use the short identifier (no more than 8 characters) of the catalog name in the VSAM catalog name field. So, if the name of the integrated catalog facility (ICF) catalog is longer than eight characters, you must enter an alias name in this field.
If data sets are not to be managed by the user, then select "Managed by DB2". As in the selection above, this value 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 X SECQTY is at least 2 gigabytes). You can then enter or change the storage group name. DB2 will create a data set for the table space with the aid of this storage group name. The storage group must exist at the current server. 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 MVS for dynamic allocation of the data set. All identified volumes must be of the same device type. The ICF catalog must not contain an entry for the data set.
If you selected to have data sets managed by DB2, you should enter or change the minimum primary space allocation (PRIQTY). This field is not applicable if you selected data set management by USER. The PRIQTY is in kilobytes. DB2 specifies the PRIQTY to access method services using the smallest multiple of pKB, where p=the page size of the table space. The allocated space can be greater than the amount of space requested by DB2. 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.
If you selected to have data sets managed by DB2, you should enter or change the minimum secondary space allocation (SECQTY). This field is not applicable if you selected data set management by USER. The SECQTY is in kilobytes. DB2 specifies the SECQTY to access method services using the smallest multiple of pKB, where p=the page size of the table space. The allocated space can be greater than the amount of space requested by DB2.
If you selected to have data sets managed by DB2, you should select an ERASE option. This field is not applicable if you selected data set management by USER. The ERASE option indicates whether the data sets for the partition are to be erased before they are deleted during the execution of a utility or an SQL statement that drops the table space. To ALTER with ERASE, the partition must be in the stopped state.

ERASE NO means do not erase the data sets. The data in the data sets is still accessible, though not through DB2. ERASE YES means erase the data sets. DB2 overwrites all the data in the data sets with zeroes before deletion.

When you created the table space, if you chose to manage the free space, you can now change the FREEPAGE amount. The integer you enter specifies how many data pages there should be between each free page. The value you enter can be in the range of 0 to 255. If you enter a value of 0, no pages are left free. Do not specify an amount here if the table space is in a work file database. The change does not take effect until the table space is loaded or reorganized.
When you created the table space, if you chose to manage the free space, you can change the percentage of each page to leave as free space (PCTFREE) when the table space is loaded or reorganized. The value you enter can be in the range of 0 to 99. Do not specify an amount here if the table space is in a work file database.
The cached pages options are for group buffer pools only. This specifies what partitions are written to the group buffer pool in a data sharing environment. If you select to cache the pages only on update, when more than one member in the data sharing group has the partition open, and at least one member has it open for update, the updated pages are written to the group buffer pool. If you select to always cache the pages, this means that the pages are cached as they are read in from DASD.
Select whether to compress data in the rows or not. If this field is checked, the rows are compressed after you have completed the table space LOAD or REORG dialog on the partition. If this field is not checked, no data compression is used, and updated rows will be decompressed.

Table Space Management (for non-partitioned table spaces only)

Use this page of the notebook to change the details of the non-partitioned table space.

Select whether the data set for the table space is managed by the user (VCAT). This value 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 X SECQTY is at least 2 gigabytes). The catalog name used with this option identifies the catalog for the data set and supplies the first level qualifier for the data set name. You must use the short identifier (no more than 8 characters) of the catalog name in the VSAM catalog name field. So, if the name of the integrated catalog facility (ICF) catalog is longer than eight characters, you must enter an alias name in this field.
If data sets are not to be managed by the user, then select "Managed by DB2". As in the selection above, this value 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 X SECQTY is at least 2 gigabytes). You can then enter or change the storage group name. DB2 will create a data set for the table space with the aid of this storage group name. The storage group must exist at the current server. 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 MVS for dynamic allocation of the data set. All identified volumes must be of the same device type. The ICF catalog must not contain an entry for the data set.
If you selected to have data sets managed by DB2, you should enter or change the minimum primary space allocation (PRIQTY). This field is not applicable if you selected data set management by USER. The PRIQTY is in kilobytes. DB2 specifies the PRIQTY to access method services using the smallest multiple of pKB, where p=the page size of the table space. The allocated space can be greater than the amount of space requested by DB2. 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.
If you selected to have data sets managed by DB2, you should enter or change the minimum secondary space allocation (SECQTY). This field is not applicable if you selected data set management by USER. The SECQTY is in kilobytes. DB2 specifies the SECQTY to access method services using the smallest multiple of pKB, where p=the page size of the table space. The allocated space can be greater than the amount of space requested by DB2.
If you selected to have data sets managed by DB2, you should select an ERASE option. This field is not applicable if you selected data set management by USER. The ERASE option indicates whether the data sets for the table space are to be erased before they are deleted during the execution of a utility or an SQL statement that drops the table space. To ALTER with ERASE, the table space must be in the stopped state.

When you created the table space, if you chose to manage the free space, you can now change the FREEPAGE amount. The integer you enter specifies how many data pages there should be between each free page. The value you enter can be in the range of 0 to 255. If you enter a value of 0, no pages are left free. Do not specify an amount here if the table space is in a work file database. The change does not take effect until the table space is loaded or reorganized.
When you created the table space, if you chose to manage the free space, you can change the percentage of each page to leave as free space (PCTFREE) when the table space is loaded or reorganized. The value you enter can be in the range of 0 to 99. Do not specify an amount here if the table space is in a work file database.
The cached pages options are for group buffer pools only. This specifies what table spaces are written to the group buffer pool in a data sharing environment. If you select to cache the pages only on update, when more than one member in the data sharing group has the table space open, and at least one member has it open for update, the updated pages are written to the group buffer pool. If you select to always cache the pages, this means that the pages are cached as they are read in from DASD.
Select whether to compress data in the rows or not. If this field is checked, the rows are compressed after you have completed the table space LOAD or REORG dialog on the table space. If this field is not checked, no data compression is used, and updated rows will be decompressed.