Log-based replication

Got OpenEdge Replication? Good for you!!

No??? But you have implemented After Imaging haven't you?

Seriously, you have, haven't you? If your answer is "no" then the next question has to be "Why do you care so little about your data?"

Implementing After Imaging is a snap. Especially if you're running OpenEdge 10.1x and later.

After the implementation, you can copy the images to a secondary (and even tertiary) machine and roll them forward there. All you need is a plan and a set of scripts and that's where this posting is headed.

The purpose of this article is to identify the initial setup of log-based database replication and to document the procedures for recovery from computer outages. You'll also find a link to all of the scripts needed.

Please (and I know everyone asks this and nobody ever does it) read the whole PDF document. Better spend the time now than later when your hair is on fire (I hope not literally) and the IT director is breathing down your neck.

These programs have been implemented in High Availability/Mission Critical environments so we know they work. But your environment may be different so, you need to use caution when implementing these scripts. Please read this document completely before starting to implement these scripts.

What we are saying is that these scripts are offered as is with no warranty or liability. If they cause a problem or destroy your database we are not responsible. Now that our lawyers are happy, let's get to it.

The PDF document details all of the steps you need to implement AI in either a V9 or OpenEdge environment and explains the steps needed to implement recovery from a variety of failures.

There are five scripts involved; db.registry, read_registry, replicate_db, sub_replicate_db and roll_forward_ai.

db.registry

#Syntax:
#DBHOSTSceDirBkpAIDirRMTNODERmtAIDirTgtDirRmtBkupAPWs

DB The name of the database (no extensions (.db))

HOST The name or IP address of the Primary machine

SceDir The directory containing the Source Database

BkpAIDir The directory on the Primary machine where the archived AI files will live.

RMTNODE The name or IP address of the Secondary machine

RmtAIDir The directory on the Secondary machine where the AI files will be transferred to before they are rolled forward.

TgtDir The directory on the Secondary machine containing the Target database.

RmtBkup A directory on the Secondary machine which can contain either backups transferred from Primary or backups of Target. (The initial implementation of scripts does not utilise this).

APWs The number of APW processes to start. (The initial implementation of scripts does not utilise this).

read_registry

#!/bin/sh
# Program: read_registry
# Purpose: This script sets system and database environment
# variables for use by other scripts. A file (db.registry)
# is read from to determine if the database is valid and
# "registered" on the system
# Author: WSS
# Date: February 2008

if [ x$1 = "x" ]
then
echo "You must enter a database name"
exit 1
fi

# Setup generic system variables
DLC=${DLC-/u/progress/9.1e}
PROMSGS=$DLC/promsgs
PROTERMCAP=$DLC/protermcap
PROCFG=$DLC/progress.cfg
PROPATH=$PATH:$DLC:$SCRIPTS/live
PATH=$PATH:$DLC/bin
RESP=$SCRIPTS/live/response
WHOAMI=`whoami`
HOSTNAME=`uname -n cut -f 1 -d "."`

# Setup database specific variables
DB=`grep $1 $SCRIPTS/live/db.registry cut -f 1 -d ""`
HOST=`grep $1 $SCRIPTS/live/db.registry cut -f 2 -d ""`
DB_DIR=`grep $1 $SCRIPTS/live/db.registry cut -f 3 -d ""`
BKAIDIR=`grep $1 $SCRIPTS/live/db.registry cut -f 4 -d ""`
RMTNODE=`grep $1 $SCRIPTS/live/db.registry cut -f 5 -d ""`
RMTAIDIR=`grep $1 $SCRIPTS/live/db.registry cut -f 6 -d ""`
RMTDBDIR=`grep $1 $SCRIPTS/live/db.registry cut -f 7 -d ""`

if [ x$HOST = "x" ] # This name is not in the registry
then
unset DB HOST DB_DIR BKAIDIR RMTNODE RMTAIDIR RMTDBDIR
return 2
fi

if [ $HOSTNAME != $HOST ] # This database is on a different host
then
export HOST
unset DB DB_DIR BKAIDIR RMTNODE RMTAIDIR RMTDBDIR
return 3
fi
# Export variables to the shell
export DLC PROMSGS PROTERMCAP PROCFG PROPATH PATH RESP
export DB HOST WHOAMI DB_DIR BKAIDIR RMTNODE RMTAIDIR RMTDBDIR

