Altering an index

Authorities and privileges

To alter an index:

1. Open the Alter Index window.
2. Specify new values for the required properties in the Index page.

You cannot alter the columns in the index key.
3. Specify new values for the optional properties in the Properties page.
4. If you are altering a non-partitioned index, specify new values for storage allocation properties in the Index Space Management page.
5. If you are altering a partitioned index, specify new values for partition properties in the Partition Management page.
6. Click OK.

• Creating an index
• Dropping an index
• Stopping indexes
• Starting indexes

Specify required properties

1. Index owner field
2. Index name field
3. Table Owner field
4. Table name field
5. Unique check box
6. Do not apply uniqueness constraint to null valued key check box
7. Cluster by index values
8. Build index later

Specifying optional properties

Maximum data set size field

Select a new value from the list.

Buffer pool field

Specify a buffer pool for your index. You must specify an active 4KB buffer pool. The default buffer pool is the database's buffer pool, unless the database uses a 32KB buffer pool. If the index cannot use the database's buffer pool by default, then BP0 is used.

Important: The privilege set must contain SYSADM authority, SYSCTL authority, or USE privileges on the specified buffer pool.

Close eligible data sets check box

Sets the priority for determining which data sets DB2 should close. Select the check box if you want DB2 to close data sets when the index is not being used or when the limit on the number of open data sets is reached. If you select the Close eligible data sets check box, then, when the conditions are met, DB2 will close the data sets for the least recently used index spaces before it closes the data sets for index spaces that are defined with No. If the limit is reached, and there are indexes with the close eligible data sets setting, then DB2 closes indexes without the close eligible data sets setting.

Specifying storage allocation properties

Free space reserved field

Percentage of free space in a page field

Data Set Management radio buttons

Select the Managed by user or the Managed by DB2 radio button.

If you selected Managed by user, type or select the VSAM Catalog name. The name specifies the integrated catalog facility where the data sets for this indexed are catalogued. If the catalog name is greater than eight characters, then you must use an alias for this setting.

1. Specify a storage group name. The storage group must be defined at the current server and it must include at least one volume serial number in its description. Important: The privilege set must contain SYSADM authority, SYSCTL authority, or USE privileges on the specified storage group.
2. Type the Minimum primary space allocation. This value should be an integer between 12 and 4194304, inclusive. Smaller or larger values will use 12 or 4194304, respectively.
3. Type the Minimum Secondary space allocation. If you want DB2 enabled to extend the data set for the index if necessary, this value should be an integer between 12 and 131068, inclusive. If you specify 0, DB2 cannot extend the data set. If you specify a Minimum primary space and do not specify this value, then DB2 calculates a default secondary space allocation. The default is the larger of two values: (1) 10 percent of the minimum primary space allocation or (2) 3 times the index page size, which is 3 * 4KB.
4. Optional: Select the Erase data sets when the index is dropped radio button.

Only on Update (CHANGED)

When more that one DB2 member has the index or partition open and at least one member has the index open for an UPDATE operation, updated pages are written to the group buffer pool.

Always cache pages (ALL)

Pages are cached in the group buffer when read in from DASD.

No caching (NONE)

No pages are cached to the group buffer pool.

Important: These values are ignored and no pages are cached if the group buffer pool is defined for cross-invalidation use only.

Specifying partition management properties

Change partition management properties in the Partition Management page. The Data management at partition level displays the defined partition management settings.
To change an entry, click the Change push button.