SQL is the language used for communicating with DB2. Since DB2 does work on behalf of applications using SQL, the SQL is the number one place to concentrate your initial tuning efforts. Therefore, it is best if the SQL is written with the following best practices in mind:
  • Limit the amount of data returned
  • Avoid "SELECT *"
  • Use predicates with good selectivity
  • Use Range delimiting and Index SARGable predicates wherever possible
  • When joining tables, specify join predicates that are indexed
  • Analyze local, order by, and join predicates and ensure that indexes are created on the columns being joined first, then created on the columns to support the ORDER BY statement, and then local predicates for index access (index only access)
New in DB2 V8.1, Type-2 indexes improve performance by eliminating most next-key-share locks, as entries are marked deleted instead of physically deleted from the page. Type-2 indexes are required for online load, online reorganization, and MDC. A table cannot have a mix of type-1 and type-2 indexes. Tables can be migrated to type-2 indexes via index reorganization. Type-2 indexes let you create an index on a column that is longer than 255 bytes. Convert indexes to Type-2 for your most important tables then the less important ones until all indexes have been converted. Any action that causes the index to be recreated will cause the index to be recreated as Type-2 as long as there are no Type-1 indexes. Type-2 and Type-1 cannot be mixed. Refer to the table below for a list of predicate types.

Characteristic Range Delimiting Index SARGable Data SARGable Residual
Reduce index I/O Yes No No No
Reduce data page I/O Yes Yes No No
Reduce number of rows passed internally Yes Yes Yes No
Reduce number of qualifying rows Yes Yes Yes Yes

Range delimiting (Index Manager) predicates are used to bracket an index scan or provide start and stop keys for the index search. Index Sargable predicates are not used to bracket a search, but are evaluated from the index. Columns involved in the predicates are part of the index and evaluated by Index Manager.

Data sargable predicates cannot be evaluated by index manager and are evaluated by Data Management Services. They typically require data access. DMS will retrieve the columns to evaluate the predicate as well as any others to satisfy columns in the select list that could not be obtained via an index.

Residual predicates require I/O beyond just accessing the base table such as quantified subqueries - ANY, ALL, IN, SOME, Long VARCHAR, LOBS, etc.

Residual predicates are evaluated by RDS and are the most costly of the 4 categories of predicates.

When creating indexes, consider using PCTFREE in the index specification to avoid index page splits. The default is 10%. If more than 10% is specified, only 10% free will be provided for NON-LEAF pages.