#!/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