#! /bin/sh
Sccsid="@(#) logmon 1.7 7/9/96 16:07:52"
##########################################################################
# logmon
# Logmon is a script to aid replication users upgrade from 4.9/10.x to 11.0
# SYBASE SQL Server. Because the transaction log format changed between
# 4.9/10.x and 11.0 SYBASE SQL Servers, the transaction logs of replicated
# databases need to be drained before booting the 11.0 SQL Server against
# the 4.9/10.x master device. This script will monitor the draining of
# transaction logs of replicated databases (ie having a valid LTM Truncation
# Point set) for a given SQL Server.
# After draining all of the logs, logmon will generate a script that the
# user can use to reset the LTM Truncation Points after upgrading to 11.0.
#
#
# History:
#	19-Jan-1996  (francis)
#		- Created
#	26-Jan-1996  (francis)
#		- Changed -P to accept a environment variable or filename.
#	29-Jan-1996  (francis)
#		- Fixed the following..
#		  o Don't pass -P to isql, instead, let it prompt and 
#		    then reply.
#		  o Check for isql errors in addition to isql process
#		    status completion.
#		  o If 10.x, check for sybase_ts_role.
#	30-Jan-1996  (francis)
#		- Add -version flag.
#		- If -P<pwd> is an env variable, use another shell to get
#		  it's value in cause user's env variable is the same as
#		  a local variable of this script. (ie if user does -Pxxxx,
#		  and xxxx is a local variable of this script)
#	01-Feb-1996  (francis)
#		- Beefed up exception handler. If user interrupts after we
#		  have turned off the LTM Trunc. Pts, display a list of 
#		  dbs that have been disabled. User will have to re-enable 
#		  them manually.
#		- Fixed various spelling typos.
#	11-Mar-1996  (francis)
#		- Replaced 'expr index' and 'expr length' with more portable
#		  ways (index and length is not portable).
#		- Fixed other syntax errors found.
#	12-Mar-1996  (francis)
#		- Use version of 'tr' based on bsd vs sysv.
#	10-Jul-1996  (francis)
#		- When checking SQL Server versions, accept the following..
#     		  4.9:	0*4.0*9.[0-9][0-9]*.[0-9][0-9]*
#			0*4.0*9.[0-9][0-9]*
#    		  10.x:	10.[0-9][0-9]*.[0-9][0-9]*.[0-9][0-9]*
#    		  	10.[0-9][0-9]*.[0-9][0-9]*
#

##########################################################################
# Usage: logmon -S<servername> -U<username> -P<password>
#		[ -l<filename> ] [ -n<sleep_interval> ] [ -help ] [ -version ]
# Set up some global variables

# If an exception occurs, exit
dbs_done=""
trap 'if [ "%${dbs_done}%" != "%%" ];
      then
	    echo "$cmd: Interrupted with some LTM Truncation Points turned off"
	    echo "$cmd: You will have to reset the following LTM Truncation"
	    echo "$cmd: Points manually:"
	    for db in ${dbs_done};
	    do
		echo "$cmd:       ${db}"
	    done
      fi	
      exit 1' 1 2 3 15

# Set up some common variables
cmd=logmon
cmd_line="${0} ${*}"
vers_str="$cmd: Version logmon, 7/9/96 16:07:52"
usage="
       Usage: $cmd -S<servername> -U<username> -P<password>
		 [ -l<filename> ] [ -n<sleep_interval> ] [ -help ] [ -version ]

       where   -S<servername>  - Interface entry of the 4.9 or 10.x SYBASE SQL 
				 Server that contains replicated databases 
				 that need to have their logs monitored.
	       -U<username>    - SYBASE user with adequate SA privileges
			         (usually sa)
	       -P<password>    - Password of <username>.
				 where <password> may be..
				 o a non-null environment variable containing
				   the password, 
				 o a filename of a file containing the 
				   password, 
				 o the actual password
	       -l<filename>    - Filename to store the SQL commands that
				 restore the LTM Truncation Point(s).
				 Default is \$SYBASE/$cmd.<servername>.
	       -n<sleep_interval> - Time (in secs) to sleep before checking 
				 the SQL Server when monitoring logs.
				 Default is 5 secs.
	       -help           - Display this text.
	       -version        - Display the version.
      
       The environment variable SYBASE must be set.

       Logmon should be run against a 4.9 or 10.x SYBASE SQL Server prior to 
       upgrading to 11.0 SYBASE SQL Server. Logmon monitors the transaction 
       logs of replicated databases while the transactions in the logs are 
       being drained. 
       When the transaction logs are eventually drained, the LTM Truncation 
       Points of the replicated databases are turned off. When all of the 
       replicated databases have been drained, the 4.9/10.x SYBASE SQL Server 
       can be upgraded to 11.0 SYBASE SQL Server. 
       Logmon will generate a script to reset the LTM Truncation Point in the
       replicated database, this script needs to be run against the upgraded
       server to restore the LTM Truncation Point in the replicated databases.
       To prevent loss of replicated data, any applications that utilize 
       replication should not be run until after the 11.0 SYBASE SQL Server 
       is upgraded.
"

svr_name=""
svr_flag=""
usr_name=""
usr_flag=""
password=""
pwd_flag=""
cmd_file=""
sleep_int=5
here=`pwd`

##########################################################################
# Process command line
error=false
while [ "%${1}%" != "%%" ];
do
    case ${1} in
	-S*) svr_flag=${1}
	     svr_name=`expr ${1} : '-S\(.*\)'`
	     ;;
	-U*) usr_flag=${1}
	     usr_name=`expr ${1} : '-U\(.*\)'`
	     ;;
	-P*) pwd_flag=${1}
	     password=`expr ${1} : '-P\(.*\)'`
	     ;;
	-l*) cmd_file=`expr ${1} : '-l\(.*\)'`
	     ;;
	-n*) sleep_int=`expr ${1} : '-n\(.*\)'`
	     if [ 0 -eq `expr "${sleep_int}" : '[0-9][0-9]*'` ];
	     then
		   echo "$cmd: -n<number> expected."
		   error=true
	     fi
	     ;;
   	-help ) echo "${usage}"
	     exit 0
	     ;;
	-version ) 
	     echo "${vers_str}"
	     exit 0
	     ;;
	*  ) echo "$cmd: Unknown option '${1}'."
	     error=true
	     ;;
    esac
    if [ "${error}" = "true" ];
    then
	    echo "$cmd: Please rerun '$cmd' after verifying command line flags."
	    exit 1
    fi
    shift
