Posted by 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
Related Parameters
Blog Entries Related to this Parameter
Hi Ember,
I think you’ve got a typo error in this article.
“db2 update dbm cfg for dbname using HADR_SYNCMODE NEARSYNC” did not work for me.
A
$ db2 update db cfg for test04 using HADR_SYNCMODE SYNC
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
db2 get db cfg for test04 | grep HADR_SYNCMODE
HADR log write synchronization mode (HADR_SYNCMODE) = SYNC
was successfull.
Regards Dieter
Thank you for catching that. I’ve corrected it in the post.
The IBM documentation states for configuring the HADR_SYNCMODE parameter, a reactivation of the database or a stop/start of HADR will activate the parameter.
I haven’t tried it yet, but this would be good for DB’s that have to be online 24/7.
“Changes to this parameter take effect on database activation. If the database is already online, you can have changes take effect by stopping and restarting HADR on the primary database”
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0011445.html
I haven’t tried it yet,
Yes, this came along in 10.5, Fix Pack 4, I believe, and it generally works.