Prevent database outages with High Availability Disaster Recovery in DB2

Configuring DB2 HADR using Data Studio and Optim Database Administrator

Learn how to configure DB2® High Availability Disaster Recovery using IBM Data Studio or Optim™ Database Administrator. First, this article describes all the steps, including how to configure both the primary and standby databases, how to configure the logging type, and how to back up the primary database. Then, you learn how to preview all the generated commands before executing the setup. Finally, you start the HADR environment and validate that all the steps were executed as expected.

Share:

Ivan Lopes, Jr. (ilopes@us.ibm.com), Quality Assurance Team Leader, IBM

Photo of Ivan Lopes, Jr.Ivan has over 10 years of experience working with databases. He was a Database Administrator in Brazil for five years before moving to the U.S. in 2000. He was a member of the Informix and DB2 LUW advanced support team, where he specialized in performance, backup and restore, data replication, and solving severity 1 problems. Ivan holds several Informix and DB2 professional certifications, including the Online III certification for down systems and severity 1 problems. Currently, Ivan is the Quality Assurance Team Leader for the Optim Database Administrator product.



11 March 2010

Also available in Portuguese

Before you start

Introducing DB2 High Availability Disaster Recovery

DB2 High Availability Disaster Recovery (HADR) is a DB2 feature that provides a high-availability solution for database site failures by replicating database changes from a source database (primary) to a target database (standby). HADR protects against data loss by shipping log data from the primary database to the standby database and by constantly applying the log data into the standby site using rollforward. In case of a critical hardware, network, or software failure in the primary site, the standby site can take over the database server operations much faster than the time it would take to repair the failure on the primary site. There are other high-availability solutions you can use with DB2, but DB2 HADR is a feature completely contained within DB2 itself, so it does not require any additional hardware or software solution.

Note: Starting with DB2 9.7.1, DB2 supports read operations against standby databases. This can be a great performance benefit, because users can shift the read-only workload to the standby database directly, freeing up cycles in the primary database.

Introducing Data Studio and Optim Database Administrator

IBM Data Studio is the complimentary tooling available with your database license that provides basic database administration and database development capabilities for DB2 (and, to some extent, Informix® Dynamic Database). As of DB2 9.5, Data Studio replaced older tools, including Developer Workbench. Data Studio also provides some of the basic capabilities of DB2 Control Center. Along with capabilities to test, debug, and deploy routines, including stored procedures and user-defined functions, Data Studio also includes the following key database and object-management capabilities:

  • Manage DB2 instances (start and stop, quiesce, configure parameters)
  • Manage and recover databases
  • Connect to DB2 or Informix data sources and browse data objects and their properties
  • Use editors and wizards to create and alter data objects
  • Modify privileges for data objects and authorization IDs
  • Drop data objects from databases
  • Analyze the impact of changes
  • Manage data in tables, including reorganizing, importing, and exporting
  • Backup and recover data
  • Use data diagrams to visualize and print the relationships among data objects
  • Import and export database connections
  • Configure automatic maintenance and logging
  • Rebind packages
  • Get index advice on single queries (stand-alone package only)
  • Configure pureScale members (stand-alone package only)

Data Studio comes in both an integrated development environment (IDE) package and a stand-alone package. The stand-alone package has a lighter footprint, and it contains everything you need for this tutorial. You can also use Optim Database Administrator, which is a priced-offering that includes advanced features for managing complex database schema changes. Additional features included in Optim Database Administrator include:

  • Copying and pasting database objects and data
  • Generating schema change reports
  • Exporting administration and change scripts as Command Line Processor (CLP) scripts
  • Generating undo scripts

For this tutorial, you can use either Data Studio or Optim Database Administrator.

About this tutorial

The high-availability disaster recovery (HADR) feature of DB2 for Linux, UNIX, and Windows can help prevent data loss. Using Data Studio or Optim Database Administrator to configure the setup makes the process relatively easy. In this tutorial you will learn how to configure and execute the HADR setup using the Data Studio stand-alone package. You can also use the Optim Database Administrator or Data Studio IDE instead.

Objectives

The objective of this tutorial is to teach you the process of setting up DB2 HADR using Data Studio or Optim Database Administrator. You will learn how to:

  • Connect to your primary database and configure it for HADR, including configuring archive logging and creating the backup to copy over
  • Connect to the standby database and configure it for HADR
  • Configure additional options, such as copying over external objects and configuring TCP/IP
  • Review and execute the generated HADR commands
  • Validate the setup

Prerequisites

This tutorial assumes you have basic knowledge of how to administer a DB2 database on Linux, UNIX, and Windows. Familiarity with Data Studio or Optim Database Administrator is recommended. A good resource to get basic knowledge is the Getting Started with Data Studio e-book (see Resources).

