Utilizing the DB2 HADR reads on standby feature in a business intelligence environment

The DB2® High Availability and Disaster Recovery (HADR) feature is a database replication method that provides a high-availability solution for partial and complete site failures. HADR protects against data loss by replicating data changes from a source database to a target database. The HADR reads on standby (HADR ROS) feature, allows read-only applications to access the HADR Primary or Standby database. This enables an organization to offload some of the read-only workloads running on the Primary to the Standby database. This article shows a practical application of this HADR ROS feature in conjunction with Virtual IP addresses to ensure continued, automatic connectivity in an business intelligence environment.

Aruna De Silva (adesilva@ca.ibm.com), Testing Support Specialist, IBM

Aruna De Silva  photoAruna De Silva is a member of the Quality Assurance (QA) team for DB2 on Linux, Unix, and Windows product at the IBM Toronto software laboratory. Aruna is an IBM Certified Advanced Database Administrator for DB2 9. In addition, he holds numerous IT certifications from IBM and other vendors in database, hardware, and systems administration. Aruna is actively involved in DB2 product integration, implementing and testing complex product stacks, and designing and testing HA and DR solutions. He is also the key contact person for EMC Storage within IBM Information Management Software division. He holds a Bachelor of Science degree in Computer Science from York University.



Bhuvana Balaji (bhuvana_balaji@us.ibm.com), DB2 Next Generation Data Analytics-Cognos/ROLAP QA Lead, IM Technical Quality Champion, IBM

Bhuvana Balaji is a member of the Quality Assurance (QA) team for InfoSphere Warehouse and DB2 on Linux, UNIX, and Windows products at the IBM Silicon Valley Laboratory, San Jose, Calif. Bhuvana Balaji has been with IBM since 1999 working in the BI/Analytics/Warehousing area with experience in advanced technical support, solutions development and system stress, and stability and performance testing. Bhuvana is actively involved in leading QA efforts toward designing, developing, implementing, and testing complex and large-scale warehouse implementations supporting end-to-end business intelligence/analytics scenarios. She has practical experience in warehouse data modeling and end-user analytics (reports) development using IBM Cognos products. She holds dual master's degrees in mathematics from University of Cincinnati, and from the Indian Institute of Technology.



Steve Raspudic, DB2 Software Developer, I.B.M.

Steve Raspudic photoSteve Raspudic has worked in the IBM Toronto Lab for over ten years, and has held numerous roles throughout the development, consulting, and service organizations. His recent interests are DB2 HA, DB2 HADR, and other HA technologies for DB2. Most recently, he drove the integration of HA Clustering into the core DB2 engine and is currently the manager of the DB2 High Availability team at the Toronto Lab.



24 January 2013

Introduction

The objective of this article is to showcase how to approach automating HADR in business intelligence (BI) environments using IBM® DB2 10.1 for Linux®, UNIX®, and Windows®.

The following three areas are addressed in this article:

  1. Using DB2 HADR automation to provide high availability to BI applications.
  2. Utilizing the new HADR reads on standby (ROS) feature first introduced in DB2 9.7 Fixpack 1.
  3. Maximizing overall performance of a BI solution by distributing the certain OLAP workloads between HADR Primary and ROS servers.

The concepts and instructions presented here are specific to only a single node or a non-partitioned environment. It is not applicable to a DPF environment.


BI environment used to exploit DB2 HADR reads on standby feature

A typical BI environment will have the following functional components creating an end-to-end data flow, as shown in Figure 1.

Figure 1. A general BI topology and workflow
Image shows general BI topology and workflow

The above BI topology is implemented by using following IBM products:

  • DB2 HADR Data Servers— IBM DB2 10.1 for Linux, UNIX, and Windows
  • BI Application Servers— IBM Cognos 10 Business Intelligence Server 32-Bit 10.1.0 for Windows Multilingual
  • BI Client Applications (such as modeling tools)— IBM Cognos 10 Business Intelligence Modeling 10.1.0 for Windows
Figure 2. BI topology and workflow used to showcase HADR reads on standby feature
Image shows BI topology and workflow used to showcase HADR reads on standby feature

HADR system configuration

Our HADR configuration: two servers named ma3455lnx04 and ma3455lnx05. The two HADR DB2 instances are named db2inst1 (on node ma3455lnx04) and db2inst1 (on node ma3455lnx05). The HADR database being replicated between these two instances is named TPCDS10G.

