DB2 HADR > Read From Standby and Write To Primary

Posted on 2011-10-28
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
    LVL 37

    Assisted Solution

    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
    LVL 4

    Accepted Solution

    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

    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Title # Comments Views Activity
    DB2 what is copybook? 4 506
    SQL and the "WITH UR" clause 2 2,087
    help with Oracle stored procedure with input and output parameters 8 662
    RPG to c# 3 312
    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…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now