Coding for effective indexes

The following topics provide suggestions to help you design code which allows Db2® for i to take advantage of available indexes:

Avoid numeric conversions
When a column value and a host variable (or constant value) are being compared, try to specify the same data types and attributes. Db2 for i might not use an index for the named column if the host variable or constant value has a greater precision than the precision of the column. If the two items being compared have different data types, Db2 for i needs to convert one or the other of the values, which can result in inaccuracies (because of limited machine precision).
Avoid arithmetic expressions
Do not use an arithmetic expression as an operand to compare to a column in a row selection predicate. The optimizer does not use an index on a column compared to an arithmetic expression. While this technique might not cause the column index to become unusable, it prevents any estimates and possibly the use of index scan-key positioning. The primary thing that is lost is the ability to use and extract any statistics that might be useful in the optimization of the query.
Avoid character string padding
Try to use the same data length when comparing a fixed-length character string column value to a host variable or constant value. Db2 for i might not use an index if the constant value or host variable is longer than the column length.
Avoid the use of LIKE patterns beginning with % or _
The percent (%), and underline (_), used in the pattern of a LIKE (OPNQRYF %WLDCRD) predicate, specify a character string like the row column values to select. They can take advantage of indexes when used to denote characters in the middle or at the end of a character string.
Using derived indexes
SQL indexes can be created where the key is specified as an expression. This type of key is also referred to as a derived key.
Using sparse indexes
SQL indexes can be created using WHERE selection predicates. These indexes can also be referred to as sparse indexes. The advantage of a sparse index is that fewer entries are maintained in the index. Only those entries matching the WHERE selection criteria are maintained in the index.

Parent topic: Creating an index strategy