To follow along with the tutorial, you need DB2 for Linux, UNIX and Windows 9.7 installed on two different computers. You also need to have either Data Studio (stand-alone) 2.2.0.1 or Optim Database Administrator 2.2.2 installed on a system that can connect to the DB2 instances. See Resources for links to a download of free Data Studio or to a 30-day trial of Optim Database Administrator.

The database used in this tutorial is the sample database from the fictional Great Outdoors Company. You can download this database from the Integrated Data Management Information Center (see Resources). You can use the DB2 SAMPLE database or any other database for the tutorial instead.

System requirements

The recommended approach to setting up HADR is for the two computers to be used as primary and standby computers to have identical operating systems. In the example setup, there are two x86_64 SUSE Linux® 10 boxes called server1 and server2. Server1 is the primary computer, and server2 is the standby computer. You also need an extra TCP port for the HADR service.

For a complete list of the system requirements for DB2 HADR, see Resources.


Opening Data Studio

Complete the following steps to open Data Studio:

  1. On your Windows Start menu, click Start > All Programs > IBM Data Studio stand-alone > IBM Data Studio 2.2 to open Data Studio, as shown in Figure 1.
Figure 1. Opening Data Studio 2.2
Screen cap: IBM Data Studio stand-alone > IBM Data Studio 2.2
  1. If you already have a database connection configured for the primary database, skip to Starting the HADR setup. If you don't already have a database connection configured for the primary database, continue to the next step.
  2. In the Data Source Explorer, right-click the Database Connections folder, and select New, as shown in Figure 2.
Figure 2. Creating the new Data Studio connection to the primary database
Screen cap: Data Source Explorer tab showing Database Connections folder and selecting New
  1. In the New Connection window, enter the values for the required fields for a new DB2 for Linux, UNIX, and Windows connection, and click Test Connection to test whether you can connect successfully to the primary database, as shown in Figure 3.
Figure 3. Input the required values for the new database connection
Input values such as database driver, host name, port number, authentication information. Click Test Connection button.

If your test connection succeeds, you see the Ping Succeeded! message.

  1. Click OK twice to create your connection, as shown in Figure 4.
Figure 4. Test connection was successful
Screen cap: Ping Succeeded message plus OK twice

Starting the HADR setup

Complete the following steps to set up the primary database for HADR:

  1. After successfully connecting to the database, from the Data Source Explorer, right-click the database name, such as GSDB
  2. Select Setup HADR, as shown in Figure 5.
Figure 5. Selecting the database in Data Studio and clicking Setup HADR
Screen cap: selecting the database in Data Studio and clicking Setup HADR

The Primary Database tab of the Setup HADR window should have all the information already filled in, as shown in Figure 6.

Figure 6. Setup HADR panel, Primary Database tab
Screen cap: Confirm the selection window showing DB info plus Logging section with Config button
  1. If the Database information is accurate for your environment, skip to Backing up the database. For the example, however, the red X in the Logging section indicates a problem. GSDB is using circular logging, which cannot be used when configuring HADR.

Changing the database logging type

To change the logging type, complete the following steps:

  1. Click Configure to configure a new logging type for the database, as shown in Figure 6.
  2. In the Configure Database Logging panel, check the Archive option, and enter a valid directory for your Primary archive log location and for your Backup log location. Be sure the directories are valid on both primary and standby computers.
  3. Click Run to change the logging type of your database, as shown in Figure 7.
Figure 7. Configure Database Logging panel, Type tab
Screen cap: Type window with Run highlighted

After you change the logging type of your database, the Setup HADR window shows ARCHIVE, as shown in Figure 8.

Backing up the database

Complete the following steps to make a new backup of your database for HADR to use.

  1. Click Backup to open the Backup window, as shown in Figure 8.
Figure 8. To finish up primary database setup, back up the database
Select Backup button
  1. On the Type tab of the Backup window, select the Backup the entire database option, as shown in Figure 9.
Figure 9. Choosing the Back up the entire database option
Screen cap: Back up tab with Back up the entire database selected for Type of backup
  1. On the Image tab, enter a valid directory in the Location of back up image field, as shown in Figure 10. Be sure this location is valid on both primary and standby computers.
Figure 10. Indicating the location of the back up image
Screen cap: Back up tab Indicating Location of back up image as /home/db2inst3/bkp
  1. On the Options tab, check whether Full backup - backup all data is selected.
  2. Specify an Online backup, which enables users to continue using the database during the backup. If you want to use an Offline backup, quiesce the database first.
  3. Click Run to initiate the backup, as shown in Figure 11.
