This script can be used to monitor the Ingres error log (errlog.log) for certain errors.

If the error code is found it will email an alert.

In the example below it is monitoring for timeout and deadlock errors. If it finds any errors it also attempts to extract information from the imadb about the sessions causing the timeout/deadlock and will email the details.

The script loops until a stop file is placed in /tmp

You can modify the script to monitor any sort of error that is written to the Ingres error log.

#!/bin/ksh
# Set environment
#
II_SYSTEM=/ingres
LD_LIBRARY_PATH=/usr/lib:$II_SYSTEM/ingres/lib
PATH=$II_SYSTEM/ingres/bin:$II_SYSTEM/ingres/utility:$PATH
export II_SYSTEM LD_LIBRARY_PATH PATH
ERRLOG=$II_SYSTEM/ingres/files/errlog.log
IMALOG=/tmp/check_timeouts_sql.log
MAILFILE=/tmp/check_timeouts_mail.txt
TODAY_DATE=`date +’%a %b %e’`
TIMEOUT=’DM9043′
PGDEADLOCK=’DM9042′
TBLDEADLOCK=’DM9044′
EXTDEADLOCK=’DM9066′
MAIL_ACC=’you@email.com’
HOST=`hostname`
#
# Initialise counters
#
let TIMEOUT_COUNT=0
let PGDEADLOCK_COUNT=0
let TBLDEADLOCK_COUNT=0
let EXTDEADLOCK_COUNT=0
#

while [ ! -f /tmp/stop_check_timeouts ]
do

CHECK_TIMEOUT=`grep “$TODAY_DATE” $ERRLOG | grep $TIMEOUT | wc -l`
CHECK_PGDEADLOCK=`grep “$TODAY_DATE” $ERRLOG | grep $PGDEADLOCK | wc -l`
CHECK_TBLDEADLOCK=`grep “$TODAY_DATE” $ERRLOG | grep $TBLDEADLOCK | wc -l`
CHECK_EXTDEADLOCK=`grep “$TODAY_DATE” $ERRLOG | grep $EXTDEADLOCK | wc -l`

if (( $CHECK_TIMEOUT > $TIMEOUT_COUNT ))
then
let TIMEOUT_COUNT=CHECK_TIMEOUT
TIMEOUT_ERROR_LINE=`grep $TIMEOUT $ERRLOG | tail -1`
TIMEOUT_BLOCKEE=`grep $TIMEOUT $ERRLOG | tail -1 | awk ‘{ print $4 }’ | sed -e ‘s/\]//’ | sed -e ‘s/://’ `
TIMEOUT_BLOCKER=`grep $TIMEOUT $ERRLOG | tail -1 | awk ‘{ print $32 }’ | sed -e ‘s/\]//’ | sed -e ‘s/\.//’ `
TIMEOUT_BLOCKER=”0000″$TIMEOUT_BLOCKER

sql -s imadb >| $IMALOG << ! execute procedure ima_set_vnode_domain \g select lowercase(hex(bigint(session_id))) , real_user from ima_server_sessions where lowercase(hex(bigint(session_id))) = '$TIMEOUT_BLOCKEE' or lowercase(hex(bigint(session_id))) = '$TIMEOUT_BLOCKER' \g select lowercase(hex(bigint(session_id))) , real_user , session_query from ima_server_sessions where db_name = 'finance' order by col1 \g ! echo $TIMEOUT_ERROR_LINE >| $MAILFILE
cat $IMALOG >> $MAILFILE
cat $MAILFILE | mailx -s “Timeout found on $HOST” $MAIL_ACC
fi

if (( $CHECK_PGDEADLOCK > $PGDEADLOCK_COUNT ))
then
let PGDEADLOCK_COUNT=CHECK_PGDEADLOCK
PGDEADLOCK_ERROR_LINE=`grep $PGDEADLOCK $ERRLOG | tail -1`
PGDEADLOCK_BLOCKEE=`grep $PGDEADLOCK $ERRLOG | tail -1 | awk ‘{ print $4 }’ | sed -e ‘s/\]//’ | sed -e ‘s/://’ `
PGDEADLOCK_BLOCKER=`grep $PGDEADLOCK $ERRLOG | tail -1 | awk ‘{ print $30 }’ | sed -e ‘s/\]//’ | sed -e ‘s/\.//’ `
PGDEADLOCK_BLOCKER=”0000″$PGDEADLOCK_BLOCKER