replicate_db

Usage will be "replicate_db dbname" or "replicate_db all"

#!/bin/ksh
# Program: replicate_db
# Purpose: This script acts as a wrapper for the sub_replicate_db
# script. Allowing one or all databases to be replicated
# at the same time.
# Syntax: replicate_db
# Author: WSS
# Date: February 2008
#
SCRIPTS=${SCRIPTS-/u/scripts}
HOSTNAME=`uname -n cut -f 1 -d "."`
export SCRIPTS HOSTNAME

case $1 in

allALLAll)
for i in `grep $HOSTNAME $SCRIPTS/live/db.registry cut -s -f 1 -d "" `
do
if [ $i != "#DB" ]
then
. $SCRIPTS/live/read_registry $i

if [ $? != "0" ]
then
echo "" >/dev/null
else
echo "Working on database: $i"
$SCRIPTS/live/sub_replicate_db $i
fi
fi
done
;;
*)
. $SCRIPTS/live/read_registry $1

case $? in
2)
echo "Database $1 is not in database registry"
exit 1
;;
3)
echo "Database $1 is on $HOST not on this host"
exit 1
;;
esac
echo "Working on database: $1"
$SCRIPTS/live/sub_replicate_db $1
;;
esac

sub_replicate_db

#!/bin/sh
# Program: sub_replicate_db
# Purpose: This script is called by replicate_db to do the actual
# process of switching ai files (if possible), archiving
# the file(s) locally, moving them over to the remote
# machine and applying them to the replication DB and
# lastly archinving the file(s).
# Syntax: sub_replicate_db
#
# Author: WSS
# Date Written: February 2008
#
# Mods: Added double quotes around find command being executed via rexec
# due to the command not running properly.

# Setup the generic variables
SCRIPTS=${SCRIPTS-/u/scripts}
LOG=$SCRIPTS/logs
BKLOG=$SCRIPTS/logs/archive
INPROCESS=$SCRIPTS/tmp
DOW=`date +%a`
WHOAMI=`whoami`
export SCRIPTS LOG BKLOG INPROCESS DOW WHOAMI

# Do Operating system dependent aliasing
case `uname -s` in
HP-UX)
alias rsh=remsh
;;
esac

# Read the registry to setup the database specific variables
. $SCRIPTS/live/read_registry $1

case $? in
2)
echo "Database $1 is not in database registry"
exit 1
;;
3)
echo "Database $1 is on $HOST not on this host"
exit 1
;;
esac

if [ $BKAIDIR = "NO_AI" ]
then
exit 0
fi

LOG=$LOG/replicate$DB
INPROCESS=$INPROCESS/replicate$DB.inprocess

# Exit subroutine
# This subroutine takes three parameters.
# "Error code" - This gives the status the program will exit with
# 0 - OK, not 0 - error
# "Message" - The message you want in the log file
# "Keep_file" - This parameter is optional. It allows you to exit
# but leave the replicate.inprocess file intact
exit_code()
{
echo >> $LOG

if [ $1 != 0 ] # If it is a bad exit code display "ERROR" header
then
echo "###### ERROR ###### ERROR ###### ERROR ######" >> $LOG
echo >> $LOG
fi

echo $2 >> $LOG # Regardless of the exit code display a message

if [ $1 != 0 ] # If it is a bad exit code display "ERROR" footer
then
echo >> $LOG
echo "###### ERROR ###### ERROR ###### ERROR ######" >> $LOG
fi
echo >> $LOG

## Error mailing code
# if [ $1 != 0 ]
# then
# mailx -s "Error in replication for $DB" $WHOAMI < $LOG # else # mailx -s "replication for $DB Successful" $WHOAMI < $LOG # fi if [ x$3 = "x" ] then rm $INPROCESS fi exit $1 } # Logging subroutine # One parameter - The message you want in the log file log_code() { echo >> $LOG
echo $1 >> $LOG
echo >> $LOG
}

