Simplify DB2 HADR with IBM PureApplication System

Setting up DB2® HADR in IBM® PureApplication™ System is not a daunting task. This article describes the simple steps for setting up a DB2 HADR environment for your applications. Also learn to provision an HADR-enabled database from a virtual application pattern to enable monitoring and to verify your setup.

Ammu Soni (asoni@us.ibm.com), Advisory Software Engineer, IBM

Ammu SoniAmmu Soni, an advisory software engineer in the Information Management division, has over 13 years of experience at IBM. She is currently the QA team lead for the Pure Application System DbaaS team. Previously, Ammu was part of the IBM Informix organization. She co-authored the IBM Redbooks publication IBM Informix Flexible Grid:Extending Data Availability.



Manoj Ghogale (mghogale@in.ibm.com), Staff Software Engineer, IBM

Manoj GhogaleManoj Ghogale is a test automation specialist on the DBaaS QA team. He has been with IBM for over nine years and is currently leading the effort for PureSystem QA Automation using Rational Tools.



Kapish Kumar (kapish.kumar@in.ibm.com), Software Test Engineer, IBM

Kapish KumarKapish Kumar, a software test specialist, joined IBM as a fresh college graduate and now has five years of experience. Initially, he worked in functional verification testing for DB2 V10.1, and for the last few years he has been a test engineer on the IBM PureApplication System.



Sweety Shalini (sshalini@in.ibm.com), Software Engineer, IBM

Sweety ShaliniSweety Shalini has worked as a functional tester for the Galileo Project and was also part of IWD (DbaaS) functional testing. Currently, she is on the IBM PureApplication Systems QA team and has tested many releases of DBaaS (Database as a service) in IPAS.



10 April 2014

Introduction

Most applications today need continuous data availability for operation. Any downtime of a database accessed by an application can result in significant losses of time and money. Minimizing outage time and data loss has become a business necessity.

Learn more about PureApplication System and other products in the PureSystems family.

With the DBaaS 1.1.0.8 pattern type and IBM PureApplication System (PureApp System) v1.1, you can deploy your databases in an HADR environment. HADR in the PureApp System is designed to provide high availability at the database level. Disaster recovery is not yet supported, as the virtual machines (VMs) created will be in a single rack.

Terminology in this article

  • ACR: Automatic client reroute.
  • Auto takeover: A system automatically changes the state when failover happens.
  • Disaster recovery: Activities to restore a database after a fire, earthquake, vandalism, or other catastrophic event.
  • Failover: Forced rapid change in status of the standby database to become the primary and maintain data availability.
  • HADR: High availability disaster recovery.
  • TSA: Tivoli System Automation.
  • TSM: Tivoli Storage Manager.
  • Virtual application: An application that runs in 1-n VMs.
  • Virtual Machine (VM): A software-based emulation of a physical computer but does not have the physical disk drive, CPU, network card, monitor, and so on. A virtual appliance can be a VM, but a VM is not necessarily a virtual appliance.
  • Standby: The system to handle operations when the primary fails.

HADR in the PureApp System frees you from setting and configuring the environment. It provides an ultra-simple method of provisioning an HADR-enabled database. The only prerequisite is a configured IBM Tivoli Storage Manager (TSM) server and use of Internet protocol version IPv4 while deploying a database. (In future releases, IP version IPv6 will be supported.)

PureApp System provisions two separate VMs, which are kept on separate physical nodes, so failure of one physical device does not affect the other database on another physical node. After the VMs are ready, PureApp System installs DB2 and automatically opens the necessary port values in the firewall for TCP/IP connections. The system assigns primary and standby designations to the VMs and then creates the new HADR database on the primary. To create the standby, a backup of the primary database is sent to TSM. Users and groups are created on the standby that mirror the user and groups on the primary. The primary backup image is restored onto the standby database. The HADR configuration settings are updated on the standby. In PureApp System v1.1, only NEARSYNC mode of synchronization is supported.

In this article, you provision HADR-enabled databases as an instance and from a virtual application pattern. We also discuss auto takeover operations and compare traditional HADR setup to HADR in PureApp System.


