Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


DB2 HADR > Read From Standby and Write To Primary

Posted on 2011-10-28
Medium Priority
Last Modified: 2012-06-27
Is it possible to read from an HADR Standby (read-only) database and write back to the Primary database without using a middle-man application or script?  I have some hard hitting processes that I would like to read from Standby and perform updates on the Primary database.
For example:
INSERT INTO primary.table SELECT * FROM standby.table

Open in new window

If this is possible, what is the likelihood of encountering concurrency or other issues?
Do you have any recommendations on performing such tasks?
Question by:cheeseman-support
  • 2
  • 2
LVL 37

Assisted Solution

momi_sabag earned 400 total points
ID: 37045822
what are you trying to achieve?
in order to set up hadr you need two db2 instances, and you can't use a statement that goes against two different instances at once without a middle-man application

so the answer is, no

Accepted Solution

gmarino earned 1600 total points
ID: 37045911
At first, I thought about setting up Federation between the Primary and Secondary databases - but this is not allowed.  

The following restrictions apply to a HADR database that is configured as a federated database:

    The reads on standby feature is not be supported.

Without that feature, you are left with having to make 2 separate connections to the PRIMARY and STANDBY, store the intermediate results of the select and convert that data into an insert statement.  Also, you would have to take into consideration what would happen in the event of a failover where the Primary and Standby switch roles - or worse - when one becomes unavailable.

- Greg

All reads from the STANDBY are UNCOMMITTED READs.  You will also have to deal with

Author Comment

ID: 37046104
I can't see any way to get around using a middle-man application.

On the second part of the question... is ANYONE doing anything like this?  That is to say.. Read from standby, generate insert/update/merge/etc statements and execute against the Primary.
LVL 37

Expert Comment

ID: 37046366
i don't understand why you want to do that
if the data in on the stand by, it is already in the primary as well

Author Comment

ID: 37056109
The reason I've been asked to do this is to eliminate as many reads to the Primary database for in-house applications that are not part of our ERP.  Some reads are very large transactions for generating financial and costing information.  The goal is to enhance the internal customer perception of the performance of our ERP.

We have had some DB2 performance issues for the last few months with no obvious solution.  Although we do not believe our custom applications are the cause of the DB2 performance issues, we believe they may exacerbate them.

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question