How to update HADR related parameters quickly?

Body

Wishing you all a very happy 2017!

This is my first blog entry this year. Hope you find it useful. I have come up with small script that generates 'db2 update db cfg' command for HADR setup. 

During initial HADR setup or while reconfiguring HADR after it is broken due to several reasons, we need to update HADR related database configuration parameters. These parameters include following list:

 HADR local host name                  (HADR_LOCAL_HOST) = 
 HADR local service name                (HADR_LOCAL_SVC) = 
 HADR remote host name                (HADR_REMOTE_HOST) = 
 HADR remote service name              (HADR_REMOTE_SVC) = 
 HADR instance name of remote server  (HADR_REMOTE_INST) = 
 HADR timeout value                       (HADR_TIMEOUT) = 120
 HADR target list                     (HADR_TARGET_LIST) = 
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 HADR spool log data limit (4KB)      (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
 HADR log replay delay (seconds)     (HADR_REPLAY_DELAY) = 0
 HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0

We need to configure these parameters on all servers [Primary and Standby (Principal+Auxiliary)].

The shell script to configure these parameters as follows:

#!/bin/sh
var=$#
var=$((var-1))
if [ $# -eq 0 ]; then
 num=0
else
 num=$1
fi

if [ $var -ne $num -o \( $# -lt 7 -o $# -gt 10 \) ]; then
  echo ""
  echo "HELP:"
  echo ""
  echo "If local and remote instance names are SAME and if you want to specify mandatory parameters only -"
  echo "> Usage: $0 6 database_name local_hostname remote_hostname local_service_name remote_service_name remote_instance_name" >&2
  echo ""
  echo "If local and remote instance names are DIFFERENT and if you want to specify mandatory parameters only -"
  echo "> Usage: $0 7 database_name local_hostname remote_hostname local_service_name remote_service_name local_instance_name remote_instance_name" >&2
  echo ""
  echo "If local and remote instance names are SAME and you want to specify syncmode and peer window-"
  echo "> Usage: $0 8 database_name local_hostname remote_hostname local_service_name remote_service_name remote_instance_name syncmode peer_window" >&2
  echo ""
  echo "If local and remote instance names are DIFFERENT and you want to specify syncmode and peer window-"
  echo "> Usage: $0 9 database_name local_hostname remote_hostname local_service_name remote_service_name local_instance_name remote_instance_name syncmode peer_window" >&2
  echo ""
  echo "Please note: This script suggests you commands to setup HADR between Primary and Standby (Principal Standby) database servers. For multiple standby servers, you need to update HADR_TARGET_LIST manually."
  echo "There are few other HADR related parameters which you may want to configure manually. For eg: HADR_TIMEOUT, HADR_SPOOL_LIMIT, HADR_REPLAY_DELAY etc."
  exit 1
fi

case $1 in
  6)
        update1="db2 update db cfg for $2 using HADR_LOCAL_HOST $3 HADR_LOCAL_SVC $5 HADR_REMOTE_HOST $4 HADR_REMOTE_SVC $6 HADR_REMOTE_INST $7"
        update2="db2 update db cfg for $2 using HADR_LOCAL_HOST $4 HADR_LOCAL_SVC $6 HADR_REMOTE_HOST $3 HADR_REMOTE_SVC $5 HADR_REMOTE_INST $7"
  ;;
  7)
        update1="db2 update db cfg for $2 using HADR_LOCAL_HOST $3 HADR_LOCAL_SVC $5 HADR_REMOTE_HOST $4 HADR_REMOTE_SVC $6 HADR_REMOTE_INST $8"
        update2="db2 update db cfg for $2 using HADR_LOCAL_HOST $4 HADR_LOCAL_SVC $6 HADR_REMOTE_HOST $3 HADR_REMOTE_SVC $5 HADR_REMOTE_INST $7"
  ;;
  8)
        update1="db2 update db cfg for $2 using HADR_LOCAL_HOST $3 HADR_LOCAL_SVC $5 HADR_REMOTE_HOST $4 HADR_REMOTE_SVC $6 HADR_REMOTE_INST $7 HADR_SYNCMODE $8 HADR_PEER_WINDOW $9"
        update2="db2 update db cfg for $2 using HADR_LOCAL_HOST $4 HADR_LOCAL_SVC $6 HADR_REMOTE_HOST $3 HADR_REMOTE_SVC $5 HADR_REMOTE_INST $7 HADR_SYNCMODE $8 HADR_PEER_WINDOW $9"
  ;;
  9)
        update1="db2 update db cfg for $2 using HADR_LOCAL_HOST $3 HADR_LOCAL_SVC $5 HADR_REMOTE_HOST $4 HADR_REMOTE_SVC $6 HADR_REMOTE_INST $8 HADR_SYNCMODE $9 HADR_PEER_WINDOW ${10}"
        update2="db2 update db cfg for $2 using HADR_LOCAL_HOST $4 HADR_LOCAL_SVC $6 HADR_REMOTE_HOST $3 HADR_REMOTE_SVC $5 HADR_REMOTE_INST $7 HADR_SYNCMODE $9 HADR_PEER_WINDOW ${10}"
  ;;