done

##########################################################################
# Check that -S, -U, and -P were provided.
[ "%${svr_flag}%" = "%%" -o "%${usr_flag}%" = "%%" \
			 -o "%${pwd_flag}%" = "%%" ] && \
    { echo "$cmd: The -S, -U, and -P flags are required.";
      echo "$cmd: Please rerun '$cmd' after verifying command line flags.";
      exit 1; }

##########################################################################
# Verify environment variable SYBASE
[ "%${SYBASE}%" = "%%" ] && \
	{ echo "$cmd: The environment variable SYBASE needs to be defined.";
	  echo "$cmd: Please rerun 'cmd' after defining SYBASE.";
	  exit 1; }


##########################################################################
# The isql command line
# Make sure that isql exists and is executable
# Use -w200 to force wide output
isql=${SYBASE}/bin/isql
[ -f ${isql} -a -x ${isql} ] || \
	{ echo "$cmd: '${isql}' does not exist, or is not executable.";
	  echo "$cmd: Please check the SYBASE environment variable.";
	  echo "$cmd: Please rerun '$cmd' after resolving this problem.";
	  exit 1; }
isql="${isql} ${svr_flag} ${usr_flag} -w200"

##########################################################################
# Check if 'echo -n' is supported.
# If echo does not recognize '-n', then it will echo back '-n testing'
# Otherwise, it will echo 'testing'
echon=echo
[ "-n testing" != "`echo -n testing`" ] && echon="echo -n"

##########################################################################
# Check which version of tr to use
# On bsd, use tr 'a-z' 'A-Z'
# On sysv, use tr '[a-z]' '[A-Z]'
tr_cmd="tr '[a-f]' '[A-F]'"
[ "ABCDEF" = "`echo abcdef | tr a-f A-F`" ] && tr_cmd="tr a-f A-F"

##########################################################################
# ask() - General purpose y/n prompter
# $1 - prompt string, $2 - variable to return answer in
# Accepts Y, y, N, n and /n
ask()
{
    ask_ans=""
    until [ 0 -ne 0 ];
    do
	$echon "${1}"
	read ask_ans
	[ "%${ask_ans}%" = "%%" -o "${ask_ans}" = "Y" -o "${ask_ans}" = "y" \
		                -o "${ask_ans}" = "N" -o "${ask_ans}" = "n" ] \
	    && eval ${2}=${ask_ans} \
	    && break
    done
}

##########################################################################
# backup_file() - Create a backup filename
# $1 - filename, $2 - backup filename
# First try <filename>.bak
# If it exists, try <filename>.NNN, until one is found that does not exist
backup_file()
{
    bfname="${1}.bak"
    ext_cnt=0
    ext_str=""
    ext_len=0
    while [ -f ${bfname} ];
    do
	ext_len=`expr "${ext_cnt}" : '.*'`
	[ ${ext_len} -eq 1 ] && ext_str="00${ext_cnt}"
	[ ${ext_len} -eq 2 ] && ext_str="0${ext_cnt}"
	[ ${ext_len} -ge 3 ] && ext_str="${ext_cnt}"
	ext_cnt=`expr ${ext_cnt} + 1`
	bfname="${1}.${ext_str}"
    done
    eval ${2}=${bfname}
}

##########################################################################
# Confirm the servername
ask "$cmd: Monitor '${svr_name}' for replicated databases? [Y/n]" ans
[ "%${ans}%" = "%%" -o "${ans}" = "Y" -o "${ans}" = "y" ] || \
	{ echo "$cmd: Please check the command line flags specified.";
	  echo "$cmd: Please rerun '$cmd' after verifying command line flags.";
	  exit 1; }

##########################################################################
# Process the password
# Is <password> 
#	- an environment variable containing the password?
# 	- a filename of a file containing the password?
# 	- the actual password?
if [ "%${password}%" != "%%" ];
then
	sh_cmd="echo \$${password}"
	pwd_env=`( /bin/sh -c "${sh_cmd}" )`
	has_slash=`echo "${password}" | sed -n -e 's:/:/:p'`
	if [ "%${has_slash}%" = "%%" -a "%${pwd_env}%" != "%%" ];
	then
		password="${pwd_env}"
		pwd_flag="-P${password}"
	elif [ -f ${password} ];
	then
		password="`cat ${password}`"
		pwd_flag="-P${password}"
	fi
fi

##########################################################################
# Query if all replicated applications are shutdown.
ask "$cmd: Have all replicated applications been shutdown? [Y/n]" ans
[ "%${ans}%" = "%%" -o "${ans}" = "Y" -o "${ans}" = "y" ] || \
    { echo "$cmd: All replicated applications need to be shutdown before";
      echo "$cmd:      proceeding with log draining.";
      echo "$cmd: When all replicated applications have been shutdown,";
      echo "$cmd:      you may rerun the command:";
      echo "$cmd:      '$cmd'.";
      exit 1; }

##########################################################################
# Verify that we can login into SQL Server
echo "$cmd: Testing connection to '${svr_name}'"
rslt=`( echo ${password}
	echo "quit" ) | ${isql} > /dev/null 2>&1`

# Check command execution status
[ ${?} -ne 0 ] && \
   { echo "$cmd: Can't log into server '${svr_name}'.";
     echo "$cmd: Please rerun '$cmd' after verifying command line flags.";
     exit 1; }

# Check for isql error
err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
[ "%${err}%" != "%%" ] && \
   { echo "$cmd: Internal error logging into server '${svr_name}'.";
     exit 1; }

##########################################################################
# Select the SQL Server version
# Execute 'select @@version' to get the SYBASE SQL Server version
# The version string is in the format ' SQL Server/<version>/.*'
# The sed script below, extracts just the version part of the version string
echo "$cmd: Verifying the SYBASE SQL Server version."
rslt=`( echo ${password}
	echo "select @@version"
	echo "go" ) | ${isql}`

