Static SQL is rigidly formed and usually provides a known access path to data. Dynamic SQL is more flexible, but this flexibility comes with some trade-offs. For example, dynamic SQL brings the potential for more accurate access paths but at the cost of not knowing what that access path will be before the SQL is run.
The differences between static and dynamic SQL are lessening, though. Over the past few DB2 versions, IBM has added options and features that blur the differences between dynamic and static SQL. One of these features provides improved and additional options for the REOPT parameter.
The REOPT parameter of the BIND command specifies whether to have DB2 determine an access path at run-time by using the values of host variables, parameter markers and special registers. There are four options from which to choose when specifying REOPT:
REOPT(NONE). DB2 won’t reoptimize SQL at run-time to factor in the values of host variables, parameter markers and special registers. REOPT(NONE) is the default; choose this option when static SQL access paths are fine.
REOPT(ALWAYS). DB2 will reprepare every time the statement is executed. This means that statements containing host variables, parameter markers or special registers will be prepared using actual values, which should improve the optimization. Subsequent OPEN or EXECUTE requests for the same statement will reprepare the statement, reoptimize the query plan using the current set of values for the variables and execute the newly generated query plan. Consider using REOPT(ALWAYS) for dynamic SQL with parameter markers and to avoid dynamic statement caching.
REOPT(ONCE). DB2 will prepare SQL statements only once, using the first set of host variable values, no matter how many times the statement is executed by the program. The access path is stored in the dynamic statement cache and will be used for all subsequent executions of the same SQL statement.
REOPT(AUTO). This option directs DB2 to attempt to formulate the optimal access path in the minimum number of prepares. The basic premise of REOPT(AUTO) is to reoptimize only when host variable values change significantly enough to make reoptimization worthwhile. Using this option, DB2 will examine the host variable values and generate new access paths only when host variable values change and DB2 hasn’t already generated an access path for those values.
You might consider specifying REOPT(AUTO) for SQL statements that can take a relatively long time to execute, depending on the values of parameter markers. In particular, you should especially consider doing this when parameter markers refer to non-uniform data that’s joined to other tables.
Also, consider re-evaluating programs bound specifying REOPT(ONCE). In some cases, switching to REOPT(AUTO) from REOPT(ONCE) can produce a performance improvement by reoptimizing when it makes sense, instead of just sticking with a single access path based on the first values supplied to the parameter markers.
REOPT and Static SQL
With static SQL, the values for any host variables, parameter markers or special registers aren’t taken into account when the program is bound. But you can use REOPT to cause DB2 to factor these values into the optimization process. REOPT(ALWAYS) or REOPT(NONE) apply to static SQL, but REOPT(ONCE) and REOPT(AUTO) are only valid for dynamic SQL because DB2 doesn’t cache static query plans.
Consider binding with REOPT(ALWAYS) when the values for your program’s host variables or special registers are volatile and make a significant difference for access paths. This means that these statements get compiled at the time of EXECUTE or OPEN instead of at BIND time. During this compilation, the access plan is chosen, based on the real values of these variables.
Be sure to factor in the overhead to prepare the access plan for all the SQL in the program at run-time, as the more complex the SQL, the greater the overhead will be.
If you have only one or two static SQL statements that would benefit from reoptimization at run-time, consider creating separate packages. Put the statements that can benefit from reoptimization into a package that can be bound REOPT(ALWAYS) and put the remaining statements into a program that can be bound with REOPT(NONE). Doing so will cause your application to incur the cost of reoptimization only for those statements that may benefit.
These days dynamic doesn’t have to be totally dynamic and static doesn’t have to be totally static. You can take measures to make dynamic SQL more static and static SQL more dynamic; and REOPT is just one way.