Parameter Wednesday – DB CFG – HADR_SYNCMODE

DB2 Version This Was Written For

9.7

Parameter Name

HADR_SYNCMODE

Where This Parameter Lives

Database Configuration

How To Check Value

> db2 get db cfg for sample |grep HADR_SYNCMODE
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC

OR

> db2 "select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBCFG where name='hadr_syncmode' with ur"

NAME                             VALUE            VALUE_FLAGS DEFERRED_VALUE   DEFERRED_VALUE_FLAGS DATATYPE
-------------------------------- ---------------- ----------- ---------------- -------------------- ----------------
hadr_syncmode                    NEARSYNC         NONE        NEARSYNC         NONE                 VARCHAR(16)

Description

Specifies the mode that HADR will run in if otherwise configured and started. The mode determines how primary log writes are synchronized with the standby when the HADR servers are in a peer state.

Impact

Can cause performance degradation, especially if communications between the two servers is slow, particularly in sync mode.

Default

NEARSYNC

Range/Values

NEARSYNC, ASYNC, SUPERASYNC, SYNC

Recycle Required To Take Effect?

Yes – this annoys me with HADR parameters, but like all the rest of the HADR parameters, HADR_SYNCMODE requires a recycle of the database (not the database manager) to take effect.

Can It Be Set To AUTOMATIC?

No, there is no AUTOMATIC option for this parameter. You must set it based on the requirements of your HADR implentation.

How To Change It

 db2 update db cfg for dbname using HADR_SYNCMODE NEARSYNC

Rule of Thumb

If you don’t know what to do, the default of NEARSYNC is a good choice, assuming that your HADR servers are not geographically distributed.

Tuning Considerations

Usually you make a decision on this when you are building out a system or setting HADR up for the first time, and do not change it again. It is important to understand the possible values before making a decision.

SYNC

This mode give you the least chance for data loss, but the most possibility of performance impact. It should not be used if your primary and standby are in geographically different locations. With SYNC mode, DB2 does not allow a transaction to continue until the commit has been written to the log files(disk) both on the primary and the standby server. Thus if both servers fail at the same time, committed data is guaranteed to be exactly right on both servers.

NEARSYNC

This mode is nearly as good as SYNC for data loss, but with a bit less risk of performance impact. With NEARSYNC, a trasaction can complete when data has been written to log files (disk) on the primary server, and when the data has been written to memory on the standby server. The possibility for data loss comes in if both servers were to fail at the same instant, and you get the standby back first – it would be missing anything that was in memory on the standby but had not yet made it to disk there.

This is the mode all of my clients use. In fact, I’ve never seen an implementation of HADR using any other mode.

ASYNC

ASYNC carries a significantly higher risk of data loss, but with even higher performance potential. With ASYNC, DB2 does not wait for any acknowledgement from the standby server. Instead, a transaction can complete when the data is delivered to the TCP layer on the primary machine. With this mode, any data that has not been received and applied by the standby server can be lost in event of a failure.

SUPERASYNC

This mode was more recently added to the options. (9.7 FixPack 5 or 9.5 FixPack 8), and with the release of DB2 10, I more fully understand why. From what I hear in DB2 10, we’ll be able to have several more servers added into the HADR cluster as long as they’re in SUPERASYNC mode.

With SUPERASYNC, transactions are considered complete as soon as they are written to the log files on the primary database server – just the same as it runs without HADR. So essentially there should be no situation where HADR in this mode slows down transactions. On the other hand, there is a very significant possibility of data loss – there isn’t even such a thing as a “peer” state for this mode, and you’d want to monitor your log gap very closely.

I’m intrigued with trying this with DB2 10.

Related Error Messages

War Stories From The Real World

As mentioned, NEARSYNC is the mode I’ve always seen used. There’s not much excitement with this parameter. You basically have to understand it when setting up HADR and once you get used to it, there’s not a lot of thought that has to go into it.

Link To Info Center

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0011445.html

Related Parameters

Blog Entries Related to this Parameter

HADR