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.