row_locking.sh.txt

管理online的shell程序

#!/usr/bin/ksh
###############################################################################
#
#       Module:         page_locking.sh
#       Author:         Peter R. Schmidt
#       Description:    Change all tables to row level locking
#
#	Argument 1	Database Name
#
#       Change Log
#
#         Date    Name               Description.................
#       07/14/99  Peter R. Schmidt   Start Program 
#
###############################################################################

OUTPUT=row_locking.out
TMPFILE=row_locking.tmp
FLAG_FAILED=false
FLAG_RETRY=false
MAX_RETRIES=50

if [ $# != 0 ]
then
	DATABASE=$1
else
	echo "Usage: row_locking.sh [database name] [Retry #]"
	exit 1
fi

if [ $# = 2 ]
then
	FLAG_RETRY=true
	NUM_RETRY=$2
else
	FLAG_RETRY=false
	NUM_RETRY=0
fi

if [ -f $OUTPUT -a $FLAG_RETRY = false ]
then
        rm -f $OUTPUT
fi

if [ -f $TMPFILE ]
then
        rm -f $TMPFILE
fi

UDATE=`date`
echo "----------------------------------------------------------------------" | tee -a $OUTPUT
echo "Convert Informix tables to ROW level locking - $UDATE" | tee -a $OUTPUT
echo "----------------------------------------------------------------------" | tee -a $OUTPUT

if [ $FLAG_RETRY = true ]
then
	UDATE=`date` | tee -a $OUTPUT
	echo "RETRY $NUM_RETRY on $UDATE" | tee -a $OUTPUT
	echo "----------------------------------------------------------------------" | tee -a $OUTPUT
fi

echo "Collecting table info from the systables table..." | tee -a $OUTPUT
        
dbaccess <<-EOF1 2>&1 | tee -a $OUTPUT
	database $DATABASE;
	unload to '$TMPFILE' delimiter "|"
	select tabname 
	from systables 
	where locklevel <> 'R' and tabid > 99 and tabtype = 'T'
	order by 1;
EOF1

echo "unload completed" | tee -a $OUTPUT

echo "----------------------------------------------------------------------" | tee -a $OUTPUT

for TABLE in `awk '{ print $1 }' FS='|' $TMPFILE`
do

	UDATE=`date +%D-%T`
	echo | tee -a $OUTPUT
	echo "Attempting to alter table $TABLE on $UDATE" | tee -a $OUTPUT
	echo | tee -a $OUTPUT
	
	dbaccess >> $OUTPUT 2>&1 <<-EOF2
		database $DATABASE;
		alter table $TABLE lock mode (ROW);
	EOF2
	
	if [ $? != 0 ]
	then
			echo "Note: alter on table $TABLE FAILED" | tee -a $OUTPUT
		FLAG_FAILED=true
	else
			echo "Note: alter on table $TABLE SUCCESSFUL" | tee -a $OUTPUT
	fi

	echo | tee -a $OUTPUT
	echo "----------------------------------------------------------------------" | tee -a $OUTPUT
	done

rm -f $TMPFILE

echo
echo "Program completed - results in $OUTPUT"

if [ $FLAG_FAILED = true ]
then
	UDATE=`date +%D-%T`
	echo "Note: some tables did not get converted to row locking" | tee -a $OUTPUT
	if [ $NUM_RETRY -ge $MAX_RETRIES ]
	then
		echo "Exceeded maximum number of retries of $MAX_RETRIES" | tee -a $OUTPUT
		echo "No more attempts will be made" | tee -a $OUTPUT
	else
		echo "Sleep 10 seconds on $UDATE..." | tee -a $OUTPUT
		sleep 10
		NUM_RETRY=`expr $NUM_RETRY + 1`
		exec $0 $DATABASE $NUM_RETRY
	fi
fi