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, such as 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.

There a number of ways that REOPT can be specified:

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
```
For 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
```
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:
```
sysproc.set_routine_opts('reopt always')
```
- 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.

You can also use optimization profiles to set REOPT for static and dynamic statements, as shown in the following example:

<STMTPROFILE ID="REOPT example ">
     <STMTKEY>
       <![CDATA[select acct_no from customer where name = ? ]]>
     </STMTKEY>
     <OPTGUIDELINES>
        <REOPT VALUE='ALWAYS'/>
    </OPTGUIDELINES>
</STMTPROFILE>