Get the most from the DB2 HADR standby database

Using the reads on standby capability

DB2® High Availability Disaster Recovery (HADR) is an easy-to-use data replication feature of IBM® DB2 for Linux®, UNIX®, and Windows® that provides a high availability (HA) solution for both partial and complete site failures. Beginning with DB2 V9.7 Fix Pack 1, the standby database permits read access from user applications. This article explains how this capability can be used for read applications and what the current limitations are. In addition, it includes suggestions for how you can utilize the potential of the standby database.

Share:

Samir Katti (svkatti@us.ibm.com), Software Test Specialist, IBM

Author photoSamir Katti has worked as a functional verification engineer for DB2 relational database for the last 3.5 years. He has been an active member of the team that verifies the HADR - reads on standby feature. He holds a Master's degree in Computer Science from Oregon State University.



Jing Xu (xujingxj@cn.ibm.com), Software Developer, IBM

Author photoJing Xu is a Software Developer who has worked in DB2 for Linux, UNIX and Windows development and testing in IBM China Lab for the last 4 years. He took part in FVT (functionality verification testing) for the HADR - reads on standby feature, and is now working on HADR development.



17 May 2012

Also available in Chinese Russian Vietnamese

Introduction and background

The DB2 for Linux, UNIX, and Windows High Availability Disaster Recovery (HADR) feature is a database replication feature that provides a high availability solution for both partial and complete site failures. HADR protects against data loss by replicating data changes from a source database called the primary, to a target database called the standby.

HADR was introduced in DB2 V8.2. Before DB2 V9.7 Fix Pack 1, the standby server only rolled forward the log records shipped from primary server, and was offline to users. No user connections were allowed on the standby database.

Starting with DB2 V9.7 Fix Pack 1, the reads on standby feature (HADR RoS) enables read-only applications to access the standby database. This is possible only when the reads on standby feature is enabled. With this feature read/write applications can access the HADR primary database, and read only applications can access the primary or the standby databases. This enables you to offload some of the read-only workload running on the primary to the standby. Applications connecting to the standby database do not affect the availability of the standby in case of a failover.

This article explains how to set up the reads on standby feature for reading on standby, and discusses the limitations of reading from current standby. In addition, it includes suggestions for utilizing the potential of the standby database. The article assumes the reader is familiar with setting up an HADR pair.

Setting up reads on standby

With the reads on standby feature, the standby database of an HADR pair can be used to support read only applications. The standby can be enabled in a read mode by setting the value of registry variable DB2_HADR_ROS. The registry variable is set to ON to enable reads on standby. The setting of the registry variable is not dynamic. In other words, the standby server must be stopped and restarted for the change to the registry variable to take effect. If, in the event that the standby becomes a primary due to a takeover operation, the registry variable will not have any effect on the new primary. The read capability is supported for the HADR sync modes: ASYNC, NEARSYNC, SYNC and SUPERASYNC. Read capability is not supported when the standby is in local catch-up state.

Perform the following steps to set up DB2_HADR_ROS.

  1. After setting up the HADR pair, check to see if DB2_HADR_ROS is set.
    db2 => !db2set
  2. Connecting to the standby database should yield SQL1776 rc=1, as shown in Listing 1.
    Listing 1. Connect to standby database
    db2 => connect to hadrdb
    SQL1776N  The command is not supported on an HADR standby database or on an
    HADR standby database with the current configuration or state.  Reason code =
    "1".
  3. Set the DB2_HADR_ROS variable, as shown in Listing 2.
    Listing 2. Set variable
    db2 => !db2set DB2_HADR_ROS=ON
    db2 => !db2set
    DB2_HADR_ROS=ON
  4. Bounce the server for the registry variable to take effect, as shown in Listing 3.
    Listing 3. Bounce the server
     db2 => deactivate db hadrdb
    DB20000I  The DEACTIVATE DATABASE command completed successfully.
    db2 => !db2stop
    SQL1064N  DB2STOP processing was successful.
    db2 => !db2start
    SQL1063N  DB2START processing was successful.
  5. Activate and connect to the standby database, as shown in Listing 4.
    Listing 4. Activate and connect
    db2 => activate db hadrdb
    DB20000I  The ACTIVATE DATABASE command completed successfully.
    db2 => connect to hadrdb
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 9.7.5
     SQL authorization ID   = KATTI
     Local database alias   = HADRDB

