Calculating DB2 HADR Network Bandwidth

[Former Member]

Summary

This is a step by step guide to calculate DB2 HADR bandwith requirement hourly

Introduction

DB2 high availability disaster recovery (HADR) ensures high availability, Continuous Availability, data protection and disaster recovery for enterprise data. It aids

in establishing and maintaining secondary "standby databases" as alternative/supplementary repositories to

production "primary databases".

Since it is based on log shipment between primary and standby databases over network (generally WAN

for disaster recovery solutions) one of the main challenges is the calculation of required bandwidth.

In this whitepaper a bandwidth calculation method will be explained.

Bandwidth Calculation Formula

The formula calculates the required bandwidth with “Archived logs per second” value.

Required bandwidth = ((Archived logs MB per second / 0.7) * 😎  = bandwidth in Mbps

So we will aim to obtain that value.

Getting Archived logs per second.

We will use  DB2 diagnostic (db2diag) log file to find last completed archive log files. This file hold all completed archived log file names as seen below;

To find last archived log;

LASTARCHIVELOG=$(grep "Completed archive for log file" /db2/TST/db2dump/db2diag.log|tail -n 1|cut -d" " -f 6|cut -d. -f1|sed 's/[A-Za-z.]*//g')

To archive time , I`am using script run time( Second)

LAST_ARCHIVE_TIME=$(date +%s)

To find archive size in megabyte from database configuration;

ARCHIVESIZE1=$(db2 get db cfg for TST |grep LOGFILSIZ|cut -d= -f2)

typeset -i ARCHIVESIZE1

let ARCHIVESIZE=$ARCHIVESIZE1*4/1024

If scripts runs first time, we only saved archived logs and time to "/tmp/before-db2-log"

if [ ! -f $FILE ];

then

    echo "Script runs firts time, $FILE does not exists!"

    echo "$LAST_ARCHIVE_TIME,$LASTARCHIVELOG" >> /tmp/before-db2-log

else

If scripts does not runs first time , we compare the previously executed results with last archived log to find log difference when script operating range. (For our example this is 1 hour)

beforelog=$(cat /tmp/before-db2-log|tail -n 1|cut -d, -f2)

beforelogtime=$(cat /tmp/before-db2-log|tail -n 1|cut -d, -f1)

typeset -i beforelog

typeset -i LASTARCHIVELOG

let difference=$LASTARCHIVELOG-$beforelog

Now we know; 1 hour previous log number, 1 hour previous time(In second) and current log number, current time (in second).

We can find the number of log difference in one hour.

We will get average archive log generate during this time.( differencesize/ differencetime).

Also we have to convert Megabyte to Megabit ( multiplying by eight).

Finally, we have to consider TCP header size.( We calculated only application layer but we have to convert to transport layer for TCP transfer.)

Because of this, we use 10/7 correction factor for TCP header length.

let differencesize=$difference*$ARCHIVESIZE

let differencetime=$LAST_ARCHIVE_TIME-$beforelogtime

bandwith=$(echo "scale=2; $differencesize*8/7*10/$differencetime"|bc)

Generally systems activity for all day long or a week must be observed to get an clear idea about required

bandwidth. So to keep the historical data of this metric we will use an operating system level script.

Recording Archive Log Generate Per Second Value Hourly

Here is an example script prepared in AIX environment. SID of database is TST .

HOUR=`date +%k`

echo `date +%d%m%y`,$HOUR,"Bandwith requirement $bandwith Mbit/s" >>  /db2/db2tst/bandwith-db2.log

For determine log sequence number in the next run,

echo "$LAST_ARCHIVE_TIME,$LASTARCHIVELOG" >> /tmp/before-db2-log

We will run this script hourly via crontab so that it records  average “Archive Log Generate per Sec”  values in bandwith-db2.log.

You can use crontab –e command to create crontab entry. An example crontab entry:

0 * * * * /usr/bin/su - db2tst -c "/db2/db2tst/bandwith-db2.sh" 1>/dev/null 2>/dev/null

At the end of day bandwith-db2.log file will look like this:

6.Analyzing RGPS with Excel

We can use an excel sheet to turn this values into bandwidth with our formula mentioned before. We pasted

the values Hour,AVG to column A,B  respectively.

Max. Bandwidth values must be provided for maximum protection mode of HADR which keeps standby

synchron with primary. So this database needs 7 Mbps to run in this modes.

Average values are acceptable and must be provided for maximum availability and performance modes. At

hour 13 system has a peak average value for bandwidth – 3 Mbps.

So finally we got an excel which we can find hourly bandwidth requirement. Further analyses can be done

based on RTO and RPO targets.

bandwith-db2.sh

#!/usr/bin/ksh

#set working directory. script is located here..

cd /db2/TST

#hostname of the primary DB.. used in messages..

HOST_NAME=$(hostname)

LASTARCHIVELOG=$(grep "Completed archive for log file" /db2/TST/db2dump/db2diag.log|tail -n 1|cut -d" " -f 6|cut -d. -f1|sed 's/[A-Za-z.]*//g')

LAST_ARCHIVE_TIME=$(date +%s)

ARCHIVESIZE1=$(db2 get db cfg for TST |grep LOGFILSIZ|cut -d= -f2)

typeset -i ARCHIVESIZE1

let ARCHIVESIZE=$ARCHIVESIZE1*4/1024

FILE=/tmp/before-db2-2-log

if [ ! -f $FILE ];

then

    echo "Error $FILE does not exists!"

    echo "$LAST_ARCHIVE_TIME,$LASTARCHIVELOG" >> $FILE

else

beforelog=$(cat $FILE|tail -n 1|cut -d, -f2)

beforelogtime=$(cat $FILE|tail -n 1|cut -d, -f1)

typeset -i beforelog

typeset -i LASTARCHIVELOG

let difference=$LASTARCHIVELOG-$beforelog

# echo "Log difference is $difference"

let differencesize=$difference*$ARCHIVESIZE

let differencetime=$LAST_ARCHIVE_TIME-$beforelogtime

bandwith=$(echo "scale=2; $differencesize*8/7*10/$differencetime"|bc)

HOUR=`date +%k`

echo `date +%d%m%y`,$HOUR,"Bandwith requirement $bandwith Mbit/s" >>  /db2/db2tst/bandwith-db2.log

echo "$LAST_ARCHIVE_TIME,$LASTARCHIVELOG" >> $FILE

fi

For Oracle bandwith calculating;  http://scn.sap.com/docs/DOC-14449