Indexes Wizard (DB2 LUW)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for Linux, Unix, and Windows Object Wizards

Comparable to an index in a book, an index gives you speedy access to particular records in a table. The Index Wizard lets you create an index without knowing the underlying commands.

To create a new index using a wizard:

  1. Open a creation wizard for an index. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Indexes (DB2 LUW) - Properties

When creating or editing an index, this tab/panel lets you work with the following settings:

Setting Description

Parent Type, Parent Schema and Parent Name

Choose the type (TABLE or MATERIALIZED QUERY TABLE) owning schema, and name of the parent object in which the index is being created.

Schema and Name

Choose the owner and name of the index being created.

Index Type

Index enforces uniqueness on the values of the table's index key.


Specifies that the index is the clustering index of the table. The cluster factor of a clustering index is maintained or improved dynamically as data is inserted into the associated table, by attempting to insert new rows physically close to the rows for which the key values of this index are in the same range.

Allow Reverse Scans

If selected, a ALLOW REVERSE SCAN clause is included with the CREATE/ALTER INDEX statement, specifying that the index supports both forward and reverse scans.

Compress (9.7^)

YES - Selecting this option adds a COMPRESS YES clause, enabling index compression. NO - Selecting this option adds a COMPRESS NO clause, disabling index compression. DEFAULT - Selecting this option specifies default compression handling to that of the parent object.

Percent Free

Lets you provide a PCTFREE value, specifying the percentage of each index page to leave as free space when building the index, from 0 to 99.

Minimum Percent Used

Lets you provide a MINPCTUSED value, specifyinf whether index leaf pages are merged online, and the minimum percentage threshold of space used on index leaf pages.

Indexes (DB2 LUW) - Columns and Include Columns

Index columns can be segregated into unique key columns (Columns pane) and Include columns that are to be part of the index but do not form part of the unique key.

The steps in completing the panes for the two column types are identical.

  • From the Column dropdown, select a column for the index and specify a Sort option. To add more columns, click the New button and then follow the steps in the last instruction. Use the Delete button to drop columns.