Verify HADR setup using RoS

Users generally want to verify that the HADR setup is working as expected and that there has been no data loss after the HADR pair is configured. Before DB2 V9.7 FP1, you had to use TAKEOVER to make the standby server become the new primary server in order to verify an HADR setup. No connections were allowed on the standby server, since it is offline to users. Thus it was difficult to check data on standby. The only way to bring standby online was TAKEOVER.

The following process was required to verify HADR before RoS was available.

  1. On primary side, make some changes, such as creating a table and inserting values, as shown in Listing 5.
    Listing 5. Changes to primary server
    [1049] [xjcd@db2eng63] /home/xjcd
    => db2 connect to hadrdb
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 9.7.5
     SQL authorization ID   = XJCD
     Local database alias   = HADRDB
    
    [1050] [xjcd@db2eng63] /home/xjcd
    => db2 "create table t1(c1 int)"
    DB20000I  The SQL command completed successfully.
    
    [1051] [xjcd@db2eng63] /home/xjcd
    => db2 "insert into t1 values(123)"
    DB20000I  The SQL command completed successfully.
    
    [1052] [xjcd@db2eng63] /home/xjcd
    => db2 connect reset
    DB20000I  The SQL command completed successfully.
  2. On the standby side, run TAKEOVER HADR and check the changes made on the primary server, as shown in Listing 6.
    Listing 6. Checking the changes on the standby server
    [898] [xjcd@db2eng64] /home/xjcd
    
    => db2 takeover hadr on db hadrdb 
    DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.
    
    [899] [xjcd@db2eng64] /home/xjcd
    
    => db2 connect to hadrdb
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 9.7.5
     SQL authorization ID   = XJCD
     Local database alias   = HADRDB
    
    [900] [xjcd@db2eng64] /home/xjcd
    
    => db2 "select * from t1"
    
    C1         
    -----------
            123
    
      1 record(s) selected.

With read on standby enabled after DB2 V97 FP1, verifying the HADR setup becomes much easier. Connections can be established directly to the standby, so no takeover is required.

Perform the following steps to verify HADR using RoS.

  1. On the primary side, make some changes to the database, as shown in Listing 7.
    Listing 7. Making changes to the primary database
    [1049] [xjcd@db2eng63] /home/xjcd
    => db2 connect to hadrdb
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 9.7.5
     SQL authorization ID   = XJCD
     Local database alias   = HADRDB
    
    [1050] [xjcd@db2eng63] /home/xjcd
    => db2 "create table t1(c1 int)"
    DB20000I  The SQL command completed successfully.
    
    [1051] [xjcd@db2eng63] /home/xjcd
    => db2 "insert into t1 values(123)"
    DB20000I  The SQL command completed successfully.
    
    [1052] [xjcd@db2eng63] /home/xjcd
    => db2 connect reset
    DB20000I  The SQL command completed successfully.
  2. Connect to standby directly after RoS is enabled, and check for the changes made on the primary database, as shown in Listing 8.
    Listing 8. Checking for changes on the standby database
    [910] [xjcd@db2eng64] /home/xjcd
    => db2 connect to hadrdb
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 9.7.5
     SQL authorization ID   = XJCD
     Local database alias   = HADRDB
    
    [911] [xjcd@db2eng64] /home/xjcd
    
    => db2 "select * from t1"
    
    C1         
    -----------
            123
    
      1 record(s) selected.

Replay only window on the standby database

When an HADR active standby database is replaying DDL log records or maintenance operations, the standby enters the replay-only window. When the standby is in the replay-only window, existing connections to the standby are terminated and new connections to the standby are blocked (SQL1776N Reason Code 4). New connections are allowed on the standby after all of the transactions that issued DDL or maintenance operations have completed. Existing applications are forced off at the outset of the replay-only window, and an error is returned (SQL1224N). The replay only window status can be obtained by executing the db2pd -db db_name -hadr command on the standby database.