Overview of the HADR ROS setup and configuration process

Following is a brief overview of the process used to set up HADR ROS and integrate it into our BI test environment:

  1. In our solution, a subset of TPC-DS benchmark workload is used to create the HADR data source. TPC-DS is essentially a decision support benchmark that models several generally applicable aspects of a decision support system, including queries and data maintenance. The underlying business model of TPC-DS is a retail product supplier, with the following schema structure:
    • Four subject areas: Catalog, Web, Store, Inventory
    • Seven fact tables: two fact tables for each subject area
    • 15 dimension tables: most shared across subject areas (e.g., Item) and some specific to subject area (e.g., Store)
    • 24 tables total

    Refer to TPC organizations for the benchmark details and how to obtain the TPC-DS workload.

  2. Setup HADR and create the initial cluster using the db2haicu tool. Please follow the instructions in the whitepaper Automated Cluster Controlled HADR (High Availability Disaster Recovery) Configuration Setup using the IBM DB2 High Availability Instance Configuration Utility (db2haicu) (see Resources). The XML input file is included to create the cluster for your reference as part of downloads. Since virtual IP/service IP is used to access the HADR databases, you need to use identical DB2 listener port values for both instances. With automatic client reroute (ACR), you can have different listener ports on primary and standby servers.

    In our HADR configuration, the following configuration parameters were set on the primary and standby databases.

Table 1. Matching scores
Database configuration parameterPrimary nodeStandby node
HADR_LOCAL_HOSTma3455lnx04ma3455lnx05
HADR_LOCAL_SVC2494324944
HADR_REMOTE_HOSTma3455lnx05ma3455lnx04
HADR_REMOTE_SVC2494424943
HADR_REMOTE_INSTdb2inst1db2inst1
HADR_SYNCMODENEARSYNCNEARSYNC
HADR_TIMEOUT120120
HADR_PEER_WINDOW120120
LOGINDEXBUILDONON

NOTE: It is important that you set the following configuration parameters after the standby database has been created: HADR_LOCAL_HOST, HADR_LOCAL_SVC, HADR_REMOTE_HOST, HADR_REMOTE_SVC, HADR_REMOTE_INST. If they are set prior to creating the standby database, the settings on the standby database will reflect what is set on the primary database.

  1. Create the HADR standby virtual IP (VIP) required for accessing the ROS node.
  2. Set up the BI environment. Our BI environment was based on all IBM products. However, this solution is general enough to be adaptable by a different BI product mix. For instructions on setup and configuration of the BI environment the respective product documentation should be consulted.
  3. Configure BI design tools and OLAP tools to utilize the HADR ROS node or both as required. The screenshots taken from key configuration steps during our BI setup are included. For any other application, as long as it is using JDBC Type 4 drivers to connect to the data source most of these steps will be similar.
  4. The main objective of this article: how to maximize your HADR configuration, by using either or both servers for query reporting (in addition to providing automated fail-over).

Preparing to configure the HADR pair for ROS

  1. Set TCP/IP as the default DRDA protocol: db2set DB2COMM=TCPIP.
  2. Disable auto-start of the instance, using the following DB2 registry variable: db2set DB2AUTOSTART=NO.
  3. Set the following two HADR reads on standby DB2 registry variables on both systems. You will need to stop and start DB2 after this.
    db2set DB2_HADR_ROS=YES
    db2set DB2_STANDBY_ISO=UR
  4. Disable automatic database maintenance on both HADR databases: db2 update db cfg for TPCDS10G using AUTO_MAINT OFF

Creating the ROS VIP for the standby node

Following is a brief description of the steps required to set up ROS VIP for the HADR standby node. Please follow the instructions in "Continuous access to Read on Standby databases using Virtual IP addresses" for detailed instructions.

