Enables query optimization or reoptimization of SQL statements
that have special registers, global variables, or parameter markers.
- db2cli.ini keyword syntax:
- Reopt = 2 | 3 | 4
- Default setting:
- No query optimization occurs at query execution time. The default
estimates chosen by the compiler are used for special registers, global
variables, or parameter markers.
- Equivalent connection or statement attribute:
- SQL_ATTR_REOPT
- Usage notes:
-
Optimization occurs by using the values available at query
execution time for the special registers, global variables, or parameter
markers instead of the default estimates that are chosen by the compiler.
The valid values of the keyword are:
- 2 = SQL_REOPT_NONE. This is the default. No query optimization
occurs at query execution time. The default estimates chosen by the
compiler are used for the special registers, global variables, or
parameter markers. The default NULLID package set is used to execute
dynamic SQL statements.
- 3 = SQL_REOPT_ONCE. Query optimization occurs once at query execution
time, when the query is executed for the first time. The NULLIDR1
package set, which is bound with the REOPT ONCE bind option, is used.
- 4 = SQL_REOPT_ALWAYS. Query optimization or reoptimization occurs
at query execution time every time the query is executed. The NULLIDRA
package set, which is bound with the REOPT ALWAYS bind option, is
used.
The NULLIDR1 and NULLIDRA are reserved package set names, and
when used, REOPT ONCE and REOPT ALWAYS are implied respectively. These
package sets have to be explicitly created with the following commands:
db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA
If both the
Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet
takes precedence.