# Check if another replication or AI archive process is running
if test -s $INPROCESS # If the inprocess file exists
then # Give a message to the screen and exit
echo
echo "Replication or AI archive process for "$DB" already in process"
echo "Found $INPROCESS file - It can be removed if no previous"
echo "AI Archive or replication for $DB is running"
exit 1
else # Archive the the log file and create a new one
mv $LOG.* $BKLOG 2>/dev/null
LOG=$LOG.$DOW
log_code "Starting replication process for "$DB
date >> $LOG
date > $INPROCESS
fi

# Check to see if the backup node is available
ping -s 1024 -c 2 $RMTNODE >/dev/null
if [ $? != 0 ]
then
exit_code "1" "The backup node "$RMTNODE" is not available"
else
log_code "Backup node is available for transfer"
fi

# If there are less than 9 full extents switch extents so that the busy
# extent is marked as full and can be backed up

FULLAIEXT=`rfutil $DB_DIR/$DB -C aimage extent list grep -i full wc -l`
if [ $FULLAIEXT -lt 6 ]
then
log_code "Switching AI extents for $DB"
_rfutil $DB_DIR/$DB -C aimage extent new
else
log_code "All extents full for $DB - No AI switch"
fi

if [ $? != 0 ]
then
exit_code "1" "Could not switch after image extents"
else
log_code "AI switch for $DB_DIR/$DB complete"
fi

# Copy all extents marked full to the backup directory and make
# them as empty extents so that it can be reused
FIRST_LOOP="yes"

while true

do

EXTENT_NAME=`_rfutil $DB_DIR/$DB -C aimage extent full`

if [ $? = 0 ]
then # Full extent available to copy

# Get the directory and file name to be copied from
DAY_TIME=`date "+%a%H%M%S"` # Get current day and time

# Copy full ai file to backup directory and then compress them
if [ $FIRST_LOOP = "yes" ]
then
log_code "Removing AI file(s) older than 3 days"
find $BKAIDIR/$DB* -mtime 3 -exec \rm {} \; 2>/dev/null >/dev/null
find $BKAIDIR/$DB* -mtime 4 -exec \rm {} \; 2>/dev/null >/dev/null
find $BKAIDIR/$DB* -mtime 5 -exec \rm {} \; 2>/dev/null >/dev/null
fi

log_code "doing copy of $EXTENT_NAME to $BKDIR/$DB.$DAY_TIME"
cp $EXTENT_NAME $BKAIDIR/$DB.$DAY_TIME

if [ $? != 0 ] # Error if copy failed

then

exit_code "1" "Copy of AI file to backup directory failed"

fi

sub_replicate_db continued

# Copy the extent to the backup node

if [ $FIRST_LOOP = "yes" ] # First, clean up last weeks extents
then
# We will keep 3 days of AI history then remove it.
log_code "Removing AI file(s) older than 3 days from $RMTNODE:$RMTAIDIR"

find $BKAIDIR/$DB* -mtime +3 -exec \rm {} \; 2>&1 >/dev/null

ssh $WHOAMI@$RMTNODE "find $RMTAIDIR/$DB* -mtime +3 -exec \rm {};" 2>&1 >/dev/null

FIRST_LOOP="no"
fi

# Compression REMOVED due to lack of universal compatibility
# log_code "Compressing $BKAIDIR/$DB.$DAY_TIME in background"
# compress $BKAIDIR/$DB.$DAY_TIME &

log_code "Copying $EXTENT_NAME to $RMTNODE:$RMTAIDIR/$DB.$DAY_TIME"
scp $EXTENT_NAME $WHOAMI@$RMTNODE:$RMTAIDIR/$DB.$DAY_TIME
if [ $? != 0 ]
then
exit_code "1" "Remote copy of $EXTENT_NAME failed"
else
log_code "Remote copy of $EXTENT_NAME successful"
fi

# Roll forward AI file on the backup node

ssh $WHOAMI@$RMTNODE $SCRIPTS/live/roll_forward_ai $RMTDBDIR/$DB $RMTAIDIR/$DB.$DAY_TIME
if [ $? != 0 ]
then
exit_code "1" "Roll forward failed"
else
log_code "Roll forward of $RMTAIDIR/$DB.$DAY_TIME successful."
fi