# Check command execution status
[ ${?} -ne 0 ] && \
    { echo "$cmd: Error getting SQL Server Version." ;
      echo "$cmd: Check if server '${svr_name}' is still up." ;
      exit 1; }

# Check for isql error
err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
[ "%${err}%" != "%%" ] && \
    { echo "$cmd: Error getting SQL Server Version.";
      echo "$cmd: Wrong number of rows returned.";
      exit 1; }

# Extract the rowcount
rowcnt=0
rowcnt=`echo "${rslt}" | \
	sed -n -e '/rows* affected/s/^(\([0-9][0-9]*\) row.*$/\1/p'`

# Only one row should be returned
[ ${rowcnt} -ne 1 ] && \
    { echo "$cmd: Error getting SQL Server Version.";
      echo "$cmd: Wrong number of rows returned.";
      exit 1; }

##########################################################################
# Verify that SQL Server version is 4.9 or 10.x
# Accept only 4.9 or 10.x SYBASE SQL Server versions.
# The accepted version strings are..
#     4.9:	0*4.0*9.[0-9][0-9]*.[0-9][0-9]*
#    10.x:	10.[0-9][0-9]*.[0-9][0-9]*.[0-9][0-9]*
vstr=`echo "${rslt}" | sed -n -e '/ SQL Server\//p'`
vers=`expr "${vstr}" : ' SQL Server/\([^/][^/]*\)/.*$'`
is49=`expr ${vers} : '0*4\.0*9\.[0-9][0-9]*\.[0-9][0-9]*.*' \| \
	   ${vers} : '0*4\.0*9\.[0-9][0-9]*'`
is10=`expr ${vers} : '10\.[0-9][0-9]*\.[0-9][0-9]*\.[0-9][0-9]*.*' \| \
	   ${vers} : '10\.[0-9][0-9]*\.[0-9][0-9]*'`
[ ${is49} -eq 0 -a ${is10} -eq 0 ] && \
	{ echo "$cmd: SQL Server version must be 4.9 or 10.x";
	  echo "$cmd:      got '${vstr}'";
	  echo "$cmd: Please rerun '$cmd' after resolving server version.";
	  exit 1; }

##########################################################################
# Does user have sybase_ts_role
# Only 10.x has roles
if [ ${is10} -ne 0 ];
then
    rslt=`( echo ${password}
	    echo "select  convert(tinyint, isnull(current_roles(), 0x0))"
	    echo "	& convert(tinyint, 0x08)"
	    echo "go" ) | ${isql}`

    # Check command execution status
    [ ${?} -ne 0 ] && \
	{ echo "$cmd: Error getting sybase_ts_role";
	  echo "$cmd: Check if server '${svr_name}' is still up.";
	  exit 1; }

    # Check for isql error
    err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
    [ "%${err}%" != "%%" ] && \
	{ echo "$cmd: Internal error getting sybase_ts_role";
	  exit 1; }

    # Extract the role
    role=0
    role=`echo "${rslt}" | sed -e '1,3d' -e '/^$/d' -e '/^(/d'`

    # User has to have sybase_ts_role
    [ ${role} -ne 8 ] && \
	{ echo "$cmd: User '${usr_name}' does not have sybase_ts_role";
	  echo "$cmd: Check username, or grant sybase_ts_role to '${usr_name}'";
	  exit 1; }
fi

##########################################################################
# Generate the cmd_file name
if [ "%${cmd_file}%" != "%%" ];
then
	##################################################################
	# Create the full pathname to ${cmd_file} if path is relative
	fpath=`expr /${cmd_file} : '/\(/.*\)' \| ${here}/${cmd_file}`

	##################################################################
	# If ${cmd_file} is a directory, abort
	[ -d ${fpath} ] && \
		{ echo "$cmd: '${fpath}' is a directory.";
		  echo "$cmd: Please check the '-l' command line flag.";
		  exit 1; }
	cmd_file=${fpath}
else
	##################################################################
	# Otherwise, generate the cmd_file name, $SYBASE/$cmd.$svr_name
	cmd_file=${SYBASE}/${cmd}.${svr_name}
fi

##########################################################################
# Get list of all databases
# Execute 'select name from master..sysdatabases' to get list of all databases.
# The sed script strips out lines 1 and 2, blank lines, and 
# lines starting with '('
echo "$cmd: Getting list of replicated databases from '${svr_name}'"
rslt=`( echo ${password}
	echo "select name from master..sysdatabases"
        echo "go") | ${isql}`

# Check command execution status
[ ${?} -ne 0 ] && \
	{ echo "$cmd: Error selecting from sysdatabases.";
	  echo "$cmd: Check if server '${svr_name}' is still up.";
	  exit 1; }

# Check for isql error
err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
[ "%${err}%" != "%%" ] && \
    { echo "$cmd: Error getting SQL Server Version.";
      echo "$cmd: Wrong number of rows returned.";
      exit 1; }

# Extract the rowcount
rcount=0
rcount=`echo "${rslt}" | \
	sed -n -e '/rows* affected/s/^(\([0-9][0-9]*\) row.*$/\1/p'`

# Better have at least one database
[ ${rcount} -eq 0 ] && \
    { echo "$cmd: Internal error selecting from sysdatabases.";
      exit 1; }

# Extract the database list
dbs=`echo "${rslt}" | sed -e '1,3d' -e '/^$/d' -e '/^(/d'`

##########################################################################
# Get list of replicated dbs
# Execute a 'dbcc dbinfo()' for each database.
# A database is replicated if dbinfo.dpi_rep_stat has the 1 bit set (the
# 1 bit means that the LTM Truncation Point is valid).
rep_dbs=""
for db in ${dbs};
do
	##################################################################
	# Exec 'dbcc dbinfo' to get dbinfo.dbi_rep_stat
	# The sed script selects the line beginning with 'dbi_repstat:'
	# and returns the value.
	rslt=`( echo ${password}
		echo "dbcc traceon (3604)"
		echo "go"
		echo "dbcc dbinfo(${db})"
		echo "go" ) | ${isql}`

	# Check the command execution status
	[ ${?} -ne 0 ] && \
		{ echo "$cmd: Error getting dbi_rep_stat.";
		  echo "$cmd: Check if server '${svr_name}' is still up.";
		  exit 1; }

	# Check for isql error
	err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
	[ "%${err}%" != "%%" ] && \
		{ echo "$cmd: Internal error getting LTM Truncation Pt.";
		  exit 1; }
	
	# Extract the status
        trunc_state=`echo "${rslt}" \
	  | sed -n -e '/dbi_rep_stat:/s/^dbi_rep_stat: \([0-9][0-9]*\)$/\1/p'`
	
	##################################################################
	# Check if dbinfo.dbi_rep_stat has the 1 bit set
	# Mod dbi_repstat by 2 to see if 1 bit is set
	trunc_state=`expr ${trunc_state} % 2`

	##################################################################
	# If 1 bit is set, database is replicated
	[ ${trunc_state} -ne 0 ] && rep_dbs="${rep_dbs} ${db}"
