Using the REOPT bind option with input variables in complex queries
Input variables are essential for good statement preparation times in an online transaction processing (OLTP) environment, where statements tend to be simpler and query access plan selection is more straightforward.
Multiple executions of the same query with different input variable values can reuse the compiled access section in the dynamic statement cache, avoiding expensive SQL statement compilations whenever the input values change.
However, input variables can cause problems for complex query workloads, where query access plan selection is more complex and the optimizer needs more information to make good decisions. Moreover, statement compilation time is usually a small component of total execution time, and business intelligence (BI) queries, which do not tend to be repeated, do not benefit from the dynamic statement cache.
If input variables need to be used in a complex query workload,
consider using the REOPT(ALWAYS) bind option. The REOPT bind
option defers statement compilation from PREPARE to OPEN or EXECUTE
time, when the input variable values are known. The values are passed
to the SQL compiler so that the optimizer can use the values to compute
a more accurate selectivity estimate. REOPT(ALWAYS) specifies that
the statement should be recompiled for every execution. REOPT(ALWAYS)
can also be used for complex queries that reference special registers,
WHERE TRANS_DATE = CURRENT DATE - 30 DAYS,
for example. If input variables lead to poor access plan selection
for OLTP workloads, and REOPT(ALWAYS) results in excessive overhead
due to statement compilation, consider using REOPT(ONCE) for selected
queries. REOPT(ONCE) defers statement compilation until the first
input variable value is bound. The SQL statement is compiled and optimized
using this first input variable value. Subsequent executions of the
statement with different values reuse the access section that was
compiled on the basis of the first input value. This can be a good
approach if the first input variable value is representative of subsequent
values, and it provides a better query access plan than one that is
based on default values when the input variable values are unknown.
- For embedded SQL in C/C++ applications, use the REOPT bind option. This bind option affects re-optimization behavior for both static and dynamic SQL.
- For CLP packages, rebind the CLP package with the REOPT bind
option. For example, to rebind the CLP package used for isolation
level CS with REOPT ALWAYS,
specify the following command:
rebind nullid.SQLC2G13 reopt always
CLI applications, set
the REOPT value in one of the following ways:
- Use the REOPT keyword setting in the db2cli.ini configuration
file. The values and corresponding options are:
- 2 = SQL_REOPT_NONE
- 3 = SQL_REOPT_ONCE
- 4 = SQL_REOPT_ALWAYS
- Use the SQL_ATTR_REOPT connection or statement attribute.
- Use the SQL_ATTR_CURRENT_PACKAGE_SET connection or statement attribute
to specify either the NULLID, NULLIDR1, or NULLIDRA package sets.
NULLIDR1 and NULLIDRA are reserved package set names. When used, REOPT ONCE or 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
- Use the REOPT keyword setting in the db2cli.ini configuration file. The values and corresponding options are:
- For JDBC applications that use the IBM® Data Server Driver for JDBC and SQLJ, specify the -reopt value when you run the DB2Binder utility.
- For SQL PL procedures, use one of the following approaches:
- Use the SET_ROUTINE_OPTS stored procedure to set the bind options
that are to be used for the creation of SQL PL procedures within the
current session. For example, call:
- Use the DB2_SQLROUTINE_PREPOPTS registry variable to set the SQL PL procedure options at the instance level. Values set using the SET_ROUTINE_OPTS stored procedure will override those specified with DB2_SQLROUTINE_PREPOPTS.
- Use the SET_ROUTINE_OPTS stored procedure to set the bind options that are to be used for the creation of SQL PL procedures within the current session. For example, call:
<STMTPROFILE ID="REOPT example "> <STMTKEY> <![CDATA[select acct_no from customer where name = ? ]]> </STMTKEY> <OPTGUIDELINES> <REOPT VALUE='ALWAYS'/> </OPTGUIDELINES> </STMTPROFILE>