Technical Blog Post
Abstract
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.
UID
ibm13286671