sql -s imadb >| $IMALOG << ! execute procedure ima_set_vnode_domain \g select lowercase(hex(bigint(session_id))) , real_user from ima_server_sessions where lowercase(hex(bigint(session_id))) = '$PGDEADLOCK_BLOCKEE' or lowercase(hex(bigint(session_id))) = '$PGDEADLOCK_BLOCKER' \g select lowercase(hex(bigint(session_id))) , real_user , session_query from ima_server_sessions where db_name = 'finance' order by col1 \g ! echo $PGDEADLOCK_ERROR_LINE >| $MAILFILE
cat $IMALOG >> $MAILFILE
cat $MAILFILE | mailx -s “Page Deadlock found on $HOST” $MAIL_ACC
fi

if (( $CHECK_TBLDEADLOCK > $TBLDEADLOCK_COUNT ))
then
let TBLDEADLOCK_COUNT=CHECK_TBLDEADLOCK
TBLDEADLOCK_ERROR_LINE=`grep $TBLDEADLOCK $ERRLOG | tail -1`
TBLDEADLOCK_BLOCKEE=`grep $TBLDEADLOCK $ERRLOG | tail -1 | awk ‘{ print $4 }’ | sed -e ‘s/\]//’ | sed -e ‘s/://’ `

sql -s imadb >| $IMALOG << ! execute procedure ima_set_vnode_domain \g select lowercase(hex(bigint(session_id))) , real_user from ima_server_sessions where lowercase(hex(bigint(session_id))) = '$TBLDEADLOCK_BLOCKEE' \g select lowercase(hex(bigint(session_id))) , real_user , session_query from ima_server_sessions where db_name = 'finance' order by col1 \g ! echo $TBLDEADLOCK_ERROR_LINE >| $MAILFILE
cat $IMALOG >> $MAILFILE
cat $MAILFILE | mailx -s “Table Deadlock found on $HOST” $MAIL_ACC
fi

if (( $CHECK_EXTDEADLOCK > $EXTDEADLOCK_COUNT ))
then
let EXTDEADLOCK_COUNT=CHECK_EXTDEADLOCK
EXTDEADLOCK_ERROR_LINE=`grep $EXTDEADLOCK $ERRLOG | tail -1`
EXTDEADLOCK_BLOCKEE=`grep $EXTDEADLOCK $ERRLOG | tail -1 | awk ‘{ print $4 }’ | sed -e ‘s/\]//’ | sed -e ‘s/://’ `
EXTDEADLOCK_BLOCKER=`grep $EXTDEADLOCK $ERRLOG | tail -1 | awk ‘{ print $32 }’ | sed -e ‘s/\]//’ | sed -e ‘s/\.//’ `
EXTDEADLOCK_BLOCKER=”0000″$EXTDEADLOCK_BLOCKER

sql -s imadb >| $IMALOG << ! execute procedure ima_set_vnode_domain \g select lowercase(hex(bigint(session_id))) , real_user from ima_server_sessions where lowercase(hex(bigint(session_id))) = '$EXTDEADLOCK_BLOCKEE' or lowercase(hex(bigint(session_id))) = '$EXTDEADLOCK_BLOCKER' \g select lowercase(hex(bigint(session_id))) , real_user , session_query from ima_server_sessions where db_name = 'finance' order by col1 \g ! echo $EXTDEADLOCK_ERROR_LINE >| $MAILFILE
cat $IMALOG >> $MAILFILE
cat $MAILFILE | mailx -s “Extend Deadlock found on $HOST” $MAIL_ACC
fi

sleep 10

done
/bin/rm -f /tmp/stop_check_timeouts
exit