NOTE: The configuration steps described in this section must be performed as root on each or both nodes as described.

  1. On both systems, create a script called hadrV10_monitor_shadow.ksh. This script must meet the following requirements:
    • Executable by root
    • Saved in the /usr/sbin/rsct/sapolicies/db2/ directory on each machine
    • Have the same permissions as the hadrV10_monitor.ksh script which should already reside in the same directory

    The hadrV10_monitor_shadow.ksh script should have the following contents:

    Listing 1. hadrV10_monitor_shadow.ksh script file
    #!/bin/ksh -p
    # THIS SCRIPT REQUIRES THE SAME INPUT PARAMETERS AS THE hadrV10_monitor.ksh SCRIPT
    # For example: hadrV10_monitor_shadow.ksh $1 $2 $3
    # $1 must be instance name at 'THIS' node
    # $2 must be instance name at 'THE OTHER' node
    # $3 must be HADR DBNAME
                            
    VIPhere=2
                            
    # su - ${1?} -c "db2pd -hadr -db ${3?}" | egrep '(^Primary |^STANDBY )' | \
    awk '{print $1 " " $2}' | read Role State
    su - ${1?} -c "db2pd -hadr -db ${3?}"   | egrep -i "HADR_ROLE" | head -1 | \
    tr "[a-z]" "[A-Z]" | awk '{print $3}' | read Role
    su - ${1?} -c "db2pd -hadr -db ${3?}"   | egrep -i "HADR_STATE" | head -1 | \
    tr "[a-z]" "[A-Z]" | awk '{print $3}' | read State
                            
    # ROS VIP is up when STANDBY is in PEER state
    if [[ X${Role} == 'XSTANDBY' && X${State} == 'XPEER' ]]; then
    VIPhere=1
    fi
                            
    # ROS VIP is also up when STANDBY is in Disconnected PEER state
    if [[ X${Role} == 'XSTANDBY' && X${State} == 'XDISCONNECTED_PEER' ]]; then
    VIPhere=1
    fi
                            
    # ROS VIP is also up when STANDBY is in Remote Catchup state
    if [[ X${Role} == 'XSTANDBY' && X${State} == 'XREMOTE_CATCHUP' ]]; then
    VIPhere=1
    fi
                            
    # ROS VIP is also up when STANDBY is in Remote Catchup PENDING state
    if [[ X${Role} == 'XSTANDBY' && X${State} == 'XREMOTE_CATCHUP_PENDING' ]]; then
    VIPhere=1
    fi
                            
    logger -i -p notice -t $0 "$1,$2,$3: Role=$Role, State=$State, Returning $VIPhere"
    return $VIPhere

    On each node, ensure that this script file is accessible and permissions are correct via:

    root> ls -altr /usr/sbin/rsct/sapolicies/db2/hadrV10_monitor_shadow.ksh
    -r-xr-xr-x 1 root root 1073 Apr 26 13:03
    /usr/sbin/rsct/sapolicies/db2/hadrV10_monitor_shadow.ksh
  2. Create two Tivoli® SA MP resources (hadr_shadow_01 and hadr_shadow_02) to monitor the HADR state by running the following commands on either of the nodes (ma3455lnx04 or ma3455lnx05) in the peer domain. In the following command, replace ma3455lnx04 with your first hostname, replace db2inst1 with your instance name on the first host, and replace TPCDS10G with the name of your database:
    Listing 2. Creating Tivoli SA MP resource hadr_shadow_01
    root> mkrsrc IBM.Application Name="hadr_shadow_01" ResourceType=0 
    NodeNameList="{'ma3455lnx04'}" UserName=root StartCommand="/bin/true"
    StopCommand="/bin/true" 
    MonitorCommand="/usr/sbin/rsct/sapolicies/db2/hadrV10_monitor_shadow.ksh 
    db2inst1 db2inst1 TPCDS10G" MonitorCommandPeriod=5
    MonitorCommandTimeout=29 RunCommandsSync=1

    In the following command, replace ma3455lnx05 with your second hostname, replace db2inst1 with your instance name on the second host, and replace TPCDS10G with the name of your database:

    Listing 3. Creating Tivoli SA MP resource hadr_shadow_02
    root> mkrsrc IBM.Application Name="hadr_shadow_02" ResourceType=0
    NodeNameList="{'ma3455lnx05'}" UserName=root StartCommand="/bin/true"
    StopCommand="/bin/true"
    MonitorCommand="/usr/sbin/rsct/sapolicies/db2/hadrV10_monitor_shadow.ksh 
    db2inst1 db2inst1 TPCDS10G " MonitorCommandPeriod=5 
    MonitorCommandTimeout=29 RunCommandsSync=1
  3. Create an equivalency of the hadr_shadow_01 and hadr_shadow_02 resources by running the following command. Replace ma3455lnx04 with the name of your first hostname, and replace ma3455lnx05 with the name of your second hostname:
    root> mkequ -p NoControl hadr_shadow_equiv 
    IBM.Application:hadr_shadow_01:ma3455lnx04,hadr_shadow_02:ma3455lnx05
  4. Create the VIP resource for the standby HADR database using the following command. Replace ma3455lnx04 with your first hostname, replace ma3455lnx05 with your second hostname, replace the IP address 9.26.92.136 with the IP address you will be using, replace 255.255.252.0 with the netmask you will be using:
    root> mkrsrc IBM.ServiceIP ProtectionMode=1 Name=ros_ip_9_26_92_136-rs
    IPAddress= 9.26.92.136 NetMask=255.255.252.0
    NodeNameList="{ma3455lnx04,ma3455lnx05}"

    NOTE: The IP address that you select for the VIP, must be reserved exclusively for the use of this configuration and must not already be assigned to any existing computer on the network. In addition, this IP address must be routable from each machine. This IP address cannot be used by any other database in the cluster (that is, the primary and standby VIPs can be associated with only one HADR pair at a time). The netmask must match exactly the netmask that is currently in use on each of the machines.

  5. Create the resource group for the VIP (which will be collocated with the HADR standby) using the following commands:
    root> mkrg hadr_ros_ip-rg
    root> addrgmbr -g hadr_ros_ip-rg \
    IBM.ServiceIP:ros_ip_9_26_92_136-rs
  6. Create the dependencies to set up collocation relationship with the shadow equivalency:
    root> mkrel -p DependsOn -S IBM.ServiceIP:ros_ip_9_26_92_136-rs -G
    IBM.Equivalency:hadr_shadow_equiv ros_do
    root> mkrel -p DependsOn -S IBM.ServiceIP:ros_ip_9_26_92_136-rs -G
    IBM.Equivalency:db2_public_network_0 ros_do_pubnw
  7. Bring the HADR ROS IP resource group online using root> chrg -o Online hadr_ros_ip-rg.