Perform the following steps.

  1. After setting up the HADR pair and establishing a connection to the standby database, issue the following command, shown in Listing 9, to obtain the current active applications on standby.
    Listing 9. Obtaining the status of applications on the standby database
    db2 => list applications
    Auth Id  Application    Appl.      Application Id            DB       # of
             Name           Handle                               Name     Agents
    -------- -------------- ---------- --------------            -------- -----
    KATTI    db2bp          13         *LOCAL.katti.120305194800 HADRDB   1
  2. Check to see if the replay only window is active or inactive on the standby database, as shown in Listing 10.
    Listing 10. Checking replay only window
    db2 => !db2pd -db hadrdb -hadr
    
    Database Partition 0 -- Database HADRDB -- Active Standby -- Up 0 days 00:05:37 – 
    Date 03/05/2012 11:50:58
    
    HADR Information:
    Role    State                SyncMode   HeartBeatsMissed   LogGapRunAvg (bytes)
    Standby Peer                 Sync     0                  3298
    
    ConnectStatus ConnectTime                           Timeout
    Connected     Mon Mar  5 11:45:26 2012 (1330976726) 120
    
    ReplayOnlyWindowStatus ReplayOnlyWindowStartTime             MaintenanceTxCount
    Inactive                       N/A                                   0
    
    LocalHost                                LocalService
    grebe                                    DB2_katti
    
    RemoteHost                               RemoteService      RemoteInstance
    petrel                                   xkatti             katti
    
    PrimaryFile  PrimaryPg  PrimaryLSN
    S0000001.LOG 13         0x000000000235DFB6
    
    StandByFile  StandByPg  StandByLSN         StandByRcvBufUsed
    S0000001.LOG 13         0x000000000235DFB6 0%
  3. Issue an uncommitted transaction containing DDL on the primary database, as shown in Listing 11.
    Listing 11. Issuing an uncommitted transaction
    db2 => update command options using c off
    DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.
    
    db2 => create table t1(c1 int, c2 int)
    DB20000I  The SQL command completed successfully.
  4. Check to see if the replay only window is active, as shown in Listing 12.
    Listing 12. Checking the replay only window
    db2 => !db2pd -db hadrdb -hadr
    
    Database Partition 0 -- Database HADRDB -- Active Standby -- Up 0 days 00:10:26 
    -- Date 03/05/2012 11:55:47
             
    HADR Information:
    Role    State                SyncMode   HeartBeatsMissed   LogGapRunAvg (bytes)
    Standby Peer                 Sync       0                  1441
    
    ConnectStatus ConnectTime                           Timeout
    Connected     Mon Mar  5 11:45:26 2012 (1330976726) 120
    
    ReplayOnlyWindowStatus  ReplayOnlyWindowStartTime             MaintenanceTxCount
    Active                          Mon Mar  5 11:54:09 2012 (1330977249) 1
    
    LocalHost                                LocalService
    grebe                                    DB2_katti
    
    RemoteHost                               RemoteService      RemoteInstance
    
    petrel                                   xkatti             katti
    
    PrimaryFile  PrimaryPg  PrimaryLSN
    S0000001.LOG 15         0x000000000235FCFC
    
    StandByFile  StandByPg  StandByLSN         StandByRcvBufUsed
    S0000001.LOG 14         0x000000000235EE8A 0%
  5. See if existing applications have been kicked off, as shown in Listing 13.
    Listing 13. Existing applications
    db2 => list applications
    SQL1611W  No data was returned by Database System Monitor.
  6. Check to see if new connections are blocked, as shown in Listing 14.
    Listing 14. New connections
     db2 => connect to hadrdb
    SQL1776N  The command is not supported on an HADR standby database or on an
    HADR standby database with the current configuration or state.  Reason code =
    "4".

So the replay windows obviously have an impact on user business on the standby database. If RoS is enabled on standby, ensure that you do the following.

  • Plan to have all the operations that will generate replay only windows occur together in a short period of time on the primary server.
  • Enable automatic commit on the primary server so that the replay only window can be as short as possible.
  • Turn off the automatic maintenance features on both the primary and standby servers, as shown in Listing 15.
    Listing 15. Turning off the automatic maintenance features on primary and standby databases
    => db2 UPDATE DATABASE CFG FOR hadrdb USING AUTO_MAINT OFF AUTO_RUNSTATS OFF 
           AUTO_REORG OFF 
    DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

For the full list of DDL statements and maintenance operations, please refer to DB2 Information Center link in the Resources section for more information on this article.


Binding packages to the standby database

