You can create indexes on DB2 table columns to speed up query processing. An index uses pointers to actual data to more efficiently access specific pieces of data.
Once created, DB2 automatically maintains each index as data is added, modified, and deleted from the table. As data is selected from the table, DB2 can choose to use the index to access the data more efficiently.
It is important to remember that index modification and usage is automatic—you do not need to tell DB2 to modify indexes as data changes nor can you tell DB2 to use a particular index to access data. This automation and separation makes it easy to deploy DB2 indexes.
Index:==>Leaf page ==> Non leaf page ==> Non leaf page ==> Root Page
In DB2 there is TYPE2 index being used currently. Load utility updates indexes efficiently. Type-2 index is an enhancement to the Type-1 index. It has numerous benefits.
Creating index on tables is trial and error process i.e how many or what cloumns need to use for INDEX. As of DB2 V8 the Index Key size is 2,0000 Bytes.
We need to avoid indexes on INSERT, DELETE and UPDATE. For SELECT, we can use indexes, and we can retrieve rows with less CPU time.
We can give index on columns of WHERE, GROUPBY, ORDERBY,UNION ALL etc. DB2 requires 3 types of Data pages to form an internal INDEX in DB2.
Before creating index on a Table, we need to analyze the following factors:
- Percentage of table access versus table modification (that is, the frequency of INSERT, UPDATE, and DELETE operations)
- Data access patterns, both ad hoc and planned
- Amount of data accessed by each query against the table
- Impact on recovery
- Performance requirements of accessing the table
- Performance requirements of modifying the table
- Storage requirements
- Impact of reorganization
- Impact on the LOAD utility
Srinimf 
You must be logged in to post a comment.