done

##########################################################################
# If there are no replicated dbs, there is nothing to do, bail
[ "%${rep_dbs}%" = "%%" ] && \
	{ echo "$cmd: There are no replicated databases in '${svr_name}'.";
	  echo "$cmd: It is safe to upgrade this server to 11.0.";
	  exit 1; }

##########################################################################
# Tell user which dbs are replicated.
echo "$cmd: The following replicated databases require log draining:"
for db in ${rep_dbs};
do
	echo "$cmd:         ${db}"
done

##########################################################################
# Give the user the last chance to bail
echo "$cmd: Ready to monitor transaction log draining."
echo "$cmd: Be prepared to start SYBASE Replication Servers and LTMs if they"
echo "$cmd:      are not already running."
ask "$cmd: Do you want to continue? [Y/n]" ans
[ "%${ans}%" = "%%" -o "${ans}" = "Y" -o "${ans}" = "y" ] || exit 0

##########################################################################
# Check if ${cmd_file} already exists
# If it exists, back it up.
if [ -f ${cmd_file} ];
then
	bak_file=""
	backup_file "${cmd_file}" bak_file
	echo "$cmd: '${cmd_file}' already exists. "
	echo "$cmd: Backing it up as '${bak_file}'."
	mv ${cmd_file} ${bak_file} > /dev/null 2>&1
	[ ${?} -eq 0 ] || \
	    { echo "$cmd: Could not backup '${cmd_file}'";
	      echo "$cmd:     as '${bak_file}'.";
	      echo "$cmd: Please rerun '${cmd}' after resolving this problem.";
	      exit 1; }
fi

##########################################################################
# Create ${cmd_file}
touch ${cmd_file} > /dev/null 2>&1
[ ${?} -ne 0 ] && \
	{ echo "$cmd: Could not create '${cmd_file}'.";
	  echo "$cmd: Please rerun '$cmd' after resolving this problem.";
	  exit 1; }

##########################################################################
# Write bourne shell header to ${cmd_file}
today=`date`
cat << EOF0 >> ${cmd_file}
#! /bin/sh 
# This script was generated on '${today}' by '$cmd'.
# The command line that created this script was 
#	'${cmd_line}'
# Use this script to restore the LTM Truncation Points in
# replicated databases of server 
#	'${svr_name}'.
# This script can be executed by the command
#	'sh ${cmd_file}'
#
EOF0
[ ${?} -ne 0 ] && \
	{ echo "$cmd: Could not write header to '${cmd_file}'.";
	  echo "$cmd: Please rerun '$cmd' after resolving this problem.";
	  exit 1; }