esac

echo "Please run following commands:"
echo ""

echo "On (local_host) $3"
echo " $update1"
echo ""

echo "On (remote_host) $4"
echo " $update2"
echo ""

echo "After running these commands, you may start HADR on standby first followed by primary."

echo ""

Please save this file with any name - say 'update_hadr.sh' and grant execute permission on it.

Once done, you can check the usage by running script without providing any parameters. It displays help as follows:

$ ./update_hadr

HELP:

If local and remote instance names are SAME and if you want to specify mandatory parameters only -
> Usage: ./update_hadr 6 database_name local_hostname remote_hostname local_service_name remote_service_name remote_instance_name

If local and remote instance names are DIFFERENT and if you want to specify mandatory parameters only -
> Usage: ./update_hadr 7 database_name local_hostname remote_hostname local_service_name remote_service_name local_instance_name remote_instance_name

If local and remote instance names are SAME and you want to specify syncmode and peer window-
> Usage: ./update_hadr 8 database_name local_hostname remote_hostname local_service_name remote_service_name remote_instance_name syncmode peer_window

If local and remote instance names are DIFFERENT and you want to specify syncmode and peer window-
> Usage: ./update_hadr 9 database_name local_hostname remote_hostname local_service_name remote_service_name local_instance_name remote_instance_name syncmode peer_window

Please note: This script suggests you commands to setup HADR between Primary and Standby (Principal Standby) database servers. For multiple standby servers, you need to update HADR_TARGET_LIST manually.
There are few other HADR related parameters which you may want to configure manually. For eg: HADR_TIMEOUT, HADR_SPOOL_LIMIT, HADR_REPLAY_DELAY etc.

- The first parameter is a number (6,7,8,9) that indicates the number of parameters values you need to specify with that option.

Command usage:

$ ./update_hadr 6 SAMPLE hostA hostB 50000 60000 db2inst1
Please run following commands:

On (local_host) hostA
 db2 update db cfg for SAMPLE using HADR_LOCAL_HOST hostA HADR_LOCAL_SVC 50000 HADR_REMOTE_HOST hostB HADR_REMOTE_SVC 60000 HADR_REMOTE_INST db2inst1

On (remote_host) hostB
 db2 update db cfg for SAMPLE using HADR_LOCAL_HOST hostB HADR_LOCAL_SVC 60000 HADR_REMOTE_HOST hostA HADR_REMOTE_SVC 50000 HADR_REMOTE_INST db2inst1

After running these commands, you may start HADR on standby first followed by primary.

$ ./update_hadr 7 SAMPLE hostA hostB 50000 60000 db2inst1 db2inst2
Please run following commands:

On (local_host) hostA
 db2 update db cfg for SAMPLE using HADR_LOCAL_HOST hostA HADR_LOCAL_SVC 50000 HADR_REMOTE_HOST hostB HADR_REMOTE_SVC 60000 HADR_REMOTE_INST db2inst2

On (remote_host) hostB
 db2 update db cfg for SAMPLE using HADR_LOCAL_HOST hostB HADR_LOCAL_SVC 60000 HADR_REMOTE_HOST hostA HADR_REMOTE_SVC 50000 HADR_REMOTE_INST db2inst1

After running these commands, you may start HADR on standby first followed by primary.

$ ./update_hadr 8 SAMPLE hostA hostB 50000 60000 db2inst1 SYNC 90 
Please run following commands:

On (local_host) hostA
 db2 update db cfg for SAMPLE using HADR_LOCAL_HOST hostA HADR_LOCAL_SVC 50000 HADR_REMOTE_HOST hostB HADR_REMOTE_SVC 60000 HADR_REMOTE_INST db2inst1 HADR_SYNCMODE SYNC HADR_PEER_WINDOW 90

On (remote_host) hostB
 db2 update db cfg for SAMPLE using HADR_LOCAL_HOST hostB HADR_LOCAL_SVC 60000 HADR_REMOTE_HOST hostA HADR_REMOTE_SVC 50000 HADR_REMOTE_INST db2inst1 HADR_SYNCMODE SYNC HADR_PEER_WINDOW 90

After running these commands, you may start HADR on standby first followed by primary.

$ ./update_hadr 9 SAMPLE hostA hostB 50000 60000 db2inst1 db2inst2 SYNC 180
Please run following commands:

On (local_host) hostA
 db2 update db cfg for SAMPLE using HADR_LOCAL_HOST hostA HADR_LOCAL_SVC 50000 HADR_REMOTE_HOST hostB HADR_REMOTE_SVC 60000 HADR_REMOTE_INST db2inst2 HADR_SYNCMODE SYNC HADR_PEER_WINDOW 180

On (remote_host) hostB
 db2 update db cfg for SAMPLE using HADR_LOCAL_HOST hostB HADR_LOCAL_SVC 60000 HADR_REMOTE_HOST hostA HADR_REMOTE_SVC 50000 HADR_REMOTE_INST db2inst1 HADR_SYNCMODE SYNC HADR_PEER_WINDOW 180

After running these commands, you may start HADR on standby first followed by primary.

Hope this helps you.

- Yogesh.