Reopt CLI/ODBC and IBM data server driver configuration keyword
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
- IBM® data server driver configuration file (db2dsdriver.cfg) syntax:
- <parameter name="Reopt" value="2 | 3 | 4"/>
Attention: The IBM data server
driver configuration
file (db2dsdriver.cfg) syntax is available in Db2 11.5.4 and later.
- 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.
If both the Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet takes precedence.db2 bind db2clipk.bnd collection NULLIDR1 db2 bind db2clipk.bnd collection NULLIDRA