Specifying required index information

These steps are part of the larger task of Creating an index. When you complete the steps for specifying the index information, return to Creating an Index.



To specify information on the Index page:

1. Identify the index and the associated table:
   
   
   1. In the Index owner field, specify a user ID of the person who will own the index.

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

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

      • Has a LOCKSIZE of ROW
      • Is in a large partitioned table space
      • 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 or from 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. Use the Ascending and Descending radio buttons to specify the search order for each index key.
   
   
4. 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. 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.
   

[Return to Create an index]