Provisioning HADR enabled databases in a few steps

  1. Log in to any PureApp System rack.
  2. Click Workload Console at the top of the Welcome page.
  3. Go to the New Database creation window, as follows:
    • Select Working with databases on the Welcome page then click Deploy database, as in Figure 1.
    • Click Instances -> Databases -> + sign.
    Figure 1. Welcome, IBM PureApplication System rack
    Welcome page of IPAS Rack
  4. Enter the following information, as partially shown in Figure 2.
    Figure 2. DB information to be completed
    DB information to be filled
    • Database name: Provide an HADR database name.
    • Database description (optional): A description related to this DB feature. It is an optional field.
    • Purpose: Whether you want this DB for production, testing, or something else.
    • Source: Select any one of the following sources.
      • Apply a default database workload standard then click Departmental Transactional.
      • Apply a database workload standard then click the Workload standard, which has been created from a Departmental Transactional type of workload.
      • Clone from database image then click Image created from an existing deployed HADR database.
    • Database size(GB): The size limit for user data in your database. The database size limit is 2TB.

      The maximum storage capacity that you can initially assign a DB2 database on AIX® is 1TB. The maximum storage capacity that you can initially assign a DB2 database on Linux® is 1.8TB. After the database is created, use the Workload Console to increase the storage size up to 2TB.

    • Database compatibility mode: DB2 mode is the default option. Choose Oracle mode to allow applications currently running on Oracle to run on DB2.
    • Database version: Version of the database software used to create the database.
      • DB2 Version 10.1 for Linux
      • DB2 Version 10.5 for Linux
    • Database Level: Fix pack level of the database software used to create the database.
      • If Database version is V10.1, choose fix pack level DB2 Version 10.1 Fix Pack 2 for Linux.
      • If Database version isV10.5, choose fix pack level DB2 Version 10.5 Fix Pack 1 for Linux.
    • Enable HADR: You must select this box to create an HADR database with the DB2 HADR feature.
    • Schema file (optional): Select a schema file, click Browse, navigate to a schema file, and click Open in the file upload window.

      A schema file is an SQL file (.ddl or .sql extension) that's used to determine the structure of a database. Schema file statements must be delimited by line breaks, semicolons, or standard DB2 delimiters.

    • Advanced options: Expand advanced options and:
      1. Select an option from Territory. This value specifies the region that is associated with your database.
      2. Select an option from Code set. This value specifies the set of characters that are permitted for storage in the database.
      3. Select an option from Collating sequence. This value specifies how to sort non-unicode databases.
  5. After all fields are complete, click OK to deploy an HADR database.

Within a short time, deployment should be successful. After a successful deployment, a green icon displays preceding the database instance name, as in Figure 3.

Figure 3. Deployed HADR DB instance
Deployed HADR db instance

Provisioning HADR-enabled database from a Virtual Application pattern

  1. Log in any PureApp system rack.
  2. Click Workload Console at the top of the Welcome page.
  3. Select Patterns -> Virtual Applications, as in Figure 4, then click the + icon in the upper left of the page.
    Figure 4. Pattern for Virtual Applications
    Pattern to Virtual Applications movement
  4. Select the pattern type Web Application Pattern Type 2.0 then click Blank application.
  5. Click Start Building, as in Figure 5.
    Figure 5. Web Application, select blank application
    Web Application pop up, select blank application

    A virtual application builder page displays in a new window.

  6. On the virtual application builder page, expand the Database component and drag the Database (DB2) component into the workspace.
  7. Provide the values in the fields in the pane on the right.
    • Name: The name of Virtual Application to be deployed.
    • Database name: An HADR database name.
    • Database description (optional): A description of this DB feature.
    • Purpose: Whether you want this DB for production, testing, or something else.
    • Source: Select any of the following sources.
      • Apply a default database workload standard then click Departmental Transactional.
      • Apply a database workload standard then click the workload standard, which has been created from a Departmental Transactional type of workload.
      • Clone from database image then click Image created from an existing deployed HADR database.
    • Database version: Version of the database software used to create the database.
      • DB2 Version 10.1 for Linux
      • DB2 Version 10.5 for Linux
    • Database Level: Fix pack level of the database software used to create the database.
      • If Database version is set as V10.1, choose fix pack level DB2 Version 10.1 Fix Pack 2 for Linux.
      • If Database version is set as V10.5, choose fix pack level DB2 Version 10.5 Fix Pack 1 for Linux.
    • Database Size(GB): The size limit for user data in your database. The database size limit is 2TB.
    • Database compatibility mode: DB2 mode is the default option. Choose Oracle mode to allow applications currently running on Oracle to run on DB2.
    • Enable HADR: You must select this box to create an HADR database with the DB2 HADR feature, as in Figure 6.
      Figure 6. DB information to be filled in virtual application builder page
      DB information to be filled in virtual application builder page
    • Schema file (optional): Select a schema file, click Browse, navigate to a schema file, and click Open in the file upload window. A schema file is an SQL file (.ddl or .sql extension) that's used to determine the structure of a database. Schema file statements must be delimited by line breaks, semicolons, or standard DB2 delimiters.
    • Advanced options: Expand advanced options and:
      • Select an option from Territory. This value specifies the region that is associated with your database.
      • Select an option from code set. This value specifies the set of characters that are permitted for storage in the database.
      • Select an option from collating sequence. This value specifies how to sort non-unicode databases.
  8. Click Save in the upper left corner. Provide the Virtual Application name, a Description, and then click OK. After you save it, the created pattern is reflected in Patterns -> Virtual Applications.

    Click Created pattern then click Deploy in the upper right corner. Give the Virtual Application instance name in the Name field, set the environment values per your settings, and click OK to deploy an HADR database.

  9. You can check the deployment status by clicking Instances -> Virtual Applications.

Within a short time, deployment should be successful. After a successful deployment, a green icon displays preceding the virtual application instance name.

Monitoring an HADR database

After deployment, you can analyze database performance by using database performance monitoring. You can view a health summary of the database and go in-depth for more details.

