REOPT bind option
The REOPT option specifies whether DB2® determines an access path at run time by using the values of host variables, parameter markers, and special registers.
REOPT |
|
On: BIND and REBIND PLAN and
PACKAGE Not valid for REBIND of a native SQL procedure package. |
- (NONE)
- Does not determine an access path at run time. You can use NOREOPT(VARS) as a synonym for REOPT(NONE).
- (ALWAYS)
- Determines the access path again at run time each time the statement
is run. DB2 determines access
paths at both bind time and run time for statements that contain one
or more of the following variables:
- Host variables
- Parameter markers
- Special registers
At run time, DB2 uses the values in those variables to determine the access paths. You can use REOPT(VARS) as a synonym for REOPT(ALWAYS).
- (ONCE)
- Determines the access path for any dynamic statement only once, at the first run time or at the first time the statement is opened. This access path is used until the prepared statement is invalidated or removed from the dynamic statement cache and needs to be prepared again.
- (AUTO)
- Autonomically determines if a new access path needs to be generated to further optimize the performance for each execution. DB2 determines the access path at the first run time and then at each subsequent execution, DB2 evaluates whether a new access path is needed. If so, DB2 generates a new access path and uses that access path until another new access path is generated. For cached dynamic statements that reference parameter markers, a new path can be generated at any execution. The new path is based on changes to estimated filter factors for predicates that result from changes to parameter marker values.
Usage notes:
If you specify the bind option REOPT(ALWAYS), REOPT(AUTO), or REOPT(ONCE), DB2 sets the bind option DEFER(PREPARE) automatically. However, when you specify REOPT(ONCE), DB2 determines the access path for a statement only once (at the first run time).
You cannot use REOPT(ALWAYS) with the KEEPDYNAMIC(YES) option.
- REOPT(ONCE) is ignored if you use it with static SQL statements because DB2 for z/OS® caches only dynamic statements.
- If a dynamic statement in a plan or package that is bound with REOPT(ONCE) runs when dynamic statement caching is turned off, the statement runs as if REOPT(ONCE) is not specified.
- You cannot use both REOPT(ONCE) and NODEFER(PREPARE).
- You can use both REOPT(ONCE) and KEEPDYNAMIC(YES).
Defaults:
Process | Default value |
---|---|
BIND PLAN | NONE |
BIND PACKAGE | NONE |
REBIND PLAN | Existing value |
REBIND PACKAGE | Existing value |
Catalog record: Column REOPTVAR of the SYSPLAN and SYSPACKAGE catalog tables.