##########################################################################
# For each replicated db, wait until LTM Trunc. Pt. has passed the 
# current last log page.
# First get the current last log page and the timestamp of the current 
# last log page, this is the old log end.
# Execute 500 begin/ends to extend the log past the current log end, 
# this is the new log end.
# Get the LTM Trunc. Pt. Page and its timestamp.
# Continue doing this until the LTM Trunc. Pt. is passed the old log end.
for db in ${rep_dbs};
do
    ######################################################################
    # Let user know what's going on
    echo "$cmd: Ready to monitor replicated database '${db}'"

    ######################################################################
    # Checkpoint database to get an accurate last log page
    # Execute a checkpoint to ensure that dbinfo is accurate
    echo "$cmd: Checkpointing '${db}'"
    rslt=`( echo ${password}
	    echo "use ${db}"
	    echo "go"
      	    echo "checkpoint"
      	    echo "go" ) | ${isql} > /dev/null 2>&1`

    # Check command execution status
    [ ${?} -ne 0 ] && \
	    { echo "$cmd: Error checkpointing '${db}'.";
	      echo "$cmd: Check if server '${svr_name}' is still up.";
	      exit 1; }

    # Check for isql error
    err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
    [ "%${err}%" != "%%" ] && \
	    { echo "$cmd: Internal error checkpointing '${db}'.";
	      exit 1; }

    ######################################################################
    # Get the last log page via 'dbcc dbinfo'. 
    # Last logpage is dbinfo.dbi_checkpt
    # The sed script selects lines beginning with 'dbi_checkpoint' and
    # returns the value.
    rslt=`( echo ${password}
	    echo "dbcc traceon (3604)"
	    echo "go"
	    echo "dbcc dbinfo(${db})"
	    echo "go" ) | ${isql}`

    # Check command execution status
    [ ${?} -ne 0 ] && \
	    { echo "$cmd: Error getting last log page of '${db}'.";
	      echo "$cmd: Check if server '${svr_name}' is still up.";
	      exit 1; }

    # Check for isql error
    err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
    [ "%${err}%" != "%%" ] && \
	    { echo "$cmd: Internal error getting last log page of '${db}'.";
	      exit 1; }

    # Extract the last log page
    log_end=`echo "${rslt}" | \
       sed -n -e '/^dbi_checkpt/s/^dbi_checkpt: page \([0-9][0-9]*\),.*$/\1/p'`

    ######################################################################
    # Get the timestamp of last log page using 'dbcc page'
    # The sed script selects lines beginning with 'pageno=' and
    # returns the timestamp (hi and lo).
    rslt=`( echo ${password}
	    echo "dbcc traceon (3604)"
	    echo "go"
	    echo "dbcc page (${db}, ${log_end}, 0)"
	    echo "go" ) | ${isql}`

    # Check command execution status
    [ ${?} -ne 0 ] && \
	    { echo "$cmd: Error getting last page timestamp in '${db}.'.";
	      echo "$cmd: Check if server '${svr_name}' is still up.";
	      exit 1; }

    # Check for isql error
    err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
    [ "%${err}%" != "%%" ] && \
	{ echo "$cmd: Internal error getting last page timestamp in '${db}'.";
	  exit 1; }

    # Extract the timestamp
    ts0=`echo "${rslt}" | sed -n -e \
      '/^pageno=/s/^.*timestamp=\([0-9a-f][0-9a-f]* [0-9a-f][0-9a-f]*\)$/\1/p'`

    ######################################################################
    # Get hi and lo parts of timestamp
    ts0_hi=`expr "${ts0}" : '\([0-9a-f][0-9a-f]*\) [0-9a-f][0-9a-f]*'`
    ts0_lo=`expr "${ts0}" : '[0-9a-f][0-9a-f]* \([0-9a-f][0-9a-f]*\)'`

    ######################################################################
    # Convert from hexidecimal to decimal.
    # The tr script forces hex a-f to upper case.
    # The dc script changes the input radix to 16, pushes a hex number
    # and then prints the hex number in decimal.
    ts0_hi=`echo "16i ${ts0_hi} p" | ${tr_cmd} | dc`

    # Check command execution status
    [ ${?} -ne 0 -o "%${ts0_hi}%" = "%%" ] && \
	    { echo "$cmd: Internal error converting timestamp in '${db}.'.";
	      exit 1; }

    ts0_lo=`echo "16i ${ts0_lo} p" | ${tr_cmd} | dc`

    # Check command execution status
    [ ${?} -ne 0 -o "%${ts0_lo}%" = "%%" ] && \
	    { echo "$cmd: Internal error converting timestamp in '${db}.'.";
	      exit 1; }

    ######################################################################
    # Write 500 begin/ends to log to force extend the log past the
    # current last log page.
    rslt=`( echo ${password}
	    echo "use ${db}"
      	    echo "go"
      	    echo "begin tran"
      	    echo "commit tran"
      	    echo "go 500" ) | ${isql}`

    # Check command execution status
    [ ${?} -ne 0 ] && \
	    { echo "$cmd: Error extending end of log in '${db}'.";
	      echo "$cmd: Check if server '${svr_name}' is still up.";
	      exit 1; }

    # Check for isql error
    err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
    [ "%${err}%" != "%%" ] && \
	{ echo "$cmd: Internal error extending log in '${db}'.";
	  exit 1; }

    # Extract the xact count
    xact_cnt=0
    xact_cnt=`echo "${rslt}" | \
	sed -n -e '/^.*xacts:/s/^\([0-9][0-9]*\) xacts:$/\1/p'`

    # Check that 500 transactions were done
    [ ${xact_cnt} -ne 500 ] && \
	{ echo "$cmd: Internal error extending log in '${db}'.";
	  exit 1; }

    ######################################################################
    # Let the user know what's going on.
    echo "$cmd: Waiting for transaction log of '${db}' to be drained."
    echo "$cmd: If the SYBASE Replication Server and LTM for database"
    echo "$cmd:      '${db}' are not yet running, start them now."

    ######################################################################
    # Loop until log is drained
    # Log is considered drained when the LTM Trunc. Pt. is past the
    # old log end.
    ready=false
    while [ ${ready} != true ];
    do
        ##################################################################
	# Get the LTM Truncation Page with 'dbcc dbinfo'
	# LTM Truncation Page is in dbinfo.dbi_ltmtruncpg
	# The sed script selects lines beginning with 'dbi_ltmtruncpg'
	# and returns the value.
	rslt=`( echo ${password}
		echo "dbcc traceon (3604)"
		echo "go"
		echo "dbcc dbinfo(${db})"
		echo "go" ) | ${isql}`

	# Check command execution status
	[ ${?} -ne 0 ] && \
	    { echo "$cmd: Error getting LTM Trunc. Pt. in '${db}'.";
	      echo "$cmd: Check if server '${svr_name}' is still up.";
	      exit 1; }

	# Check for isql error
	err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
	[ "%${err}%" != "%%" ] && \
	    { echo "$cmd: Internal error getting LTM Trunc. Pt in '${db}'.";
	      exit 1; }

	# Extract the LTM Truncation Page
	ltm_pag=`echo "${rslt}" | sed -n -e \
		 '/^dbi_ltmtruncpg/s/^dbi_ltmtruncpg: \([0-9][0-9]*\)$/\1/p'`

        ##################################################################
	# Get the timestamp of LTM truncation page using 'dbcc page'
	# The sed script select lines beginning with 'pageno='
	# and returns the timestamp (hi and lo).
	rslt=`( echo ${password}
		echo "dbcc traceon (3604)"
		echo "go"
		echo "dbcc page (${db}, ${ltm_pag}, 0)"
		echo "go" ) | ${isql}`

	# Check command execution status
	[ ${?} -ne 0 ] && \
	    { echo "$cmd: Error getting timestamp in '${db}'.";
	      echo "$cmd: Check if server '${svr_name}' is still up.";
	      exit 1; }

	# Check for isql error
	err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
	[ "%${err}%" != "%%" ] && \
	    { echo "$cmd: Internal error getting timestamp in '${db}'.";
	      exit 1; }

	# Extract the timestamp
	ts1=`echo "${rslt}" | sed -n -e \
    '/^pageno=/s/^.*timestamp=\([0-9a-f][0-9a-f]* [0-9a-f][0-9a-f]*\)$/\1/p'`

        ##################################################################
	# Get hi and lo parts of timestamp
	ts1_hi=`expr "${ts1}" : '\([0-9a-f][0-9a-f]*\) [0-9a-f][0-9a-f]*'`
	ts1_lo=`expr "${ts1}" : '[0-9a-f][0-9a-f]* \([0-9a-f][0-9a-f]*\)'`

        ##################################################################
	# Convert from hex to decimal
	# The tr script forces hex a-f to upper case.
	# The dc script changes the input radix to 16, pushes a hex 
	# number, and then prints the hex number in decimal.

	ts1_hi=`echo "16i ${ts1_hi} p" | ${tr_cmd} | dc`

	# Check command execution status
	[ ${?} -ne 0 -o "%${ts1_hi}%" = "%%" ] && \
		{ echo "$cmd: Internal error converting timestamp in '${db}'.";
		  exit 1; }

	ts1_lo=`echo "16i ${ts1_lo} p" | ${tr_cmd} | dc`

	# Check command execution status
	[ ${?} -ne 0 -o "%${ts1_lo}%" = "%%" ] && \
		{ echo "$cmd: Internal error converting timestamp in '${db}'.";
		  exit 1; }

        ##################################################################
	# Compare the old last log page timestamp to the ltm page
	# timestamp.
	# Check if timestamp(old last log page) < timestamp(ltm page).
	# If ( (hi(old) < hi(ltm)) || 
	#      ( (hi(old) == hi(ltm)) && (lo(old) < lo(ltm)) ) )
	if [ ${ts0_hi} -lt ${ts1_hi} \
		-o \( ${ts0_hi} -eq ${ts1_hi} -a ${ts0_lo} -lt ${ts1_lo} \) ];
	then
		#########################################################
		# If last log page timestamp is less than the timestamp
		# of the timestamp of the LTM Trunc. Pt the log can be
		# considered drained.
		# Force a newline if 'echo -n'
		[ "${echon}" != "echo" ] && echo "done"
		ready=true
	else
		#########################################################
		# If the last log page timestamp greater than or equal
		# to the timestamp of the LTM Trunc. Pt the log is not
		# yet drained.
		if [ "${echon}" = "echo" ];
		then
		        #################################################
			# Display message
			echo "$cmd: Sleeping for ${sleep_int} secs."
		else
		        #################################################
			# Display a '.' for each sleep interval.
			${echon} "."
		fi
		sleep ${sleep_int}
		ready=false
	fi
    done

    #####################################################################
    # If done, turn off the truncation point.
    if [ ${ready} = true ];
    then
        #################################################################
	# Let user know whats going on
	echo "$cmd: Replicated database, '${db}' is now drained."
	echo "$cmd: Turning off its LTM Truncation Point."

        #################################################################
	# Tell user to stop the LTM
	echo "$cmd: Please shutdown the LTM for database, '${db}'."
	ask  "$cmd: Enter <return> when the LTM is shutdown [enter]: " ans

        #################################################################
	# Wait for LTM to release context
	sleep 5 

	dbs_done="${dbs_done} ${db}"
        #################################################################
	# Turn off the LTM truncation point
	# Execute 'dbcc settrunc (ltm, ignore)' 
	rslt=`( echo ${password}
		echo "use ${db}"
		echo "go"
		echo "dbcc settrunc ('ltm', 'ignore')"
		echo "go" ) | ${isql}`

	# Check command execution status
	[ ${?} -ne 0 ] && \
	    { echo "$cmd: Error turning off LTM Truncation Pt in '${db}'.";
	      echo "$cmd: Check if server '${svr_name}' is still up.";
	      exit 1; }

	# Check for isql error
	err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
	[ "%${err}%" != "%%" ] && \
	    { echo "$cmd: Internal error turning off LTM Trunc Pt in '${db}'.";
	      exit 1; }
	
        #################################################################
	# Check if LTM truncation point was released or not
	rslt=`( echo ${password}
		echo "use ${db}"
	   	echo "go"
	   	echo "dbcc gettrunc"
	   	echo "go" ) | ${isql}`

	# Check command execution status
	[ $? -ne 0 ] && \
	    { echo "$cmd: Error getting LTM Truncation Pt in '${db}'.";
	      echo "$cmd: Check if server '${svr_name}' is still up.";
	      exit 1; }

	# Check for isql error
	err=`echo "${rslt}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'`
	[ "%${err}%" != "%%" ] && \
	    { echo "$cmd: Internal error getting LTM Truncation Pt in '${db}'.";
	      exit 1; }

	# Extract the LTM Truncation State
	state=`echo "${rslt}" | sed -n -e \
	    '4s/^[^0-9][^0-9]*[0-9][0-9]*[^0-9][^0-9]*\([0-9][0-9]*\).*$/\1/p'`

        #################################################################
	# Get ltm_trunc_state, and check 1 bit
	state=`expr ${state} % 2`
	[ ${state} -eq 1 ] && \
	    { echo "$cmd: The LTM Truncation Pt. in '${db}', could not be";
	      echo "$cmd:      turned off.";
	      echo "$cmd: You will have to turn it off manually with the";
	      echo "$cmd:      following isql commands:";
	      echo "$cmd:          use ${db}";
	      echo "$cmd:          go";
	      echo "$cmd:          dbcc settrunc ('ltm', 'ignore')";
	      echo "$cmd:          go";
	      continue; }
    fi