At this point, the ROS VIP address is online and should be collocated with the current location of the standby HADR database. Verify this using the lssam command. Your output should be similar to the output that follows. In particular, confirm that all states are normal (if any states are printed in red or yellow, there is likely a configuration error.) Confirm also that the VIP for the ROS is online and that it represents the standby HADR node.

Figure 3. Listing peer domain resources using lssam command
Image shows listing peer domain resources using lssam command

If both nodes are in peer mode (i.e., normal operational status):

  • Primary VIP (db2ip_9_26_92_135-rs) resource:
    • Online on the HADR primary node
    • Offline on the HADR standby node
  • Standby ROS VIP (ros_ip_9_26_92_136-rs) resource:
    • Online on the HADR standby node
    • Offline on the HADR primary node

Now that you have completed setting up the VIPs required for our BI applications, you can now proceed to re-configure the BI topology to utilize those two VIPs.

NOTE: It is recommended that real-time stats be turned off so it does not interfere with the readers on the standby.


Configuring Cognos BI server to connect to HADR database servers using VIPs

To enable the Cognos server to communicate with the databases configured as a HADR ROS pair:

  1. Collect DB2 HADR/ROS environment information
  2. Catalog data sources on the IBM Cognos® server
  3. Create or modify read-only data source connection information using Cognos administration console

Collect DB2 HADR/ROS environment information

  1. Login to one of the HADR servers as root and get the primary and ROS VIP values
    • Primary VIP value:
      [root@ma3455lnx04 haSetup]# lsrsrc -s "Name like 'db2ip%'"\
      IBM.ServiceIP Name IPAddress NodeNameList
      Resource Persistent Attributes for IBM.ServiceIP
      resource 1:
              Name         = "db2ip_9_26_92_135-rs"
              IPAddress    = "9.26.92.135"
              NodeNameList = {"ma3455lnx05"}
      resource 2:
              Name         = "db2ip_9_26_92_135-rs"
              IPAddress    = "9.26.92.135"
              NodeNameList = {"ma3455lnx04"}
      resource 3:
              Name         = "db2ip_9_26_92_135-rs"
              IPAddress    = "9.26.92.135"
              NodeNameList = {"ma3455lnx04","ma3455lnx05"}
    • ROS VIP value:
      [root@ma3455lnx04 haSetup]# lsrsrc -s "Name like 'ros%'" \
      IBM.ServiceIP Name IPAddress NodeNameList
      Resource Persistent Attributes for IBM.ServiceIP
      resource 1:
              Name         = "ros_ip_9_26_92_136-rs"
              IPAddress    = "9.26.92.136"
              NodeNameList = {"ma3455lnx05"}
      resource 2:
              Name         = "ros_ip_9_26_92_136-rs"
              IPAddress    = "9.26.92.136"
              NodeNameList = {"ma3455lnx04"}
      resource 3:
              Name         = "ros_ip_9_26_92_136-rs"
              IPAddress    = "9.26.92.136"
              NodeNameList = {"ma3455lnx04","ma3455lnx05"}
  2. Log in to one of the servers and get the DB2 listener port (SVCENAME). Note that the DB2 listener port must be same on both nodes for applications to fail-over using VIP.
    • On the primary node:
      [db2inst1@ma3455lnx04 ~]$ db2 get dbm cfg | awk '/\(SVCENAME/ {print $6}'
      DB2_db2inst1
                                  
      [db2inst1@ma3455lnx04 ~]$ awk '/DB2_db2inst1/' /etc/services
      DB2_db2inst1                   24939/tcp

