Creating an index

Authorities and privileges

To create an index:

1. Open the Create Index window.
2. Specify required properties on the Index page.
3. Specify optional properties on the Properties page .
4. Specify storage allocation properties on the Index Space Management page.
5. If you are creating a partitioned index, specify partition properties on the Partition Management page. (If the target table is nonpartitioned, then this tab is not available.)
6. Click OK.

• Altering an index
• Dropping an index
• Stopping indexes
• Starting indexes

Specifying required properties

1. Identify the index and the target table:
1. In the Index owner field, specify the user ID of the person who owns the index.

Tip: To display a list of existing objects, click on the (Browse) push button next to the field. Select an existing object from the list.


2. In the Index name field, type a name for the index. Do not use the name of an existing index at the current server. If you type more than eight characters, only the first eight are used in the name.
3. In the Table owner field, specify the user ID of the person who owns the table on which your are creating an index.
4. In the Table name field, type or select the name of the table on which you are creating an index. (Use the unqualified identifier for the table.)

Tip: To display a list of existing objects, click the (Browse) push button next to the field. Select an existing object from the list.

2. Select columns for the index key:
1. In the Available columns list box, select the column or columns that you want to define as part of the index key.
2. Click on the <, <, <<, or >>(Arrow) push buttons to move the selected column or columns to the Selected columns list box.
3. Use the (Up) and (Down) push buttons to change the order of items in the Selected columns list box. The order in which the columns appear in the Selected columns list box is the order in which the index is created.
3. Optional: Use the Ascending and Descending radio buttons to specify the search order for each index key.
4. Optional: Select the Unique check box to ensure that two or more rows of the index key do not contain data with the same value. If you select the Unique check box, the Do not apply uniqueness constraint to null valued key check box is enabled.

Important: If the table contains rows with duplicate key values, the index cannot be created with the uniqueness constraint.

5. If you do not want to force the uniqueness constraint on rows that contain a null value, select the Do not apply uniqueness constraint to null valued key check box.
6. If you have data that needs to be viewed as a group or in sequence, select the Cluster by index values check box. Do not select the Cluster by index values check box if an existing index on the table already uses it.
7. Optional: Select the Build index later check box to defer building the index. If you defer building the index, you can build it later using the recover index utility.

Recommendation: If your table is populated and the index is nonunique, then you should defer the index build. If your table is populated and the index is unique, however, you should not select the Build index later check box.

Specifying optional properties

1. In the 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 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

2. Specify the maximum data set size for non-partitioned indexes. Select a value from the Maximum data set size list.
3. Optional: Select the Close eligible data sets check box. This value 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.
4. Optional: Select the Allow COPY utility check box.

Specifying storage allocation properties

1. In the Index Space Management page, select Managed by user or Managed by DB2 from the Data Set Management radio buttons.
2. If you selected Managed by user, type or select the 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.
3. If you selected Managed by DB2:
1. Type or select 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. Select Yes or No from the Erase data sets when the index is dropped radio button.
4. If you want to explicitly manage free space in the index space, select the Specify management scheme for free space check box.
1. Specify a value in the Free space reserved field
2. Specify a value in the Percentage of free space in a page field.
5. From the Cache pages in a group buffer pool radio buttons, select:

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




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

This page is available only if you:

1. Specify a table in partitioned table space in the Index page.
2. Select an available column and add it to the Selected columns list.
3. Select the Cluster by index values check box.

• To add an entry, click the Add push button, which opens the Add partition management window.
• To remove an entry, click the Remove push button.
• To change the selected entry, click the Change push button, which opens the Change partition management window.