done

##########################################################################
# All replicated databases have been drained
# Now create the script to reset the LTM Truncation Pts and to
# zero out the locators in the RSSDs.
cat << EOF0 >> ${cmd_file}
trap 'exit 1' 1 2 3 15

##########################################################################
# Servername, user and password
svr_name=${svr_name}
usr_name=${usr_name}
password=${password}

##########################################################################
# Is 'echo -n' available 
echon=echo
[ "-n testing" != "\`echo -n testing \`" ] && echon="echo -n"

##########################################################################
# ask() - general purpose y/n prompter
# \$1 - prompt, \$2 - variable to return answer in
# Accepts Y, y, N, n and /n
ask()
{
    ask_ans=""
    until [ 0 -ne 0 ];
    do
	\${echon} "\${1}"
	read ask_ans
	[ "%\${ask_ans}%" = "%%" \\
		-o "\${ask_ans}" = "Y" -o "\${ask_ans}" = "y" \\
		-o "\${ask_ans}" = "N" -o "\${ask_ans}" = "n" ] \\
	    && eval \${2}=\${ask_ans} \\
	    && break
    done
}

##########################################################################
# Save the command line
cmd=\${0}

##########################################################################
# Verify that SYBASE is set
[ "%\${SYBASE}%" = "%%" ] && \\
	{ echo "The environment variable SYBASE needs to be defined.";
	  echo "Please rerun '\$cmd' after defining SYBASE.";
	  exit 1; }

