DB2 query optimization level
When a database query is executed in DB2, various methods are used to calculate the most efficient access plan. The query optimization level parameter sets the amount of work and resources that DB2 puts into optimizing the access plan. The range is from zero to 9.
An optimization level of 9 causes DB2 to devote a lot of time and all of its available statistics to optimizing the access plan.
The optimization level is set on individual databases and can be set with either the command line or with the DB2 Control Center. Static SQL statements use the optimization level specified on the prep and bind commands. If the optimization level is not specified, DB2 uses the default optimization as specified by the dft_queryopt parameter. Dynamic SQL statements use the optimization class specified by the current query optimization special register, which is set using the SQL Set statement. For example, the following statement sets the optimization class to 1:
Set current query optimization = 1If the current query optimization register has not been set, dynamic statements will be bound using the default query optimization class.
The default value is 5. It is recommended that you set the optimization level for the needs of the application. High levels should only be used when there are very complicated queries.
Prev | Home | Next WebSphere is a trademark of the IBM Corporation in the United States, other countries, or both.
IBM is a trademark of the IBM Corporation in the United States, other countries, or both.