skip to main content
DataDirect Connect Drivers : DB2 Driver : Connection Properties : CurrentQueryOptimization
  
CurrentQueryOptimization
Purpose
Specifies the DB2 optimization class that is performed by the database when generating a query plan for a SQL statement.
This property only applies to connections to DB2 V9.7 and higher for Linux/UNIX/Windows.
Valid Values
-1 | 0 | 1 | 2 | 3 | 5 | 7 | 9
Behavior
If set to -1, the default class configured for the database server is used.
If set to 0, a minimum amount of optimization is performed. This class is useful for simple dynamic SQL to well-indexed database tables.
If set to 1, optimization that is comparable to DB2 Version 1 for Linux/UNIX/Windows is performed.
If set to 2, more optimization is performed than if set to a value of 1, but significantly less optimization is performed than if set to a value of 3 and higher, particularly for complex queries.
If set to 3, a moderate amount of optimization is performed.
If set to 5, a significant amount of optimization is performed. For complex dynamic SQL statements, heuristic rules are used to limit the amount of time spent selecting a query plan. When possible, Select statements obtain data from a materialized query table.
If set to 7, a significant amount of optimization is performed. For complex dynamic SQL statements, heuristic rules are not used to limit the amount of time spent selecting a query plan. When possible, Select statements obtain data from a materialized query table.
If set to 9, the maximum amount of optimization is performed, which can significantly expand the number of query plans that are evaluated for selection. This value, along with performance metrics, can be used to determine if a better-performing query plan can be generated for complex or long-running SQL statements.
Default
-1
Data Type
int
See also
Choosing a DB2 Optimization Class