Prevent database outages with High Availability Disaster Recovery in DB2
Configuring DB2 HADR using Data Studio and Optim Database Administrator
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.
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
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) 184.108.40.206 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.
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:
- 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
- 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.
- 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
- 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
If your test connection succeeds, you see the
- Click OK twice to create your connection, as shown in Figure 4.
Figure 4. Test connection was successful
Starting the HADR setup
Complete the following steps to set up the primary database for HADR:
- After successfully connecting to the database, from the Data Source Explorer, right-click the database name, such as GSDB
- Select Setup HADR, as shown in Figure 5.
Figure 5. 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
- 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:
- Click Configure to configure a new logging type for the database, as shown in Figure 6.
- 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.
- Click Run to change the logging type of your database, as shown in Figure 7.
Figure 7. Configure Database Logging panel, Type tab
After you change the logging type of your database, the Setup HADR
ARCHIVE, as shown in
Backing up the database
Complete the following steps to make a new backup of your database for HADR to use.
- Click Backup to open the Backup window, as shown in Figure 8.
Figure 8. To finish up primary database setup, back up the database
- 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
- 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
- On the Options tab, check whether Full backup - backup all data is selected.
- 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.
- Click Run to initiate the backup, as shown in Figure 11.
Figure 11. Choosing full backup and online backup options
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.
- 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.
- Go back to the Setup HADR window, and click the Standby Database tab.
- 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
Configuring the connection profile
- On the Create standby database connection profile tab, enter the required values for your standby database connection.
- Click Test Instance to test the connection, as shown in Figure 13.
Figure 13. Testing and creating the standby database connection
- When you see the message
Connection Succeeded, click Finish to create the connection.
Setting initialization options
- 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.
- 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.
- 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
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:
- Which external objects, such as UDFs and stored procedures, to copy to the standby database
- TCP/IP parameters
- Client reroute parameters
- Synchronization 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
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
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
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:
- Select Near synchronous mode.
- Keep the default value of 120 seconds for the Connection time-out period.
- Set the Peer window size (in seconds) field to 0.
Figure 18. Options used in the synchronization tab
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
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
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
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
Primary, and the connectStatus is
Connected, as shown in Figure 22.
Figure 22. Checking the status of the primary database using db2pd
Now use db2pd to check the status of the HADR on the standby
database. Note that the Role is
and the connectStatus is
shown in Figure 23.
Figure 23. Checking the status of the standby database using db2pd
Congratulations! You have successfully configured DB2 HADR using Data Studio.
- Read the DB2 On Campus e-book: Getting Started with Data Studio for DB2 for more information about using Data Studio.
- See the DB2 Information Center for a complete list of system requirements for DB2 HADR.
- Consult the DB2 Information Center for how to specify alternate servers for client rerouting.
- Download the Optim Database Administrator 30-day trial.
- Download Data Studio at no charge.
- Download the GSDB sample database.