db2Dean a.k.a. Dean Compher a.k.a “The Dean of DB2” is here to answer your DB2 Questions !
|
Utah Events
Nevada Events db2Dean’s IOD 2009 Trip Report Oct 24-28, 2010: IBM IOD Conference
|
Just for Grins Watch:
|
|
|
Online Events
Other Events
|
Welcome to db2Dean’s web site. I’m Dean Compher an IBM IT Specialist who, along with my team, helps customers and prospective customers with DB2 on Linux, UNIX and Windows (LUW) technical questions and issues. As this page makes painfully clear, I am a DBA and not a web designer, but I would be happy to get your DB2 questions answered or talk to you about the great features of DB2 or IBM Integration Products. If you are looking at a new database solution or want to compare us to your existing database vendor, please do not hesitate to contact me about getting a presentation or just to ask questions. My e-mail address is dean@db2Dean.com
I am located in Utah and primarily serve DB2/LUW customers in Utah, Nevada, Colorado and Montana, but I can forward requests to my peers in other technology and geographic areas as well. My team also covers Informix and products. There are questions that I get on a regular basis, and I will write articles relating to them here. I hope that you find them useful. I also welcome suggestions for future content. Click here for more information about me.
|
|
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.
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.
|
Db2Dean’s Other Interests
Mary Lou’s Stained Glass
|