DB2 Optimizer Directives

From DB Optimizer
Jump to: navigation, search

DB2 Optimizer Directives

Hints for DB2

DB2 on LUW does not allow direct influencing of the access path, as is allowed by Oracle, DB2 on z/OS and other platforms. There are several things that can affect the access path chosen.

1. Set Query Optimization Level

This controls the number of rules and methods applied by the optimizer. Zero (0) is the lowest, 9 is the highest, and 5 is the default. A higher optimization level will spend more CPU and elapsed time to perform access path computations.
set current query optimization level 0; .
where level could be 0,1,2,3,5,7,9

2. Optimize For <n> rows.

This clause is used to influence the optimizer whicfh respect to the result set size. If a small number or even 1 is specified, DB2 might choose to use an index rather than more costly methods such as a tablescan or list prefetch. Note that the entire answer can be retrieved, so it is typically important to limit the rows retrieved to a small number. Some schools of thought recommend to always use OPTIMIZE FOR 1 ROW if this clause is specified. This ensures that the access path chosen will be for a small answer set.
They have Optimize For 1 Row and Optimize For <n> Rows

3. Fetch First <n> Rows Only

In contrast to #2 Optimize For <n> Rows, the Fetch First puts a hard limit the answer set to only the first <n> rows. This is very useful when only a few rows are required by the application. Fetch First should also cause Optimize For to be in effect, but both #2 and #3 can be specified.

4. More Detailed Statistics

DB2 has the capability to collect more detailed index statistics and column distribution statistics. These statistics can affect the access path. It is also possible in DB2 LUW to specify the number of frequency values and quantile values to be collected.
Frequency values show the percentage occurence for the top <n> values. If a frequently occurring value is specified in a predicate, such as C1=v, where v occurs frequently enough, DB2 may choose to avoid using an index and scan the table for all rows. A value that is not in the <n> (default 10) list of frequent values assumes a unifor distribution.
Quantile values help DB2 to determine how many rows might qualify for a range predicate. The quantile value, in conjunction with HIGH2KEY and LOW2KEY in from SYSCAT.COLUMNS, allows DB2 to more accurate predict the number of rows that would be returned from a range.
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0005118.html
More detailed index statistics may help DB2 choose a better access path.
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001047.html

5. Selectivity

This is an advanced technique that involves setting a DB2 registry variable and a stop/start of DB2. Many customers may not wish to do this. IBM considers this a technique of last resort. The registry variable and its implementation is:
DB2_SELECTIVITY
  • Operating system: All
  • Default=NO, Values: YES or NO
  • This registry variable controls where the SELECTIVITY clause can be used in search conditions in SQL statements.
  • When this registry variable is set to YES, the SELECTIVITY clause can be specified for the following predicates:
    • A basic predicate in which at least one expression contains host variables
    • A LIKE predicate in which the MATCH expression, predicate expression, or escape expression contains host variables
db2set DB2_SELECTIVITY=YES
db2 force application all (to kick off all connected users)
db2stop
db2start
The following IBM article is extremely valuable to understanding DB2 optimization and selectivity:
http://www.ibm.com/developerworks/db2/library/tips/dm-0312yip/index.html