Catalog data sources on the Cognos server

Log in to the Cognos BI server machine as the DB2 instance owner (db2inst1) and catalog the primary and standby HADR databases (TPCDS10G) using the VIPs (primary alias as TPCDSP and standby alias as TPCDSROS):

  1. Catalog TCP/IP node for both nodes:
    dba> db2 catalog tcpip node HANODEP \
    remote 9.26.92.135 server 24939
    dba> db2 catalog tcpip node HANODES \
    remote 9.26.92.136 server 24939
  2. Catalog primary and standby databases using different aliases:
    dba> db2 catalog db TPCDS10G as TPCDSP at node HANODEP
    dba> db2 catalog db TPCDS10G as TPCDSROS at node HANODES
  3. From CLP connect to both databases and verify the connection:
    dba> db2 connect to TPCDSP user db2inst1 using ********
    dba> db2 connect to TPCDSROS user db2inst1 using ********

Create or modify read-only data source connection information using Cognos administration interface

  1. Open the Cognos (10.1) connection console locally on the server where IBM Cognos is installed or from a remote machine by launching the URL http://<machinename.domain>:80/cognos10.

    HINT: HTTP port is usually 80 on Windows, 8080 on UNIX.

    Figure 4. Launching Cognos administration console
    Image shows launching IBM Cognos administration console
  2. Launch the Cognos Administration console and click the Configuration tab. From the available Data Source Connections select the data source that your reports package uses.
  3. Click the New Data Source Connection icon and provide the name for the data source. In our test configuration, you need to specify TPCDS as the data source connection name.
  4. Click Next to select the data source connection type.
    Figure 5. Specifying data source connection parameters
    Image shows specifying data source connection parameters

    You can define and use the CLI connection alone or additionally define a JDBC (type4) connection. You cannot choose to use only the JDBC connection. The CLI connection requires the remote database to be cataloged locally, which was already done under Step 2 in the section "Catalog data sources on the Cognos server."

  5. Provide the database name and select checkboxes for user ID and passwords for sign-ons.
    Figure 6. Configuring database sign-ons
    Image shows configuring database sign-ons
  6. If you chose to configure a JDBC connection in addition to the CLI connection, provide the details of server name (which would be the ROS VIP address), DB2 port number (i.e., numeric value associated with SVCENAME database manager configuration parameter) and the actual database name (e.g. TPCDS10G).
    Figure 7. Specifying JDBC connection information
    Image shows specifying JDBC connection information
  7. Click Test Connection to test your CLI/JDBC connections from Cognos server. If it is successful, click Finish to save the data source and exit the wizard.

    A data source connection is successfully created for the ROS database in our topology.

    Data Source connection name = TPCDS
    Type = DB2, Isolation level = default
    Database name = TPCDSROS
    ROS VIP = 9.26.92.136
  8. Optional: To create a database connection for the HADR database on the primary VIP, click on the data source name viz. TPCDS and click the New Connection icon to add a CLI/JDBC connection. Provide a name for the new database connection and Repeat steps 4-7 to add the primary HADR database with the following parameters:
    Database connection name = hadrP
    Type = DB2, Isolation level = default
    Database name = TPCDSP
    ROS VIP = 9.26.92.136
  9. The Configuration tab should display the two connections to both HADR databases for the data source TPCDS (if you chose to use database connections for both HADR ROS and primary databases). You can also modify the properties of a database connection and rename them to be meaningful.
  10. You can now create dimensional models based on the HADR ROS data source (hadrROS) using Cognos Framework Manager modeling tool.

Using ROS VIP for OLAP modeling and design

