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.

  • NODEFER
  • DEFER
  • NODEFER(PREPARE)
  • DEFER(PREPARE)
  • Start of changeDEFER(INHERITFROMPLAN)End of change
 On: BIND and REBIND PLAN and PACKAGE

Start of changeNot valid for REBIND of a native SQL procedure package.End of change

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 BIND and REBIND PACKAGE, if neither option is specified, and REOPT(NONE) applies:
  • 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.
Start of change DEFER(INHERITFROMPLAN) End of change
Start of changeEnables 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.

End of change

Start of change 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. End of change

PREPARE statements that contain INTO clauses are not deferred.

To defer the preparation of an SQL statement in an application, bind or rebind the application with the option DEFER(PREPARE). This defers PREPARE messages for SQL statements that refer to a remote object until either:
  • 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.