Usually, an application containing embedded SQL needs to be pre-compiled to a source file of the host language with the DB2 APIs, and then bound to the corresponding database. You can use the db2 CLP command PREP to pre-compile embedded SQL applications. By default, the package will be created automatically at pre-compile time. Optionally, you can specify BINDFILE option in PREP command, so that a bind file(.bnd) will be generated, and after pre-compilation, the BIND utility can be used to create a package in the database for this application with the bind file. When the structure or statistics of the tables that an embedded SQL application accesses are changed, the application needs to be rebound explicitly or implicitly.

Perform the following steps for pre-compile and bind.

  1. Run the PREP command with BINDFILE option to generate a bind file (sample.bnd), as shown in Listing 16.
    Listing 16. PREP command with BINDFILE option
    => db2 prep sample.sqc bindfile
    
    LINE    MESSAGES FOR sample.sqc
    ------  --------------------------------------------------------------------
            SQL0060W  The "C" precompiler is in progress.
            SQL0091W  Precompilation or binding was ended with "0" 
                      errors and "0" warnings.
  2. Use the BIND utility to create the package in database, as shown in Listing 17.
    Listing 17. Using the BIND utility
     => db2 bind sample.bnd
    
    LINE    MESSAGES FOR sample.bnd
    ------  --------------------------------------------------------------------
            SQL0061W  The binder is in progress.
            SQL0091N  Binding was ended with "0" errors and "0" warnings.

Note, both pre-compile and bind require you to be connected to the database.

As previously discussed, no writes are allowed on the HADR standby database. Therefore, bind and rebind are not allowed on standby database either since they will write to the database. When you are trying to bind a package to the standby database, error SQL1773N reason code 5 will be reported as shown in Listing 18.

Listing 18. Errors when bind on standby database
=> db2 bind sample.bnd 

LINE    MESSAGES FOR sample.bnd
------  --------------------------------------------------------------------
        SQL0061W  The binder is in progress.
        SQL1773N  The statement or command requires functionality 
                  that is not supported on a read-enabled HADR standby 
                  database. Reason code = "5".
        SQL0082C  An error has occurred which has terminated 
                  processing.
        SQL0092N  No package was created because of previous errors.
        SQL0091N  Binding was ended with "3" errors and "0" warnings.

When you need to bind or re-bind the package on the standby database, you must bind/re-bind on the primary database. The bind/re-bind operation is shipped and replayed on the standby server since there will be log records written for them.

After bind/re-bind, you can copy the executable binary of the application to the server where the standby database is located, and then run the application. But, pay attention to the bind and re-bind commands. They will trigger the Replay Only window on standby.


Isolation level on standby

Readers on standby database can only run in the Uncommitted Reads(UR) isolation level. The reason for this limitation is that DB2 HADR is based on log shipping. As you know, transactions on the primary server will produce log records, and the log records will be shipped to the standby database. The standby database re-does the log records, the same as rolling forward after a restore database, to ensure data consistency with the primary database. But the locks required for higher isolation levels than UR are not shipped to the standby database, and the readers will not acquire any locks while replaying the log records. In a word, data on the standby database is not protected by any locks, so applications on standby can only read in UR isolation level.

Listing 19. Error when reads in higher isolation than UR
=> db2 "select * from t1 with RR"

C1         
-----------
SQL1773N  The statement or command requires functionality that is not 
supported on a read-enabled HADR standby database. Reason code = "1".

If some existing read-only applications are implemented in higher isolations than UR, and you don't want them to be broken due to this isolation level error, you can just coerce them running in the UR isolation level quietly by setting the DB2_STANDBY_ISO registry variable, as shown in Listing 20.

Listing 20. Error when reads in higher isolation than UR
=> db2set DB2_STANDBY_ISO=UR
# We need to restart DB2 instance here so that this registry variable take effect
=> db2 connect to testdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = XJCD
 Local database alias   = TESTDB

=> db2 "select * from t1 with RR"

C1         
-----------
          1
          2
          3

  3 record(s) selected.

For the read-only applications that need more than UR isolation level, you can only run them on the primary database.


Statistics on the standby database

Statistics of database objects are required by the SQL optimizer when generating access plans. If you don't have accurate statistics, the optimizer may choose an access plan that does not have the lowest cost, and query performance will be affected.