Now that you have completed the initial setup and configuration of the BI applications, you are now ready to utilize VIP connection to the reads on standby database to perform relational modeling activities and create reports.

Using the HADR ROS data source connection in Cognos Framework Manager for relational modeling

You can also use IBM Cognos 10 Framework Manager to create and connect to data sources. Using these data sources, you can perform relational modeling. The models ("cube" metadata) and the connection information for the cubes can then be packaged and published to the Cognos Content Store to be used by other IBM Cognos reporting tools.

Cognos Framework Manager is strictly read-only (i.e., import metadata, model, and publish packages). Framework Manager cannot be used to change the underlying data sources structure or definition. The ROS database is perfectly suited for this type of activity.

In the example below, how to use the standby ROS database as the metadata source in the modeling component (i.e., Framework Manager):

  1. Open IBM Cognos Framework Manager.
  2. Create a new project that will create the package:
    • Click File > New. The New Project window opens.
    • In the New Project window, specify a project name and location.
  3. Select the design language for the project. The Metadata Wizard opens.
    • You can also launch the metadata wizard from the right-click option on Project > Model.
  4. On the Select Metadata Source page, select Data Sources from the list of metadata sources.
  5. On the Select Data Source page, select the data source connection (i.e., TPCDS), which is created under section Configuring Cognos BI Server to connect to HADR database servers using VIPs. The Cognos Framework Manager will connect to the Cognos Dispatcher service and will retrieve the connection metadata such as sign-on, data source type, etc. from the Cognos Content Store.
  6. From the Select Objects page, select all the objects from the (TPCDS) schema that you wish to import.
  7. From the Generate Relationships page, select the following options:
    • Criteria to detect and generate relationships: ALL (3)
    • Objects to detect and generate relationships: both
    • For Join and Granularity options: Use Default settings
  8. Click Finish to close and exit New data source wizard.
  9. Save this new data source in the project by clicking File > Save All.
  10. Now you can create a Cube model using the schema information from the relational data source you just imported. See IBM Cognos 10 documentation for how to use Framework Manger to create models.
  11. Once a model for the end-user analytics (reports) has been developed, you will create a package that can be published to the content store. This can be accomplished through series of actions from a right-click action on the packages folder. Once the create packages is verified and published, you can use one of the reporting tools (e.g., Report Studio, Analysis Studio etc.) to create and run reports against this package.

Using ROS VIP standby for continuous read-only queries and reporting

Using HADR databases set up with ROS VIP is useful in supporting 24x7 end-user reporting activity. This configuration not only enables continuous read-only access but also provides seamless and transparent fail-overs to users. Switching the HADR pairs for various reasons will not affect end-user activity unless one of the servers is completely offline.

The main objective here is to demonstrate reporting (read-only) activities using the standby server in a HADR/ROS pair. It is sufficient to allow the standby to handle the read-only queries and reporting activities. It is demonstrated through the following sections:

  • Scenario— When the primary in a HADR pair goes offline for some reason, the server that was acting as standby becomes the new primary and starts handling the updates to the database. The original primary in the HADR pair when restarted becomes the new secondary transparent to all connected applications. In fact, BI applications such as Cognos have no knowledge about this role switch since we had configured the Cognos connection to point to the ROS virtual IP. However, transparent to the applications, the ROS VIP will be associated with the new standby and the queries will be re-directed to this new standby database server seamlessly.
  1. HADR pair is set up and configured using primary VIP and ROS VIP
    Primary server: ma3455lnx04, VIP: 9.26.92.135
    Standby server: ma3455lnx05, ROS VIP: 9.26.92.136
  2. Data source connection in the BI application (Cognos) is set up and configured to use only the standby ROS VIP connection.
  3. The analyst is executing reports against the standby database; activity is shown on standby server (ma3455lnx05).
    Figure 8. Listing active database connections on standby database
    Image shows listing active database connections on standby database
  4. Due to various reasons, the roles of the HADR pair are switched. For example, the database server on primary (ma3455lnx04) goes down, resulting in a HADR takeover from the standby (ma3455lnx05).
    New Primary server: ma3455lnx05, VIP: 9.26.92.135
    New Standby server: ma3455lnx04, ROS VIP: 9.26.92.136
  5. The analyst continues to execute reports against the ROS VIP connection, however, is unaware that a switch in the roles has happened as shown by activity on the new standby (ma3455lnx04).
    Figure 9. Active database connections after fail-over
    Image shows active database connections after failover

