| Craig S. Mullins
|October / November 2008
by Craig S. Mullins
Every good DB2 developer knows that DB2 offers two types of SQL, static and dynamic. Static SQL is hard-coded and embedded into an application program and then bound into a package, which determines the access path that DB2 will use when the program is run. Dynamic SQL is built within the program “on the fly” as it executes. Once built, the dynamic SQL statement must be compiled using the PREPARE statement; or, alternately, an implicit PREPARE is issued behind the scenes when implementing the EXECUTE IMMEDIATE flavor of dynamic SQL.
But are you aware of the REOPT parameter and how you can use it to reoptimize the static and dynamic SQL in your application programs?
Effects of REOPT on Dynamic SQL
You can gain additional optimization for dynamic SQL using the REOPT parameter of BIND. REOPT 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:
After migrating to DB2 9, consider specifying REOPT(AUTO) for SQL statements that at times can take a relatively long time to execute, depending on the values of parameter markers. In particular, especially consider REOPT(AUTO) when parameter markers refer to non-uniform data that is 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 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.
Effects of REOPT on Static SQL
REOPT(ALWAYS) and REOPT(NONE) apply to static SQL as well as dynamic.
REOPT(ONCE) and REOPT(AUTO) are not valid for static SQL because DB2
does not cache static plans. The REOPT parameters and to which types of
SQL they apply is summarized in the following table:
Consider binding static SQL 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 actual values.
Be sure to factor in the overhead to prepare the access plan for all the SQL in the program at run time -- 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 runtime consider creating a separate program. Put the statements that can benefit from reoptimization into a program that can be bound REOPT(ALWAYS) or REOPT(AUTO), 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.
And remember to keep the REOPT parameter in mind as you develop your DB2 applications.
zJournal, Oct / Nov
© 2008 Craig S. Mullins, All rights reserved.