DEFER and NODEFER bind options
The DEFER and NODEFER options determine whether to defer preparation for dynamic SQL statements that refer to remote objects, or to prepare them immediately.
|
|
On: BIND and REBIND
PLAN and PACKAGE Not valid for REBIND of a native SQL procedure package. |
If you defer preparation, the dynamic statement is prepared when DB2® first encounters a statement of the type EXECUTE, OPEN, or DESCRIBE that refers to the dynamic statement.
- For local binds, the package inherits the plan's option at run time.
- For remote bind the default is NODEFER(PREPARE) at the remote DB2 server.
If you specify the bind option REOPT(ALWAYS), REOPT(AUTO), or REOPT(ONCE), DB2 sets the bind option DEFER(PREPARE) automatically.
You cannot use both DEFER and NODEFER.
- NODEFER(PREPARE)
- Does not defer preparation.
- DEFER(PREPARE)
- Defers preparation.
- DEFER(INHERITFROMPLAN)
- Enables a local package to inherit the value of the DEFER option
from the plan, regardless of whether the package was bound remotely
or locally.
If you bind a package remotely with the DEFER(INHERITFROMPLAN) option and the remote server does not understand the INHERITFROMPLAN value, the server might return an error.
The DEFER(INHERITFROMPLAN) value is not applied in the following situations, because no associated plan exists:
- If you bind the application locally and then copy the package to a remote server.
- If you bind an application that uses RRSAF.
- For any packages that are created for utilities
In these cases, NODEFER(PREPARE) is in effect for the package.
DEFER(PREPARE) and distributed processing: Specify the bind option DEFER(PREPARE) to improve performance, instead of NODEFER(PREPARE), and when binding dynamic SQL for DRDA access. DB2 does not prepare the dynamic SQL statement until that statement executes. (The exception to this situation is dynamic SELECT, which combines PREPARE and DESCRIBE, regardless of whether the DEFER(PREPARE) option is in effect.) When a dynamic SQL statement accesses remote data, the PREPARE and EXECUTE statements can be transmitted over the network together and processed at the remote location. Responses to both statements can be sent back to the local subsystem together. This reduces network traffic, which improves the performance of the dynamic SQL statement.
PREPARE statements that contain INTO clauses are not deferred.
- The statement executes
- The application requests a description of the results of the statement
If you choose to defer PREPARE statements, after the EXECUTE or DESCRIBE statement, you should code your application to handle any SQL error codes or SQLSTATEs that the PREPARE statement might return. You can defer PREPARE statements only if you specify the bind option DEFER(PREPARE).
Defaults:
Process | Default value |
---|---|
BIND PLAN | NODEFER |
BIND PACKAGE | Plan value |
REBIND PLAN | Existing value |
REBIND PACKAGE | Existing value |
Catalog record: Column DEFERPREP of table SYSPLAN and column DEFERPREPARE of table SYSPACKAGE.