HADR Reads on Standby

29 July 2010


With DB2 HADR (High Availability Disaster Recovery) you can now do all of the reporting that your heart desires during prime-time processing without impacting your primary workload on DB2!  DB2 HADR now allows you to do your reporting and other read-only work on a second server without putting additional load on your primary database.  Since the read-only queries execute on a replicated copy of your database, they have no impact on the primary copy.  If you are already using DB2 HADR, then you can get use the HADR Reads on Standby if you have enough DB2 Licenses available.  I'll talk more about the licensing implications later.  I got most of the information in this document from the presentation by Dale McInnis that he gave at the 2009 IOD conference. 

If you are not already familiar with DB2 High Availability Disaster Recovery, is a feature that allows you to create a second copy of your DB2 database on a different server that is kept up to date automatically by DB2.  The configuration of the second database and replication is very easy with almost no lag in updates to the secondary in most cases.  DB2 Clients have a built-in feature that allows it to re-establish a connection to the secondary database when the primary fails and the secondary has taken over.  It has many other very good features including the ability to do rolling fix pack upgrades with only a few seconds of down time that you can read about in the link in the first sentence of this paragraph.  The HADR feature does not automatically detect failure of the primary server or activate the secondary, but free clustering software is included with DB2 can automate this task and can be configured with the “db2haicu” command.  The link above gives a nice of summary of HADR and the DB2 HADR Best Practices document gives more detail about how it works and how to configure it. 


In addition to being able to off-load reporting and ad hoc queries you can also use this feature to verify data on the secondary without having to change the primary.  This means that even if you don't need to do any of your query workload on the secondary, you can run some queries regularly to verify that the secondary data is up to date and that the secondary is functioning properly. 


What can you do with HADR Reads on Standby?


       Allow readers when using any of the three sync modes of HADR

       Readers allowed in all HADR states except Local catch up.

       Supports all types of complex read queries including cursors and nested queries.

       Supports use of internal temporary tables for read queries.

       Auditing and security can be done on the Standby.

       DB2 Workload Manager supported on the secondary.


What are the limitations HADR Reads on Standby?


       Only Uncommitted Read (UR or “dirty read” isolation level is allowed.  You can set the registry variable “DB2_STANDBY_ISO=UR” to allow all applications to run without modifications.

       HADR takeover time can be increased by up to 2 seconds if here is a heavy read workload on the secondary.

       All clients will be terminated when a replay of DDL/maintenance operations are done on the standby.

       Clients on the secondary are forced off when the standby takes over primary processing.

       Write queries are not allowed. 

       LOB, XML, LONG VARCHAR and LONG GRAPHIC reads will not work on the standby database.

       STMM is not supported on the secondary.

       Creation or declaration of user defined temp tables will not work on the secondary.


How do I enable Read on Standby?

1.    Upgrade to at least DB2 9.7 fix pack 1 or later if not already on that version.

2.    Turn on the registry variable DB2_HADR_ROS

3.    Get properly licensed.  You must have enough licenses of DB2 to cover all processor cores on both the primary and secondary servers.  Typically when you use HADR without RoS you only need to license DB2 for approximately one processor on the secondary server (actually 100 PVUs).  Since RoS allows you to use all the processing power of your secondary server, you need to license DB2 for that processing. 




Until recently the secondary copy of the HADR database could not be accessed by any applications or queries at all.  With the introduction of the Reads on Standby (RoS) feature you can now do read-only workloads on the secondary such as reporting, ad-hoc queries, queries to verify that all tables are available and ready for use in the case of emergency.  By off-loading the reporting or query workload onto the secondary, you can get better performance on the primary server for your primary workload or allow for more primary work to be done without increasing primary server capacity.  This allows you to make better use of your existing investments.



HOME | Search