DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The bind option REOPT can significantly improve the Embedded SQL application performance. The following are the descriptions for both Static and Dynamic SQL.
The bind option REOPT can make static SQL statements containing host variables, global variables or special registers behave like incremental-bind statements. 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.
With REOPT ONCE, the access plan is cached after the first OPEN or EXECUTE request and is used for subsequent execution of this statement. With REOPT ALWAYS, the access plan is regenerated for every OPEN and EXECUTE request, and the current set of host variable, parameter marker, global variable and special register values is used to create this plan.
When you specify the option REOPT ALWAYS, the database manager postpones preparing any statement containing host variables, parameter markers, global variables or special registers until it encounters an OPEN or EXECUTE statement; that is, when the values for these variables become known. At this time, the access plan is generated using these values. Subsequent OPEN or EXECUTE requests for the same statement will recompile the statement, reoptimize the query plan using the current set of values for the variables, and execute the newly generated query plan. When REOPT ALWAYS is specified, statement concentrator is disabled.
The option REOPT ONCE has a similar effect, with the exception that the plan is only optimized once using the values of the host variables, parameter markers, global variables and special registers. This plan is cached and will be used by subsequent requests.