#!/usr/bin/ksh
###############################################################################
#
#       Module:         syslocks.sh
#       Author:         Peter R. Schmidt
#       Description:    See who is waiting on and holding locks
#
#       Change Log
#
#         Date    Name               Description.................
#       07/15/99  Peter R. Schmidt   Start Program 
#
###############################################################################

OUTPUT=syslocks.out
TMPFILE=syslocks.tmp

XDATE=`date +%D-%T`
MACHINE=`uname -n`

clear				# clear screen

while true
do
        echo "Report on locks for an Informix online engine database"
        echo
        echo "1 = dbsname/tablename"
        echo "2 = owner name"
        echo "3 = waiter name"
        echo "0 = Exit this program"
        echo
        echo "Enter sort sequence desired (0,1,2,3)"
        read OPTION

        case $OPTION in 0|1|2|3) break;; esac
        echo
        echo "Error - you must enter 0,1,2 or 3!"
        echo
done

case $OPTION in
        0)      echo "End requested by user"; exit;;
        1)      SORT="1,2,7,3";   ORDER=asc;  ORDERBY=Tablename;;
        2)      SORT="7,1,2,3";   ORDER=asc;  ORDERBY=Owner;;
        3)      SORT="9,1,2,7,3"; ORDER=asc;  ORDERBY=Waiter;;
esac

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

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

echo
echo "Collecting lock info from the sysmaster database..."
        
dbaccess <<-EOF
database sysmaster;
unload to '$TMPFILE' delimiter "|"
select  
	dbsname,        
	b.tabname,        
	rowidr,        
	keynum,
        e.txt           type,        
	d.sid           owner,
        g.username      ownername,        
	f.sid           waiter,
        h.username      waitname
from    
	syslcktab       a,        
	systabnames     b,
        systxptab       c,        
	sysrstcb        d,        
	sysscblst       g,
        flags_text      e,        
	outer ( sysrstcb f , sysscblst h  )
where           
		b.dbsname <> "sysmaster"
	and     a.partnum = b.partnum
	and     a.owner   = c.address
	and     c.owner   = d.address
	and     a.wtlist  = f.address
	and     d.sid     = g.sid
	and     e.tabname = 'syslcktab'
	and     e.flags   = a.type
	and     f.sid     = h.sid
order by $SORT $ORDER;
EOF

awk ' \

BEGIN {
	FS="|"
	lock_cnt=0
}

{
	if (NR == 1) {
       		split (xdate,b,"-")
       		udate=b[1]
       		utime=b[2]
	
       		printf "\n%s %s           Informix Locks Report         for %s@%s\n\n", udate, utime, server, machine
		print "Database:Table           Owner      Row Id Type Type Desc              Waiter\n"
	}

	dbsname		= $1
	tabname		= $2
	rowidr		= $3
	keynum		= $4
        type		= $5
	owner		= $6
        ownername	= $7
	waiter		= $8
        waitname	= $9

	dbs_tab		= dbsname ":" tabname

	lock_cnt++

        if (type == "B")   type_desc = "byte lock"
	if (type == "IS")  type_desc = "intent shared lock"
        if (type == "S")   type_desc = "shared lock"
	if (type == "XS")  type_desc = "repeatable read shared key"
        if (type == "U")   type_desc = "update lock"
	if (type == "IX")  type_desc = "intent exclusive lock"
        if (type == "SIX") type_desc = "shared intent exclusive"
	if (type == "X")   type_desc = "exclusive lock"
        if (type == "XR")  type_desc = "repeatable read exclusive"

	printf "%-25s %-8s %7d %-3s  %-22s %-s\n", dbs_tab, ownername, rowidr, type, type_desc, waitname

}

END {
	printf "\nTotal of %d locks	  Sorted by %s\n", lock_cnt, orderby

}

' xdate=$XDATE machine=$MACHINE server=$INFORMIXSERVER orderby=$ORDERBY $TMPFILE > $OUTPUT

pg $OUTPUT

rm -f $TMPFILE

echo
echo "Note: Output report is in $OUTPUT"