Creating Models › Target Servers for Physical Models › Database Property Editors › DB2 LUW Property Editors › Define a DB2 LUW Index

Define a DB2 LUW Index

A primary key (PK) and a foreign key (IF) index is automatically created for each table in your model, based on the primary key and foreign key columns. If you designate columns as an alternate key group (AK) or an inversion entry (IE), the equivalent unique (AK) and non-unique (IE) indexes are also generated.

Use the DB2 for LUW Table Index Editor to create alternate key (unique) and inversion entry (non-unique) indexes for tables in your model. After you create an index, you can use the Index editor to modify its properties, such as the index name, column members, physical properties, user-defined properties, and comments.

To define a DB2 LUW Index

1. Right-click a table index in the Model Explorer and click Properties.

   The DB2 for LUW Table Index Editor opens.

2. Select an index and work with the following options:

   Note: Click New on the toolbar to create a index. Use the Enter filter text text box to filter a large list of indexes to locate the one you want to work with.

   Show FK Indexes

   Specifies whether or not to display foreign key indexes in the Index list.

   Physical Name

   Specifies the name of the index.

   Schema

   Specifies the default schema for the index.

   Type

   Shows the key group type. This is a read-only field.

   Is Unique

   Specifies whether or not the columns defined in the index must be unique.

   Physical Only

   Specifies whether or not the index should be suppressed from a logical model and appear in a physical model only.

   Generate

   Specifies whether or not to generate DDL for this object during Forward Engineering.

   Generate As Constraint

   Specifies whether or not to generate a constraint based on the information in the selected index during Forward Engineering.

3. On the Members tab, select the columns from the table that you want to include in the index. In the Include Column section, select any additional columns that you want to include in the index.

   You can sort the columns and open the DB2 for LUW Table Column Editor.

4. Work with the following fields on the General tab:

   Constraint Name

   Specifies the constraint associated with the index.

   Clustered

   Specifies that the index is the clustering index of the table.

   Tablespace

   Specifies the tablespace name in which to create the index.

   Percent Free

   Specifies the percentage of each index page to leave as free space when building the index.

   Level 2 Percent Free

   Specifies the percentage of each index level 2 page to leave as free space when building the index.

   Minimum Percent Used

   Specifies whether index leaf pages are merged online, and the threshold for the minimum percentage of space used on an index leaf page.

   Allow Index Reverse Scan

   Specifies that an index can support both forward and reverse scans; that is, scanning of the index in the order that was defined at index creation time, and scanning in the opposite order.

   Page Split Type

   Specifies the index split behavior. Select from the drop-down list. The valid values are symmetric, high, and low.

   Collect Statistics Type

   Specifies that the index statistics have to be collected at the time of creating the index. Select from the drop-down list. The valid values are basic, detailed, and detailed by sampling.

   Is Compressed

   Specifies that the index will use index compression. Select from the drop-down list; valid values are True and False.

   Is Partitioned

   Specifies that a partitioned index should be created.

5. Click the Extend Using tab and work with the following options:

   Index Extension

   Specifies the name of the index-extension used to manage this index.

   Expressions

   Specifies values for any required arguments for the index extension. Each expression must be a constant value with a data type that exactly matches the defined data type of the corresponding index extension parameters, including length or precision, and scale.

6. Click the Permission tab and grant permissions to users or roles on the selected index.
7. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
8. (Optional) Click the Where Used tab to view where the object is used within the model.
9. (Optional) Click the UDP tab to work with user-defined properties for the object.
10. (Optional) Click the Notes tab to view and edit user notes.
11. (Optional) Click the Extended Notes tab to view or edit user notes.
12. Click Close.

    The changes are saved and the DB2 for LUW Table Index Editor closes.