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
  • ( NONE )
  • ( ALWAYS )
  • ( ONCE )
  • ( AUTO )
 On: BIND and REBIND PLAN and PACKAGE

Start of changeNot valid for REBIND of a native SQL procedure package.End of change
Start of changeFor dynamic statements, the REOPT bind option also controls whether DB2 considers the literal values for access path selection when CONCENTRATE STATEMENTS WITH LITERALS is specified when statements are prepared. The literal values are considered only when REOPT(ONCE) or REOPT(AUTO) is specified.End of change
(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.

The following restrictions apply to REOPT(ONCE):
  • 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.