# Compression REMOVED due to lack of universal compatibility
# rsh $RMTNODE "compress $RMTAIDIR/$DB.$DAY_TIME &"

# Make the extent empty for reuse
_rfutil $DB_DIR/$DB -C aimage extent empty $EXTENT_NAME 2>/dev/null 1>/dev/null
if [ $? != 0 ]
then
exit_code "1" "Could not empty $EXTENT_NAME"
else
log_code "Extent $EXTENT_NAME marked as empty"
fi

else # No more full extents

date >> $LOG
exit_code "0" "No full extents - procedure complete"
break

fi

done # end of loop

roll_forward_ai

#!/bin/sh
# Program: roll_forward_ai
# Purpose: This script rolls forward a single AI extent
# from the backup directory
# into the warm spare database
#
# Syntax: roll_forward_ai
#
# Author: WSS
# Date Written: February 2008
#

# Setup the generic variables
SCRIPTS=${SCRIPTS-/u/scripts}
DLC=${DLC-/u/progress/9.1e}
PATH=$PATH:/u/progress/9.1e/bin
export SCRIPTS DLC PATH

# Roll forward AI file on the backup node
$DLC/bin/rfutil $1 -C roll forward -a $2 -B 5000 -TB 31 -TM 32
# 2>/dev/null 1>/dev/null
if [ $? != 0 ]
then
echo "1"
else
echo "0"
fi

So, there you go. The Explanatory PDF and the scripts are available at the White Star Web Site: http://www.wss.com/products/downloads.html Again, read the PDF documentation. Read the disclaimers (again). Let us know how you get on...

Calculate Working Days

IF Employer = "WSS" THEN workDaysPerYear = 365.25

Earlier in the year I needed to write a routine to calculate "Working Days" for a client. Rather than reinvent the wheel (and succumbing to my lazy streak) I went out to PEG and begged help.

Richard Elvin from Franklin Templeton sent me some rather elegant code to count weekends but the balance of opinion was that I'd need to have a database table to be able to count Public Holidays / Bank Holidays /Federal Holidays (substitute your own country's nomenclature).


The weekend calculation which has been rewritten into a structured program (workDays.p)has for its main logic

=========================================================================

/* If from date after to date swap them
 * over, and return negative answer...
 */
IF pdtDateTo < pdtDateFrom
THEN ASSIGN gdtTemp     = pdtDateFrom
            pdtDateFrom = pdtDateTo
            pdtDateTo   = gdtTemp
            giSign      = -1.


ASSIGN giDays = pdtDateTo - pdtDateFrom.

/* Deduct Weekends...
 * Date - WEEKDAY( Date ) gives
 * the Saturday Preceding Date
 */
ASSIGN giWeekEnds = TRUNCATE( ( ( pdtDateTo - WEEKDAY( pdtDateTo ) )
   - (pdtDateFrom - WEEKDAY( pdtDateFrom ) ) ) / 7, 0 ).

IF WEEKDAY( pdtDateTo ) = 7 THEN giDays = giDays - 1.
IF WEEKDAY( pdtDateFrom ) = 1 THEN giDays = giDays - 1.

ASSIGN giWorkDays = giDays - ( giWeekEnds * 2 ).

=========================================================================

Pretty neat huh? This, of course will work anywhere that has Saturdays and Sundays as "days off". If your part of the world doesn't get Saturdays and Sundays off, I apologise but at least you're beginning to see what it's like working at White Star.

It turned out that the company that I was writing the code for DID have a database table for holidays so all was happy. And then I got to thinking...

Well, you know how it is, you can either fill in your tax returns and get your paperwork up to date or you can play with the language. Of course, being a good citizen, I "put off until tomorrow" the paperwork and started researching holiday calculations (wonderful place that wikipedia!). Mostly pretty straight forward as it turns out. First Monday in May, Fourth Thursday in November - that kind of thing. And then I hit Easter.

If you live in the USA then who cares? But in the UK everyone gets either one or both of Good Friday and Easter Monday off.

Wikipedia gave me a choice. In the end I chose the Meeus/Jones/Butcher Gregorian algorithm because it was a straight calculation and didn't need any intermediate tables and it looks like this...


