IMMEDWRITE bind option

The IMMEDWRITE option indicates whether immediate writes are to be done for updates that are made to group buffer pool dependent page sets or partitions.

Command option Option values Used with
IMMEDWRITE
  • (NO )
  • (YES )
  • (INHERITFROMPLAN ) 1
Note:
  1. The IMMEDWRITE(INHERITFROMPLAN) option is not valid for REBIND of packages for native REST services.
  2. The bind option is not valid for REBIND of packages for native SQL procedures or advanced triggers.

This option is only applicable for data sharing environments. The IMMEDWRITE subsystem parameter has no effect on the IMMEDWRITE bind option at bind time. The following table shows the implied hierarchy of this option as it affects run time.

Option descriptions for IMMEDWRITE

IMMEDWRITE(NO)
Specifies that normal write activity is done. Updated pages that are group buffer pool dependent are written at or before phase one of commit or at the end of abort for transactions that have rolled back.
IMMEDWRITE(YES)
Specifies that updated pages that are group buffer pool dependent are immediately written as soon as the buffer update completes. Updated pages are written immediately even if the buffer is updated during forward progress or during rollback of a transaction. Specifying this option might impact performance.
IMMEDWRITE(INHERITFROMPLAN)

Enables a local package to inherit the value of the IMMEDWRITE option from the plan, regardless of whether the package was bound remotely or locally.

If you bind a package remotely with the IMMEDWRITE(INHERITFROMPLAN) option and the remote server does not understand the INHERITFROMPLAN value, the server might return an error.

The IMMEDWRITE(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, IMMEDWRITE(NO) is in effect for the package.

Table 1. The implied hierarchy of the IMMEDWRITE option
IMMEDWRITE bind option IMMEDWRI subsystem parameter Value at run time
NO NO NO
NO PH1 PH1
NO YES YES
PH1 NO PH1
PH1 PH1 PH1
PH1 YES YES
YES NO YES
YES PH1 YES
YES YES YES
Note: The NO and PH1 options are equivalent. The PH1 option is shown for backward compatibility only.

Start of changeInteractions with the PLANMGMT option: If you plan to change this option and the PLANMGMT option in a REBIND command, see PLANMGMT bind option for the implications.End of change

Performance implications of IMMEDWRITE

You can use IMMEDWRITE(NO) and IMMEDWRITE(YES) for situations where a transaction spawns another transaction that can run on another Db2 member and that depends on uncommitted updates that were made by the originating transaction.

Specify IMMEDWRITE(NO) to cause group buffer pool dependent pages to be written at or before phase 1 of commit.

Specify IMMEDWRITE(YES) to cause the originating transaction to immediately write its updated GBP-dependent buffers (instead of waiting until the end of commit or rollback), which will ensure that the dependent transaction always gets the same results regardless of whether it runs on the same member or a different member as the originating transaction. IMMEDWRITE(YES) should be used with caution because of its potential impact to performance. The impact will be more significant for plans and packages that do many buffer updates to GBP-dependent pages, and not as noticeable for plans or packages that perform few buffer updates to GBP-dependent pages. The following options can be considered as alternatives to using IMMEDWRITE(YES):
  • Always run the dependent transaction on the same Db2 member as the originating transaction.
  • Run the dependent transaction with ISOLATION(RR).
  • Wait until the completion of phase two of commit before spawning the dependent transaction.
  • CURRENTDATA(YES) or ISOLATION(RS) can be used to solve the problem only if the originating transaction updates columns that are not in the WHERE clause of the dependent transaction.

Default values for IMMEDWRITE

Process Default value
BIND SERVICE NO
BIND PLAN NO
BIND PACKAGE
  • For a local server: INHERITFROMPLAN
  • For a remote server: NO
REBIND PLAN Existing value
REBIND PACKAGE Existing value
REBIND TRIGGER PACKAGE Existing value