Creating an index

Use the Create Index window to create an index.

Authorities and privileges

To create an index:

  1. Open the Create Index window.
  2. Specify required properties on the Index page.
  3. Optional: 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 space is nonpartitioned, then this tab is not available.)
  6. Click OK.

Related information

Specify required properties

To specify general properties on the Index page:
  1. Identify the index and the target table:
    1. In the Index owner field, specify a user ID of the person who will own the index.

      2. 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. 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 you 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.)

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

    5. Select an Index type from the radio buttons:

      6. Recommendation: To ease migration to future releases of DB2, specify type 2 index.
      Type 2
      Use type 2 indexes whenever possible. Type 2 indexes provide functions that are unavailable with type 1 indexes, such as:
      • Row locking on the table space
      • UR isolation for an access path
      • Processing of queries by multiple parallel tasks
      • Concurrent access to separate logical partitions
      Type 1
      Do not specify type 1 if the identified table:
    6. Has a LOCKSIZE of ROW
    7. Is in a large partitioned table space
    8. Has an ASCII encoding scheme
  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 >> push button to move the selected column or columns to the Selected columns list box.
    3. Use the Move Up and Move 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.

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

To specify optional properties on the Properties page:
  1. If you selected index type 1, specify the Number of subpages for each physical page. If you selected index type 2, this field is not available.

    2. The number specifies the unit of index locking that DB2 uses for each physical page. Type 1, 2, 4, 8, or 16. In a data sharing environment you must specify 1.
  2. In the Buffer pool field, specify a buffer pool for your index. You must specify an active 4 KB buffer pool. The default buffer pool is the database's buffer pool, unless the uses a 32 KB buffer pool. If the index cannot use the database's buffer pool by default, then BP0 is used.

    3. Important: The privilege set must contain SYSADM authority, SYSCTL authority, or USE privileges on the specified buffer pool.
  3. In the Data set password field, type the password for the VSAM data sets. This password is defined through access method services. If you use storage groups, this password is the password that protects the data as well as the password that is passed to access method services when DB2 uses the data sets. If the index occupies multiple data sets, these data sets must have the same password.

    4. Important: The password setting does not apply to data sets that are managed by the Storage Management Subsystem (SMS). RACF or a similar external security system protects data sets defined to SMS.

  4. Select the maximum data set size for nonpartitioned indexes. If you selected Cluster by index values, this field is unavailable.
  5. 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.

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

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

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

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

      4. 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 inthe Percentage of free space in a page field.
  5. From the Cache pages in a group buffer pool radio buttons, select:
    6. 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

Use the Partition Management page to manage space at the partitions level.

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.
The Data management at partition level at partition table displays the defined partition management settings.