=========================================================================

DEFINE INPUT PARAMETER pYear AS INTEGER NO-UNDO.

/* First, find Easter Sunday. */

DEFINE VARIABLE iaa AS INTEGER NO-UNDO.
DEFINE VARIABLE ibb AS INTEGER NO-UNDO.
DEFINE VARIABLE icc AS INTEGER NO-UNDO.
DEFINE VARIABLE idd AS INTEGER NO-UNDO.
DEFINE VARIABLE iee AS INTEGER NO-UNDO.
DEFINE VARIABLE iff AS INTEGER NO-UNDO.
DEFINE VARIABLE igg AS INTEGER NO-UNDO.
DEFINE VARIABLE ihh AS INTEGER NO-UNDO.
DEFINE VARIABLE iii AS INTEGER NO-UNDO.
DEFINE VARIABLE ijj AS INTEGER NO-UNDO.
DEFINE VARIABLE ikk AS INTEGER NO-UNDO.
DEFINE VARIABLE ill AS INTEGER NO-UNDO.
DEFINE VARIABLE imm AS INTEGER NO-UNDO.
DEFINE VARIABLE iMonth AS INTEGER NO-UNDO.
DEFINE VARIABLE iDay AS INTEGER NO-UNDO.

ASSIGN iaa = pYear MOD 19
       ibb = TRUNCATE (pYear / 100, 0)
       icc = pYear MOD 100
       idd = TRUNCATE (ibb / 4, 0)
       iee = ibb MOD 4
       iff = TRUNCATE ( (ibb + 8) / 25, 0)
       igg = TRUNCATE ( (ibb - iff + 1) / 3, 0)
       ihh = (19 * iaa + ibb - idd - igg + 15) MOD 30
       iii = TRUNCATE (icc / 4, 0)
       ikk = icc MOD 4
       ill = (32 + 2 * iee + 2 * iii - ihh - ikk) MOD 7
       imm = TRUNCATE ((iaa + 11 * ihh + 22 * ill) / 451, 0)
       iMonth = TRUNCATE ((ihh + ill - 7 * imm + 114) / 31, 0)
       iDay = ((ihh + ill - 7 * imm + 114) MOD 31) + 1.

CREATE ttBankHoliday.
ASSIGN ttBankHoliday.dtDate = DATE (iMonth, iDay - 2, pYear).

IF piRegionCode NE 3 /* Scotland */ THEN DO:
   CREATE ttBankHoliday.
   ASSIGN ttBankHoliday.dtDate = DATE (iMonth, iDay + 1, pYear).
END.

=========================================================================

Notice the STRICT adherance to naming standards for all those integers!!

Having got England/Wales working I did some extra coding for Northern Ireland and for Scotland and then, because it's nearly 50% of the Progress community, I had a go at the good old United States of America. Hey, mostly straightforward and then I got totally beaten by "Inauguration Day". Let's see 20th January every 4th year after an election. A Monday if the 20th's a Sunday but no time off if the 20th is a Saturday - Oh and no time off unless you're a federal employee in DC or a bunch of other places...

Anyway there's three pieces of code for you on the Web Site - stubWorkDays.w ( a driver for workDays.p), workDays.p and bankHolidayTT.p which will subtract those Public / Bank / Whatever holidays. Get the zip file at http://www.wss.com/products/downloads.html

If you disagree strongly with the algorithms, drop me a line at alan@wss.com. Likewise, if you come up with any improvements. CAN the holidays of rest of the World be calculated?

Welcome to Progressions on-line!

Welcome to Progressions on-line! After over 10 years of print and e-distribution, Progressions has evolved to an on-line resource, free to all. (Whoever imagined blogs when Ethan Lish wrote about the interest-group predecessor to the PEG in issue #1 of Progressions?) We're happy to make this available to everyone, and encourage all of you to submit ideas, problems, experiences, things you've learned from the PEG or from your work, so that everyone can take advantage of the collective body of knowledge that has accumulated around Progress over almost 2 ½ decades. We will be encouraging many of the contributors to Progressions to submit for this weblog as well, and intend to build it into a useful repository of ideas on all sorts of topics.

Again, Welcome!

John Campell