The RUNSTATS utility is used to collect statistics in DB2. It will sample on the specified object (tables or indexes), and calculate the statistics, then write the results into the system catalog tables whose schema is SYSSTAT. Because write operations are not allowed on HADR standby database, the RUNSTATS utility is not available on standby either.

In case you want to update the statistics on standby to improve query performance for the applications on standby database, you have the following alternatives.

  • Execute RUNSTATS on the primary database. Yes, the most essential and easiest way is to run RUNSTATS on primary. Since RUNSTATS will write the results into the system catalog tables, corresponding log records will be generated, and they will be shipped to standby, and will be replayed. Therefore, the statistics on standby database will be updated.
  • Manually update the statistics catalog table directly. However, it is obvious that RUNSTATS in solution 1 will have some performance impact on primary database. Some fields in the statistics catalog tables can be updated, so you can update the statistics manually as you need. But, there will be some risk to update the catalog tables, so take more care if this solution is adopted.
  • Use the optimizer profile to indicate a plan to the optimizer. At the same time, it is possible that some conditions on primary and standby are not identical, even though this is not recommended. For example, one tablespace on the primary database has a container with better performance than standby, the statistics on the primary are not applicable on standby. In this circumstance you have to make a different access plan on primary than on standby for the same query.

You can specify optimization guidelines for queries, either in-line guidelines or optimization profiles. Usually, you insert the optimization profiles (XML files) into SYSTOOLS.OPT_PROFILE table, and then enable the profiles before the queries. The DB2 optimizer will generate and choose an access plan according to the optimization profiles. For more details on using optimization profiles, refer to the "Influence query optimization with optimization profiles and statistical views in DB2 9" article that is in the Resources section for more information.

Because write operations are blocked on the standby databases, you can not insert the optimization profiles into SYSTOOLS.OPT_PROFILE on standby. The work around would be insert into the profiles on primary, and then the corresponding log records will be shipped to standby. After the log records are replayed, the optimization profiles will be in standby database, and ready to use.


Handling LOBs on standby servers

Before V9.7 Fix Pack 5, no large objects (LOBs) such as BLOB, CLOB, or DBCLOB were allowed on reads on standby database. All LOBs were stored in a separated tablespace from other non-LOBs columns before DB2 V9.7. When you are trying to read a LOB, you will get a return code of SQL1773N, reason code 1, as shown in Listing 21.

Listing 21. Non-inline LOBs are blocked on standby
=> db2 "select C2 from T"  

C2
-----------------------
SQL1773N  The statement or command requires functionality that is not 
supported on a read-enabled HADR standby database. Reason code = "1".

In DB2 V9.7, inline LOBs were introduced. These LOBs are stored in the same tablespaces as non-LOB columns. In V9.7 Fix Pack 5 and later, inline LOBs are supported on standby. So, for small LOBs, you should make them inline if possible. To check if LOB column in a row is inline or not, ADMIN_IS_INLINED table function can be used, as shown in Listing 22.

Listing 22. Check if a LOB column is inline or not
=> db2 "select ADMIN_IS_INLINED(c1) from t2"

1     
------
     1

  1 record(s) selected.

A query result of 1 means this column of this row is inline, and 0 means it is not inline.


Conclusion

Beginning with DB2 V9.7 Fix Pack 1, DB2 provides reads on standby capability with the HADR feature. You can put the read only applications on the standby database. This capability can help you to balance the workload on the primary server. Due to the log shipping and replay mechanisms, there will be some limitations on standby for read only applications. This article described these limitations and showed how to make applications on the standby database run effectively, including the following.

  • Set DB2_HADR_ROS=on registry variable to enable the RoS feature.
  • Make transaction containing DDL statements as short as possible on the primary server to reduce the impact of the replay only window on the standby server.
  • Bind packages on the primary server, and run corresponding applications on the standby server.
  • Use Uncommitted Read (UR) isolation level for applications on the standby server.
  • Make short LOBs inline if possible so that they can be accessed on the standby database.

We hope that you can utilize the RoS feature more effectively and easily after reading this article.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
  • Try an evaluation copy of DB2 for Linux, UNIX, and Windows.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=815614
ArticleTitle=Get the most from the DB2 HADR standby database
publish-date=05172012