HDR_TXN_SCOPE session environment option

Use the SET ENVIRONMENT HDR_TXN_SCOPE statement of SQL to control when a transaction commit is returned to a client application in a cluster environment.

The HDR_TXN_SCOPE session environment option has this syntax:

Read syntax diagramSkip visual syntax diagram
HDR_TXN_SCOPE session environment option

|--SET ENVIRONMENT HDR_TXN_SCOPE -'--+-ASYNC-----+--'-----------|
                                     +-NEAR_SYNC-+      
                                     '-FULL_SYNC-'      

Usage

In a cluster environment, this statement can perform the following actions:
  • Override the current setting of the HDR_TXN_SCOPE configuration parameter for the current user session.
  • Restore the effects of that onconfig file setting after a previous SET ENVIRONMENT HDR_TXN_SCOPE statement in the same session overrode the configuration parameter setting.
To use this transaction synchronization feature, set the DRINTERVAL configuration parameter to 0, and then run a SET ENVIRONMENT statement that specifies one of the following options, delimited by single ( ' ) or double ( " ) quotation marks, immediately after the SET ENVIRONMENT HDR_TXN_SCOPE keywords. The syntax diagram is simplified by omitting the " delimiters.
'ASYNC'
Asynchronous mode, where transactions do not require acknowledgement of being received or completed on the HDR secondary server before they can complete. System performance is best when a replication pair uses asynchronous mode, but if there is a server failure, data can be lost.
'FULL_SYNC'
Fully synchronous mode, where transactions require acknowledgement of completion on the HDR secondary server before they can complete. Data integrity is highest when a replication pair uses fully synchronous mode, but system performance can be negatively affected if client applications use unbuffered logging and have many small transactions.
'NEAR_SYNC'
Nearly synchronous mode, where transactions require acknowledgement of being received on the HDR secondary server before they can complete. Nearly synchronous mode can have better performance than fully synchronous mode and better data integrity than asynchronous mode. If used with unbuffered logging, SYNC mode, which is turned on when DRINTERVAL is set to -1, is the same as nearly synchronous mode.

When the DRINTERVAL configuration parameter is set to 0, the value of the HDR_TXN_SCOPE parameter determines the synchronization mode for HDR replication.

Examples of setting HDR_TXN_SCOPE

To maintain safeguards against data loss, but avoid performance problems that are caused by client applications that perform many small transactions with unbuffered logging, you can enable nearly synchronous mode by running the following statement:
SET ENVIRONMENT HDR_TXN_SCOPE 'NEAR_SYNC';
With this setting, transactions require acknowledgement of completion on the HDR secondary server before they can complete.
When the DRINTERVAL configuration parameter is set to 0, the following statement supports asynchronous HDR replication in a session with buffered or unbuffered transaction logging:
SET ENVIRONMENT HDR_TXN_SCOPE 'ASYNC';
With this setting, transactions do not require acknowledgement of being received or completed on the HDR secondary server before they can complete.
When the DRINTERVAL configuration parameter is set to 0, the following statement supports fully synchronous HDR replication in a session with buffered or unbuffered transaction logging:
SET ENVIRONMENT HDR_TXN_SCOPE 'FULL_SYNC';
With this setting, transactions require acknowledgement of completion on the HDR secondary server before they can complete.

Copyright© 2019 HCL Technologies Limited