Figure 11. Choosing full backup and online backup options
Screen cap: the Online and Full backup options are selected, and Run is circled.

Now the primary database has been set up for HADR and backed up. Continue to the next section to configure the standby database.


Configuring the standby database connection

After you back up the primary database, you are ready to configure the standby database.

  1. If you have an existing database connection to the standby database, go to Configuring the connection profile. If you do not have an existing database connection to the standby database, continue to the next step.
  2. Go back to the Setup HADR window, and click the Standby Database tab.
  3. Click Add to add a new connection to the standby database, as shown in Figure 12.
Figure 12. Adding a new connection to the standby database
Screen cap: Standby Database tab with Add beside Connection Profile highlighted

Configuring the connection profile

  1. On the Create standby database connection profile tab, enter the required values for your standby database connection.
  2. Click Test Instance to test the connection, as shown in Figure 13.
Figure 13. Testing and creating the standby database connection
Screen cap: Instance name, Host, Port, User name, and Password filled in. Test Instance circled.
  1. When you see the message Connection Succeeded, click Finish to create the connection.

Setting initialization options

  1. After you create your standby connection, return to the Setup HADR window. On the Standby database tab, the Connection profile field and the Instance name field should be filled out.
  2. In the Initialization options section, select one of the following options:
    Use an existing database on the standby system
    By selecting this option, Data Studio will use a standby database that you have manually copied and restored into the standby system.
    Create a new database by using a backup image
    By selecting this option, Data Studio will copy and restore the primary backup image into the standby computer for you. For the example tutorial, select this option, as shown in Figure 14.
  3. Select the latest backup image available in the Backup Image Selection section, and type the directory in the Image copy location field, as shown in Figure 14.
Figure 14. Standby database, selecting the initialization, backup and restore options
Standby database, click on add to add a new connection in Data Studio

You have completed the initial configuration of the primary and standby databases. Next you will configure the additional options to support the HADR setup.


Configuring additional options

This section describes the following configuration options:

Specify which external objects to copy

The default HADR setup does not include objects stored outside of the database, such as UDFs or stored procedures you might have developed. You can use the Copy Objects tab to specify where these objects are stored and which ones you want to copy, as shown in Figure 15. Although this tutorial does not explain this option in detail, in many cases, you want to use this option unless you have made other provisions to copy these critical artifacts. For this tutorial, leave this field blank.

Figure 15. Copy Objects tab
Copy Objects tab showing images to select

Configuring the TCP/IP parameters

Use the TCP/IP parameters tab to enter the service name or port numbers for HADR to use, as shown in Figure 16. These ports should be different than the port numbers used by the DB2 instances themselves.

Figure 16. HADR port numbers used in the TCIP/IP parameters tab
HADR port numbers 50030 used in the TCIP/IP parameters tab for Primary and for Standby

Configuring the client reroute parameters

On the Client Reroute tab, specify alternate DB2 servers to be used by the client application in case of a failure. For this tutorial, uncheck the option Specify alternate servers for the database, as shown in Figure 17. If you want to specify alternate servers, see Resources.

Figure 17. Client reroute tab, uncheck the Specify alternate servers for the databases option
Screen cap: Client Reroute tab with the Specify alternate servers for the databases option unchecked

Synchronization options

On the Synchronization tab, specify the synchronization mode for your HADR pair. The synchronization mode has a direct impact on the performance of your system. There are three synchronization modes:

Synchronous mode
The primary database writes the log pages to disk first before sending the pages to the standby database. The primary database waits for an acknowledgment from the standby database before notifying the application that the commit was successful. This mode can have a significant negative impact in the performance of your system.
Near synchronous mode
The primary database writes the log pages at the same time it sends them to the standby database. In an environment with a fast network between the primary and standby computers, this mode has very little impact in the overall primary database performance. This is the recommended mode for most situations.
Asynchronous mode
The primary database does not wait for an acknowledgment from the standby database. This mode has the least performance impact, but it can lead to a loss of log data during a failure.

You also need to determine the connection time-out period and the peer window size:

  • The HADR connection time-out period determines the maximum time a HADR database tries to connect to its peer before determining that the connection is lost.
  • The peer window size is the amount of time the HADR pair continues to behave as though it were in a peer state even after losing the network connection.

For this example tutorial, complete the following steps, as shown in Figure 18:

  1. Select Near synchronous mode.
  2. Keep the default value of 120 seconds for the Connection time-out period.
  3. Set the Peer window size (in seconds) field to 0.
Figure 18. Options used in the synchronization tab
Screen cap: Synchronization tab showing Near synchronous selected, 120 time-out seconds, and 0 Peer window size

Previewing the generated commands

