Posted by With DB2 9.7 Fixpack 1, IBM started supporting reads on the HADR standby. This was a bit of an exciting feature for some of us. But in practice there are enough restrictions that it ends up not being an option that many clients choose.
Reporting Server
The main thing my clients want from this feature is to be able to separate their real-time reporting workload from their e-commerce workload. One of the problems with that is that both workloads are considered production workloads – so in case of a failover for maintenance or failure, suddenly you have only one server doing the work you previously had spread across two database servers. This usually translates to no server for the reporting workload. This alone is enough for some clients to shy away, and there’s no way to get around this restriction – it’s in the architecture, not in DB2. There is an interesting white paper on automatically maintaining reporting access: http://www-01.ibm.com/support/docview.wss?uid=swg27020912&aid=1
Indexing for Reports
If clients get past that, the next problem is that for a reporting server, you often want different indexes than you want for an e-commerce workload. With Reads On Standby, you cannot create any indexes on the standby that are not on the primary, and reporting indexes are often not a good idea on your primary e-commerce database. Along with this is the restriction that you cannot have any Declared Global Temporary Tables – which can be a requirement.
Restrictions for Reads On Standby
There are also a lot of restrictions on availability for the reporting/standby server. The most notable of these are:
- LOBs that are not inlined cannot be queried on the standby server
- During a reorg, no queries can be executed on the standby
- During any DDL operation(add index, alter table, etc), no queries can be executed on the standby
That is really just scratching the surface. The full page dedicated to restrictions in the info center is: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.ha.doc/doc/c0054258.html. On my screen, it’s a full screen of restrictions to be aware of.
Licensing
Finally, usually an HADR standby server must only be licensed at 100 PVU (please verify this with IBM before relying on this information), and when you enable Reads on Standby, it must be fully licensed based on the PVU count of the server.
With DB2 10.1 with multiple standbys, reads can be enabled on all of them.
Can you post something on implementing ROS in a multiple standby for db2 10.5 environment. It would be a good information for everyone.