When you create a unique key, a primary key, or a foreign key an index is created. An index optimizes data retrieval without performing a lengthy sequential search; that is, you can avoid having the entire table scanned when data is queried. You do not decide when an index should be used to improve performance; DB2 makes this decision based on the available table and index information. However, you play an important role in the process by defining keys that create the necessary indexes. It is also important for you to collect statistics on your indexes, both when you create them and on an ongoing basis. Keys are described in "Step 3. Enforcing Business Rules for Data". "Step 4. Collecting Statistics" describes how to collect statistics.
The indexes you need are based on how you want to group the data and how the data is most frequently accessed. Creating a large number of indexes for a table that receives many updates can slow down processing of requests. Therefore, you should use indexes only where a clear advantage for frequent access exists. The simplest recommendation is that you define a key on the table columns you use the most.
Once you have a good understanding of the performance of your system, you may be able to improve performance by placing indexes in separate table spaces.
Note that you cannot change any clause of an index definition; you must drop the index and create it again. (Dropping an index does not cause any other objects to be dropped but may cause some packages to be invalidated.) The online help for the Control Center describes how to drop an index.
See the Administration Guide for more information on indexes.