Now review the Summary tab, as shown in Figure 19.

Figure 19. In the Summary tab, review your options to make sure everything is correct
Screen cap: In the summary tab, review database info, synchronization mode, and Start HADR options

Previewing all the commands before their execution

The only option left to select is whether you want to start HADR now or at a later time. Select Start HADR on the databases, and click the Preview Command link to preview all the commands, as shown in Listing 1.

Listing 1. Command preview
--  Configure Primary database ;
--  Update HADR configuration parameters for primary database;
UPDATE DB CFG FOR GSDB USING LOGINDEXBUILD ON ;
UPDATE DB CFG FOR GSDB USING INDEXREC RESTART ;
UPDATE DB CFG FOR GSDB USING HADR_LOCAL_HOST server1;
UPDATE DB CFG FOR GSDB USING HADR_LOCAL_SVC 50030;
UPDATE DB CFG FOR GSDB USING HADR_REMOTE_HOST server2;
UPDATE DB CFG FOR GSDB USING HADR_REMOTE_SVC 50030;
UPDATE DB CFG FOR GSDB USING HADR_REMOTE_INST db2inst3;
UPDATE DB CFG FOR GSDB USING HADR_SYNCMODE NEARSYNC;
UPDATE DB CFG FOR GSDB USING HADR_TIMEOUT 120;
UPDATE DB CFG FOR GSDB USING HADR_PEER_WINDOW 0;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
DEACTIVATE DATABASE GSDB;
UNQUIESCE DATABASE;
CONNECT RESET;
--  Configure Standby database ;
ATTACH TO GSDB1;
--  Restore database on standby system - server2;
RESTORE DATABASE GSDB FROM "/home/db2inst3/bkp" TAKEN AT 20091112163944 
REPLACE HISTORY FILE WITHOUT PROMPTING;
--  Update HADR configuration parameters for standby database;
UPDATE DB CFG FOR GSDB USING LOGINDEXBUILD ON ;
UPDATE DB CFG FOR GSDB USING INDEXREC RESTART ;
UPDATE DB CFG FOR GSDB USING HADR_LOCAL_HOST server2;
UPDATE DB CFG FOR GSDB USING HADR_LOCAL_SVC 50030;
UPDATE DB CFG FOR GSDB USING HADR_REMOTE_HOST server1;
UPDATE DB CFG FOR GSDB USING HADR_REMOTE_SVC 50030;
UPDATE DB CFG FOR GSDB USING HADR_REMOTE_INST db2inst3;
UPDATE DB CFG FOR GSDB USING HADR_SYNCMODE NEARSYNC;
UPDATE DB CFG FOR GSDB USING HADR_TIMEOUT 120;
UPDATE DB CFG FOR GSDB USING HADR_PEER_WINDOW 0;
--  Start HADR on standby database - GSDB;
DEACTIVATE DATABASE GSDB;
START HADR ON DATABASE GSDB AS STANDBY;
--  Start HADR on primary database - GSDB,host (instance)-server1 (db2inst3);
ATTACH TO GSDB;
DEACTIVATE DATABASE GSDB;
START HADR ON DATABASE GSDB AS PRIMARY;

Running the HADR setup

After reviewing the information on the preview, click Run to start the execution.

First, Data Studio transfers the backup image file from the primary computer to the standby computer, as shown in Figure 20.

Figure 20. Transfer of the backup image from the primary to the standby computer
Screen cap: Transfer File window showing progress bar

After the backup image transfer, Data Studio executes the commands needed to set up HADR.

After all the commands are executed, check whether the execution was successful. For the example, the status of the HADR operation shows Succeeded, as shown in Figure 21. The last command executed was the START HADR ON DATABASE GSDB AS PRIMARY command.

Figure 21. HADR start was successful
Screen cap: SQL Results tab showing HADR operation Succeeded

Validating the HADR setup

Now you can verify the setup. You can use the db2pd command to check the status of the HADR on the primary database. Note that the Role is Primary, and the connectStatus is Connected, as shown in Figure 22.

Figure 22. Checking the status of the primary database using db2pd
Screen cap: Telnet server1 showing Role as Primary and ConnectStatus as Connected

Now use db2pd to check the status of the HADR on the standby database. Note that the Role is Standby, and the connectStatus is Connected, as shown in Figure 23.

Figure 23. Checking the status of the standby database using db2pd
Screen cap: Telnet server2 showing Role as Standby and ConnectStatus as Connected

Congratulations! You have successfully configured DB2 HADR using Data Studio.

Resources

Learn

Get products and technologies

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=473012
ArticleTitle=Prevent database outages with High Availability Disaster Recovery in DB2
publish-date=03112010