##########################################################################
# Verify if this is the correct SYBASE
echo "The environment variable SYBASE is set to '\${SYBASE}'"
ask  "Is this correct? [Y/n]" ans
[ "%\${ans}%" = "%%" -o "\${ans}" = "Y" -o "\${ans}" = "y" ] || \\
	{ echo "Please rerun '\$cmd' after redefining SYBASE correctly.";
	  exit 1; }

##########################################################################
# Verify server_name
echo "The server name is '\${svr_name}'"
ask  "Is this correct? [Y/n]" ans
if [ "%\${ans}%" != "%%" -a "\${ans}" != "Y" -a "\${ans}" != "y" ];
then
	\${echon} "Enter server name: "
	read svr_name
fi

##########################################################################
# Verify username
echo "The username is '\${usr_name}'"
ask  "Is this correct? [Y/n]" ans
if [ "%\${ans}%" != "%%" -a "\${ans}" != "Y" -a "\${ans}" != "y" ];
then
	\${echon} "Enter user name: "
	read usr_name
fi

##########################################################################
# Verify password
echo "The password is '\${password}'"
ask  "Is this correct? [Y/n]" ans
if [ "%\${ans}%" != "%%" -a "\${ans}" != "Y" -a "\${ans}" != "y" ];
then
	\${echon} "Enter password: "
	read password
fi

##########################################################################
# Echo the isql command
isql="\${SYBASE}/bin/isql -S\${svr_name} -U\${usr_name} -w200"
echo "The ISQL command is '\${isql}'."

##########################################################################
# Testing connection 
echo "Testing connection to '\${svr_name}'."
( echo \${password}
  echo "quit" ) | \${isql} > /dev/null 2>&1

##########################################################################
# Check the command execution status
[ \${?} -ne 0 ] && \\
   { echo "Can't log into server '\${svr_name}'.";
     echo "Please check your SYBASE definition, and the server name,";
     echo "     user name, and password entered above.";
     echo "Rerun '\$cmd' after resolving this problem.";
     exit 1; }