Utilizing Primary VIP and ROS VIP to run OLAP reporting

We discussed using HADR databases set up with ROS VIP for continuous read-only access. However, there may be occasions to provide users with an option to query (read only) against the standby or the primary in the HADR pair in order to balance the load of the querying processing overhead. Another reason could be one of the servers being down for a prolonged period (due to a hardware failure, for example) — in this case, only surviving IP is the primary VIP. You can take advantage of using both the primary and ROS VIP to manage such situations.

Again, you can use IBM Cognos BI server application to demonstrate how this can be done.

Scenario A: Load balancing

  1. First, in the IBM Cognos Administration console, define and create two database connections for the data source that a Cognos package will use. The Configuration tab should display the two connections to both HADR databases for the data source (if you chose to use database connections for both HADR ROS and primary databases). For example, in our test configuration for TPCDS data source, you can see hadrP and hadrROS connections as shown below.
    Figure 10. Listing database connections defined for a data source
    Image shows listing database connections defined for a data source
  2. Provide users the option at runtime to choose the database server to query against. When a report is submitted for execution, the analyst or end user has the option of choosing either of the database connections.
    1. User chooses to execute a report.
    2. User will be prompted to choose a database connection.
      Figure 11. Selecting a database connection at runtime
      Image shows selecting a database connection at runtime
    3. Choose one of the connections and submit a report for executions to get results.

Scenario B: A server goes offline

As part of normal operations, users would be querying against only the standby database. When an outage of one of the HADR servers occurs, you can enable the capability to run analytics against the database on the remaining node. The solution in this scenario would be to use both the primary and ROS VIP to create database connections, but enable only the ROS VIP connection until an outage occurs.

  1. In the IBM Cognos Administration console, define and create two database connections for the data source that a Cognos package will use.
  2. Modify and save the properties of the database connection to disable the entry that you don't want users querying against during normal operations. In this case, disable the hadrP connection.
    Figure 12. Selectively enabling a specific database connection
    Image shows selectively enabling a specific database connection
  3. End users will now only query against the HADR standby configured with the ROS VIP. There will be no prompt to choose a database connection.
  4. Assume a one of the servers failed for a prolonged period — due to a hardware problem, for example. Now the only surviving virtual IP address in the Tivoli SA MP resource model is the primary VIP.
  5. In that case, you can modify the properties of the database connection for hadrP to enable it again and disable hadrROS to allow querying against the available database. The process to switch an application to use one connection or the other when needed, is relatively low overhead and trivial (does not require any restart of the database server or the application).

Understanding the data flow for effective use of HADR VIPs in a BI environment

You need to pay special attention to the behavior of each BI application in your environment to get the full benefit of ROS. A good understanding of the nuances associated with each BI application with respect to setup, configuration, usage, and administration is critically important to ensure that you can maximize the ROS feature.

The following table illustrates how to profile each BI application to evaluate which application or operations can utilize the HADR ROS database effectively.

Table 2. Profiling BI applications to maximize HADR ROS
BI applicationOperationCategory (read-only, read/write)Node to runDescription
ISW Cubing serverOLAPMostly read-onlyAnyNote, certain administration tasks may need to change the cube metadata and such operations should be run on the primary.
AlphaBlox serverOLAPRead-onlyCubing serverABX server would run MDX queries against the ISW Cubing Services.
Cognos BI serverOLAPRead-onlyAny
ISW Design StudioMetadata modeling and schema updatesMostly read/writePrimary Most of the time Designer Client would be used to change the cube and redeploy, or make schema changes (DDL).
Cognos BI modelingMeta-data modelingRead-onlyAnyCognos Framework Manager can be used to import metadata, create cube models, and publish packages.

Conclusion

The DB2 High Availability and Disaster Recover (HADR) feature is a database replication method 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.

The HADR reads on standby (HADR ROS) feature, allows read only applications to access the HADR Primary or the HADR Standby database. This enables an organization to offload some of the read-only workloads running on the Primary to the Standby database.

This article shows a practical application of this HADR ROS feature in conjunction with virtual IP (VIP) addresses to ensure continued, automatic connectivity in a business intelligence (BI) environment.


Download

DescriptionNameSize
Sample Universal Feed agentscripts.zip3KB

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

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=855118
ArticleTitle=Utilizing the DB2 HADR reads on standby feature in a business intelligence environment
publish-date=01242013