To enable monitoring, database performance monitoring must be deployed by clicking Cloud -> Shared Services.

In the current release, database performance monitoring monitors only the primary database. When the primary fails, an alert message is sent to the user; when the primary comes back, monitoring resumes as usual.


Auto takeover operations

During the deployment, PureApp System takes care of configuring HADR setup on both the primary and standby VM. The command in Listing 1 is executed to verify the HADR setup in the primary VM.

Listing 1. Command to verify HADR setup in primary VM
 -bash-4.1$ db2pd -hadr -db MH10C 
                 
 Database Member 0 -- Database MH10C -- Active -- Up 6 days 02:57:58 -- Date 2014-03-24-11.59.00.858367

HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = NEARSYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
 ; HADR_FLAGS = 
PRIMARY_MEMBER_HOST = ipas-lpar-104-009
PRIMARY_INSTANCE = db2inst1
 PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = ipas-lpar-104-006
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 03/18/2014 09:03:02.158582
 (1395133382)     HEARTBEAT_INTERVAL(seconds) = 30
HADR_TIMEOUT(seconds) = 150
TIME_SINCE_LAST_RECV(seconds) = 3                     
 PEER_WAIT_LIMIT(seconds) = 0

The command in Listing 2 verifies the HADR setup in the standby VM.

Listing 2. Command to verify HADR setup in standby VM
 $ db2pd -hadr -db MH10C 
 Database Member 0 -- Database MH10C -- Active -- Up 6 days 02:57:58 -- Date 2014-03-24-11.59.00.858367
HADR_ROLE = STANDBY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = NEARSYNC
STANDBY_ID = 0
LOG_STREAM_ID = 0
HADR_STATE = PEER
 ; HADR_FLAGS = 
PRIMARY_MEMBER_HOST = ipas-lpar-104-009
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = ipas-lpar-104-006
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 03/18/2014 09:03:02.1601
  (1395133382)   HEARTBEAT_INTERVAL(seconds) = 30
HADR_TIMEOUT(seconds) = 150
TIME_SINCE_LAST_RECV(seconds) = 24
 PEER_WAIT_LIMIT(seconds) = 0

There are three scenarios where auto failover can occur.

Stopping DB2 instances manually on either the primary or standby machine
If you manually stop DB2 instances in any one of the VMs using the db2stop/db2stop force command, then the HADR state changes to DISCONNECTED state. After the instance is started again, HADR STATE comes again to PEER state. Failover does not happen in this case—primary is back as primary and standby as standby.
Primary VM is shut down
If the primary VM is shut down by chance, then the standby VM takes over and acts as a new primary VM. HADR STATE is DISCONNECTED in the new primary VM. After the old primary VM is backed up again, it acts as a new standby VM, and HADR STATE changes to PEER.
Auto failover
If the db2start process gets corrupted and its process ID is killed, then failover also occurs. The standby VM acts as the new primary VM with HADR STATE as DISCONNECTED. After the db2start process is recovered in the old primary VM, it should act as the new standby VM and HADR STATE changes to PEER.

Comparing traditional HADR setup and HADR in PureApp System

In a traditional setup of HADR using TSA, you have to perform the following operations:

  1. Create a database layout and other database objects on just one database that initially acts as primary node.
  2. Check/Update /etc/hosts file.
  3. Check/Update /etc/services file.
  4. Assign the following parameters to the primary node:
    hadr_local_host       primary.ibm.com
    HADR_LOCAL_SVC        hadr_service
    hadr_remote_host      standby.ibm.com
    HADR_REMOTE_SVC       hadr_service
    HADR_REMOTE_INST      dbinst2
    HADR_TIMEOUT          120
    HADR_SYNCMODE         NEARSYNC
    HADR_PEER_WINDOW      120
  5. Assign the following parameters to the standby node:
    hadr_local_host       standby.ibm.com
    HADR_LOCAL_SVC        hadr_service
    hadr_remote_host      primary.ibm.com
    HADR_REMOTE_SVC       hadr_service
    HADR_REMOTE_INST      dbinst2
    HADR_TIMEOUT          120
    HADR_SYNCMODE         NEARSYNC     
    HADR_PEER_WINDOW      120
  6. Take a database backup of the primary.
  7. Restore the database backup on the standby node.
  8. Check the database states.
  9. Start HADR on the databases.
  10. Do TSA cluster setup.
  11. Execute db2haicu on the standby node.
  12. Check cluster status.
  13. Configure ACR.

Compare this list of 13 steps with the provisioning of an HADR-enabled database in PureApp System (outlined in previous sections). With a few clicks, you are able to provision a completely functional HADR database with PureApp System and start using it immediately.


Conclusion

This article described how to achieve high availability for your DB2 database inside a single PureApp System rack. If you want to deploy only databases, then you can use the one-step deployment method. If you're looking for a complete virtual application deployment containing a DB2 HADR database, WebSphere®, and other components, then go for the Virtual Application deployment method.

Resources

Learn

Discuss

  • Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

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=967950
ArticleTitle=Simplify DB2 HADR with IBM PureApplication System
publish-date=04102014