##########################################################################
# Start to restore the replicated dbs
for db in ${rep_dbs};
do
    ######################################################################
    # Display informational message
    echo "Restoring replicated database '\${db}'."

    ######################################################################
    # Initialize local variables
    rsql=""
    rssd_name=""
    rssd_srvr=""
    rssd_user=""
    rssd_pwrd=""

    ######################################################################
    # Use loop to allow retry if commands fail
    ready=false
    while [ "\${ready}" = "false" ];
    do
	##################################################################
        # Get the RSSD name, servername, username, and password
        \${echon} "Enter the RSSD name that '\${db}' belongs to: "
	read rssd_name
        \${echon} "Enter the RSSD dataserver name that '\${rssd_name}' resides:"
	read rssd_srvr
        \${echon} "Enter the RSSD username for '\${rssd_srvr}': "
	read rssd_user
        \${echon} "Enter the RSSD password of '\${rssd_user}': "
	read rssd_pwrd

	##################################################################
	# Test the connection
	rsql="\${SYBASE}/bin/isql -S\${rssd_srvr} -U\${rssd_user} -w200"
	echo "Testing connection to '\${rssd_srvr}'."
	( echo \${rssd_pwrd}
	  echo "quit" ) | \${rsql} > /dev/null 2>&1

	##################################################################
	# Check the command execution status
	if [ \${?} -ne 0 ];
	then
	    echo "Could not connect to '\${rssd_srvr}'."
	    echo "Check the RSSD name, RSSD server, RSSD username, and"
	    echo "     RSSD password that were entered above."
	    echo "Also check that '\${rssd_srvr}' is in the interfaces file."
	    ask  "Do you want to try again? [Y/n]" ans
	    [ "%\${ans}%" = "%%" -o "\${ans}" = "Y" -o "\${ans}" = "y" ] && \\
		    continue
	    break
	fi

	##################################################################
	# Check that this RSSD is really in this server
	tmpstr=\`( echo \${rssd_pwrd}
		   echo "select count(*) from master..sysdatabases"
		   echo "where name = '\${rssd_name}'" 
		   echo "go" ) | \${rsql}\`

	##################################################################
	# Check the command execution status
	if [ \${?} -ne 0 ];
	then
	    echo "Failure validating '\${rssd_name}'."
	    echo "Check if server '\${rssd_srvr}' is still up."
	    ask  "Do you want to try again? [Y/n]" ans
	    [ "%\${ans}%" = "%%" -o "\${ans}" = "Y" -o "\${ans}" = "y" ] && \\
		    continue
	    break
	fi

	##################################################################
	# Check the command result
	err=\`echo "\${tmpstr}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'\`
	if [ "%\${err}%" != "%%" ];
	then
	    echo "Internal error checking RSSD, \${rssd_name}."
	    exit 1
	fi

	##################################################################
	# Count should not be 0
	cnt=\`echo "\${tmpstr}" | sed -e '1,3d' -e '/^\$/d' -e '/^(/d'\`
	if [ \${cnt} -eq 0 ];
	then
	    echo "Could not find '\${rssd_name}' in '\${rssd_srvr}'."
	    echo "Check the RSSD name, RSSD server, RSSD username, and"
	    echo "     RSSD password that were entered above."
	    ask  "Do you want to try again? [Y/n]" ans
	    [ "%\${ans}%" = "%%" -o "\${ans}" = "Y" -o "\${ans}" = "y" ] && \\
		    continue
	    break
	fi

	##################################################################
	# Check that this RSSD really is for this db
	tmpstr=\`( echo \${rssd_pwrd}
		   echo "use \${rssd_name}"
	           echo "go"
		   echo "select count(*) from rs_databases"
		   echo "where dsname = '\${svr_name}' and dbname = '\${db}'"
		   echo "go" ) | \${rsql}\`

	##################################################################
	# Check the command execution status
	if [ \${?} -ne 0 ];
	then
	    echo "Failure validating '\${rssd_name}'."
	    echo "Check if server '\${rssd_srvr}' is still up."
	    ask  "Do you want to try again? [Y/n]" ans
	    [ "%\${ans}%" = "%%" -o "\${ans}" = "Y" -o "\${ans}" = "y" ] && \\
		    continue
	    break
	fi

	##################################################################
	# Check the command result
	err=\`echo "\${tmpstr}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'\`
	if [ "%\${err}%" != "%%" ];
	then
	    echo "Internal error checking RSSD, \${rssd_name}"
	    exit 1
	fi

	##################################################################
	# Extract the row count
	cnt=\`echo "\${tmpstr}" | sed -e '1,3d' -e '/^\$/d' -e '/^(/d'\`
	if [ \${cnt} -eq 0 ];
	then
	    echo "Could not find row in rs_databases for dsname='\svr_name',"
	    echo "     and dbname='\${db}'"
	    echo "Check the RSSD name, RSSD server, RSSD username, and"
	    echo "     RSSD password that were entered above."
	    ask  "Do you want to try again? [Y/n]" ans
	    [ "%\${ans}%" = "%%" -o "\${ans}" = "Y" -o "\${ans}" = "y" ] && \\
		    continue
	    break
	fi

	##################################################################
	# RSSD name, server, user, and password are good.
	ready=true;
    done

    ######################################################################
    # Skip this database if errors occurred and user did not retry
    if [ "\${ready}" = "false" ];
    then
	echo "Errors occurred while processing '\$db'."
	echo "It will have to be manually restored."
	continue
    fi

    ######################################################################
    # Zero out the locator in the RSSD
    ready=false
    while [ "\$ready" = "false" ];
    do
	##################################################################
	# Zero out the locator in the RSSD
	echo "Zeroing locator for database = '\${db}', server = '\${svr_name}',"
	echo "     RSSD server = '\${rssd_srvr}', RSSD name = '\${rssd_name}'."
	tmpstr=\`( echo \${rssd_pwrd}
		   echo "use \${rssd_name}"
	           echo "go"
	           echo "update rs_locater set locater = 0x0"
	           echo "where type = \"E\" and sender = "
	           echo "( select dbid from rs_databases" 
	           echo "  where dsname = '\${svr_name}'"
		   echo "    and dbname = '\${db}' )"
	           echo "go" ) | \${rsql}\`

	##################################################################
	# Check the command execution status
	if [ \${?} -ne 0 ];
	then
	    echo "Failure updating rs_locater in '\${rssd_name}'."
	    echo "Check if server '\${rssd_srvr}' is still up."
	    ask  "Do you want to try again? [Y/n]" ans
	    [ "%\${ans}%" = "%%" -o "\${ans}" = "Y" -o "\${ans}" = "y" ] && \\
		    continue
	    break
	fi

	##################################################################
	# Check for isql error
	err=\`echo "\${tmpstr}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'\`
	if [ "%\${err}%" != "%%" ];
	then
	    echo "Internal error updating rs_locater, \${rssd_name}"
	    exit 1
	fi

	##################################################################
	# Extract the rowcount
	cnt=\`echo "\${tmpstr}" | \\
		    sed -n -e 's/(\([0-9][0-9]*\) rows* affected)/\1/p'\`
	if [ \${cnt} -ne 1 ];
	then
	    echo "Errors occurred zeroing locator for database = '\${db}',"
	    echo "     server = '\${svr_name}', RSSD server = '\${rssd_srvr}',"
	    echo "     RSSD name = '\${rssd_name}'."
	    echo "It will have to be manually restored."
	    break
	fi

	##################################################################
	# Zeroing of locator was successful
	ready=true
    done

    ######################################################################
    # Continue if errors occurred.
    [ "\${ready}" = "false" ] && continue

    ######################################################################
    # Set the truncation point to valid in the primary
    ready=false
    while [ "\${ready}" = "false" ];
    do
	##################################################################
	# Set truncation point in primary
	echo "Resetting LTM Truncation Pt in '\${db}', server = '\${svr_name}'."
	tmpstr=\`( echo \${password}
		   echo "use \${db}"
	           echo "go"
	           echo "dbcc settrunc ( 'ltm', 'valid' )"
	           echo "go" ) | \${isql}\`

	##################################################################
	# Check the command execution status
	if [ \${?} -ne 0 ];
	then
	    echo "Failure setting LTM Truncation Pt. in '\${db}'."
	    echo "Check if server '\${svr_name}' is still up."
	    ask  "Do you want to try again? [Y/n]" ans
	    [ "%\${ans}%" = "%%" -o "\${ans}" = "Y" -o "\${ans}" = "y" ] && \\
		    continue
	    break
	fi

	##################################################################
	# Check command result
	err=\`echo "\${tmpstr}" | sed -n -e '/^Msg [0-9][0-9]*, Level/p'\`
	if [ "%\${err}%" != "%%" ];
	then
	    echo "Internal error resetting LTM Truncation Pt."
	    exit 1
	fi

	##################################################################
	# Extract the LTM truncation Pt status
	cnt=\`echo "\${tmpstr}" | sed -n -e \\
	 '4s/^[^0-9][^0-9]*[0-9][0-9]*[^0-9][^0-9]*\([0-9][0-9]*\).*\$/\1/p'\`

	##################################################################
	# Test 1 bit, it should be on
	cnt=\`expr \${cnt} % 2\`
	if [ \${cnt} -ne 1 ];
	then
	    echo "Errors occurred resetting LTM Truncation Point in '\${db}',"
	    echo "     server = '\${svr_name}'."
	    echo "It will have to be manually restored."
	    break
	fi

	##################################################################
	# Done resetting LTM Truncation Pt.
	ready=true
    done

    ######################################################################
    # Continue if errors occurred.
    [ "\${ready}" = "false" ] && continue

    ######################################################################
    # Everything was successful
    echo "Primary database '\${db}' is now ready."
done
EOF0

##########################################################################
# Tell the user that we are done..
echo "$cmd: All replicated databases in '${svr_name} have been drained."
echo "$cmd: You may now proceed with the upgrade to SQL Server 11.0."
echo "$cmd: After completing the SQL Server 11.0 upgrade, reset the LTM"
echo "$cmd: Truncation Point(s), by running the following script"
echo "$cmd:      % sh ${cmd_file}"
echo "$cmd: To prevent loss of replicated data, do not restart replicated"
echo "$cmd: applications until the LTM Truncation Point(s) have been reset."
exit 0

