IBM DB2 HADR Example

Here is an HADR configuration example that I did when preparing for my DBA certification. It has being awhile when I performed these steps, so bear with me if there are any mistakes or errors in the description.

DB2(R) high availability disaster recovery (HADR) is a data replication feature that provides a high availability solution for both partial and complete site failures. HADR protects against data loss by replicating data changes from a source database, called the primary, to a target database, called the standby.

This practice assumes two DB2 instances, named DB2INST1 and DB2INST2 resides in the same physical machine.

For configuring HADR, you will need few pieces of information.

Update DB2 Registry Profile
DB2 Communication Protocol (DB2COMM)

Update Each Instance DBM Configuration
DB2 TCPIP Listening Port (SVCENAME)

Update Primary DB Archical Logging Configuration
Log Pages During Index Rebuild (LOGINDEXBUILD)
Index Creation Time (INDEXREC)
Retain Log (LOGRETAIN)

Update Each DB HADR Configuration
Local IP Address (HADR_LOCAL_HOST)
Local Service Name/Port (HADR_LOCAL_SVC)
Remote IP Address (HADR_REMOTE_HOST)
Remote Service Name/Port (HADR_REMOTE_SVC)
Remote DB2 Instance Name (HADR_REMOTE_INST)
Synchronization Mode (HADR_SYNCMODE)

---

For the intent of all purposes, I installed a fresh copy of DB2 ESE version 9 and chooose NOT to create the default DB2 instance. Windows environment is assumed.


Step 1: Create 2 DB2 Instances

Open up an instance of DB2 Command Window by typing "db2cmd" in Run dialog.

Create Instance 1 by typing "db2icrt DB2INST1"

Create Instance 1 by typing "db2icrt DB2INST2"


Step 2: Configure Instance 1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2set DB2COMM=tcpip
db2 update dbm cfg using SVCENAME 41001
db2start


Note: The TCPIP service port for this instance is 41001

Step 3: Configure Instance 2

Type the following commands:

SET DB2INSTANCE=DB2INST2
db2set DB2COMM=tcpip
db2 update dbm cfg using SVCENAME 42001
db2start

Note: The TCPIP service port for this instance is 42001


Step 4: Create the Primary DB in Instance 1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 create db BANKFRS
db2 update db cfg for BANKFRS using LOGINDEXBUILD on
db2 update db cfg for BANKFRS using INDEXREC RESTART
db2 update db cfg for BANKFRS using LOGRETAIN on

Note: This step will turn the DB into archival logging mode


Step 5: Create a backup of Primary DB

Type the following commands:

db2 BACKUP DB BANKFRS TO C:\TEMP

Note: This step will backup the BANKFRS db into C:\TEMP


Step 6: Restore the backup BANKFRS as Standby database

Type the following commands:

SET DB2INSTANCE=DB2INST2
db2 RESTORE DB BANKFRS FROM C:\TEMP

Note: This step will restore the database as BANKFRS in the second DB2 instance and place it into roll forward pending state.

Note: If you roll forward the BANKFRS now, you will not be able to initialize it as the standby database.


Step 7: Configure the HADR settings for the Primary DB in DB2INST1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 update alternate server for database BANKFRS using hostname localhost port 42001
db2 update db cfg for BANKFRS using hadr_local_host localhost
db2 update db cfg for BANKFRS using hadr_local_svc 41005
db2 update db cfg for BANKFRS using hadr_remote_host localhost
db2 update db cfg for BANKFRS using hadr_remote_svc 42005
db2 update db cfg for BANKFRS using hadr_remote_inst DB2INST2
db2 update db cfg for BANKFRS using hadr_syncmode SYNC
db2 update db cfg for BANKFRS using hadr_timeout 120

Note: Primary BANKFRS HADR service port will be 41005.
Note: Standby BANKFRS HADR service port will be 42005.
Warning: You cannot use the Instance TCPIP port (SVCENAME) or next port number (SVCENAME+1) as the HADR service port. This is by design.


Step 7: Configure the HADR settings for the Primary DB in DB2INST1

Type the following commands:

SET DB2INSTANCE=DB2INST1
db2 update alternate server for database BANKFRS using hostname localhost port 41001
db2 update db cfg for BANKFRS using hadr_local_host localhost
db2 update db cfg for BANKFRS using hadr_local_svc 42005
db2 update db cfg for BANKFRS using hadr_remote_host localhost
db2 update db cfg for BANKFRS using hadr_remote_svc 41005
db2 update db cfg for BANKFRS using hadr_remote_inst DB2INST1
db2 update db cfg for BANKFRS using hadr_syncmode SYNC
db2 update db cfg for vusing hadr_timeout 120

Note: Primary BANKFRS HADR service port will be 41005.
Note: Standby BANKFRS HADR service port will be 42005.
Warning: You cannot use the Instance TCPIP port (SVCENAME) or next port number (SVCENAME+1) as the HADR service port. This is by design.

---

Now, you are ready to test the configuration.


Step 8: Start HADR on Standby DB IN DB2INST2

SET DB2INSTANCE=DB2INST2
db2 START HADR ON DB BANKFRS AS STANDBY


Step 9: Start HADR on Primary DB IN DB2INST1

SET DB2INSTANCE=DB2INST1
db2 START HADR ON DB BANKFRS AS PRIMARY


Step 10: Take over the Primary Role to Standby Instance

SET DB2INSTANCE=DB2INST2
db2 TAKEOVER HADR ON DB BANKFRS


Step 11: Take back the Primary Role from Standby Instance

SET DB2INSTANCE=DB2INST1
db2 TAKEOVER HADR ON DB BANKFRS

Step 12: Stop the HADR service on Primary DB

SET DB2INSTANCE=DB2INST1
db2 STOP HADR ON DB BANKFRS


Step 13: Stop the HADR service on Standby DB

SET DB2INSTANCE=DB2INST2
db2 deactivate db BANKFRS
db2 STOP HADR ON DN BANKFRS



General Notes:

You can invoke the HADR wizard from the DB2 Control Center by right clicking on the specific database and select "High Availability and Disaster Recovery" -> Setup

You can monitor the HADR status on the database by using the database snapshot monitor. "db2 get snapshot for database on BANKFRS"