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.
IMMEDWRITE |
|
On: BIND and REBIND PLAN and PACKAGE, and REBIND TRIGGER PACKAGE Not valid for REBIND of a native SQL procedure package. |
- ( 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.
- ( 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.
- ( 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.
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 |
Interactions 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.
Performance implications: 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.
- 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.
Defaults:
Process | Default value |
---|---|
BIND PLAN | NO |
BIND PACKAGE |
|
REBIND PLAN | Existing value |
REBIND PACKAGE | Existing value |
REBIND TRIGGER PACKAGE | Existing value |