RE: Detailed Status of Locks ?
Sorry Guy's. Today I found a problem with my script. Which could not
cope up with too many users. I have modified the script to overcome this
problem.
here is the modified version of the script.
#!/bin/sh
########################################################################
########
#
# Shell Script: lockrep
#
# Description: Program to find out tables which is locked by users.
#
# Author: Jayakumar George Date: 12/08/97
#
#
########################################################################
########
onstat -u | nawk ' BEGIN { getline;getline;getline;getline;getline;ctr =
1 }
{ if ( $2 == "active,")
{
while(getline)
}
else
{
if ($8 > 1)
{
username[ctr]=$4
sessionid[ctr]=$3
useraddr[ctr]=$1
ctr++
}
}
tmpctr=1
while("onstat -g sql"|getline)
{
if(tmpctr > 5)
databases[$1]=substr($0,22,18)
tmpctr++
}
close("onstat -g sql ")
}
END {
if (ctr == 1)
{ print "No User(s) Has a lock"
exit 1
}
print "The following User(s) has lock(s)"
print "User ID User Name Sess. ID
Database Table(s)"
print "------- --------- --------
-------- -------"
for(forctr=1;forctr< ctr;forctr++)
{
"listusers | grep " username[forctr] | getline
uname
printf "%-36s%8d %-15s",uname
,sessionid[forctr],substr(databases[sessionid[forctr]],1,15)
close("listusers")
selstr = " set isolation to dirty read;select
tabname from systables where partnum in \\("
inclse=""
cmd = "onstat -k | grep " useraddr[forctr]
while(cmd | getline)
{
if($4 != 0)
{
hextoint($6)
tabnum[$4,$6]=res
}
}
close(cmd)
inctr=1
for (tabnumvar in tabnum)
{
if (inctr > 1)
inclse = inclse ",";
inclse = inclse tabnum[tabnumvar]
delete tabnum[tabnumvar]
inctr++
}
inclse = inclse "\\)"
selstr= selstr inclse
cmd1= "echo \\"" selstr "\\" | dbaccess "
databases[sessionid[forctr]] "2>/dev/null | grep -v \\"^$\\" | grep -v
tabname"
ctrcmd2=1
while(cmd1 | getline tabname)
{
if(ctrcmd2 == 1)
printf "%-18s",tabname
else
printf "%60c%-18s",32,tabname
ctrcmd2++
printf "\\n"
}
close(cmd1)
}
}
function hextoint(a)
{
res=0
base=16
expn=0
for(i=length(a);i>0;i--)
{
val1=substr(a,i,1)
if(match(val1,"[Aa]"))
val1 = 10
if(match(val1,"[Bb]"))
val1 = 11
if(match(val1,"[Cc]"))
val1 = 12
if(match(val1,"[Dd]"))
val1 = 13
if(match(val1,"[Ee]"))
val1 = 14
if(match(val1,"[Ff]"))
val1 = 15
res=res + (val1 * (base^expn))
expn++;
}
}
'
} -----Original Message-----
} From: Jayakumar George [SMTP:JayaG@unitedgas.co.uk]
} Sent: Tuesday, August 26, 1997 10:02 AM
} To: 'Bill Ennis'; ebert@mail.ppp.net
} Cc: informix-list@rmy.emory.edu
} Subject: RE: Detailed Status of Locks ?
}
} I have got a script to show all the locks of each database and the
} object locked in the instance. Hope this will be useful. But this uses
} dbaccess to connect to the database and obtain the information about
} the
} objects so it is a little bit slow.
}
} I have tested it in sequent Dynix PTX. It works fine.
}
}
} NOTE: This scripts uses a command called "listusers" (I do not know
} whether it is sequent specific. but if it is not you can get the
} username from /etc/passwd.). I will try to amend this script to work
} globally.
}
} just type lockrep in the command line. Please let me know you have.
}
} here is the script.
}
} #!/bin/sh
} ######################################################################
} ##
} ########
} #
} # Shell Script: lockrep
} #
} # Description: Program to find out tables which is locked by users.
} #
} # Author: Jayakumar George Date: 12/08/97
} #
} #
} ######################################################################
} ##
} ########
}
} onstat -u | nawk ' BEGIN { getline;getline;getline;getline;getline;ctr
} =
} 1 }
} { if ( $2 == "active,")
} {
} while(getline)
} }
} else
} {
} if ($8 > 1)
} {
} username[ctr]=$4
} sessionid[ctr]=$3
} useraddr[ctr]=$1
} ctr++
} }
} }
} tmpctr=1
} while("onstat -g sql"|getline)
} {
} if(tmpctr > 5)
} databases[$1]=substr($0,22,18)
} tmpctr++
} }
} }
} END {
} if (ctr == 1)
} {
} print "No User(s) Has a lock"
} exit 1
} }
} print "The following User(s) has lock(s)"
} print "User ID User Name Sess. ID
} Database Table(s)"
} print "------- --------- --------
} -------- -------"
} for(forctr=1;forctr< ctr;forctr++)
} {
} "listusers | grep " username[forctr] | getline
} printf "%-36s%8d %-15s",$0
} ,sessionid[forctr],substr(databases[sessionid[forctr]],1,15)
} selstr = " select tabname from systables where
} partnum in \\("
} inclse=""
} cmd = "onstat -k | grep " useraddr[forctr]
} while(cmd | getline)
} {
} if($4 != 0)
} {
} hextoint($6)
} tabnum[res]=res
} }
} }
} inctr=1
} for (tabnumvar in tabnum)
} {
} if (inctr > 1)
} inclse = inclse ",";
} inclse = inclse tabnumvar
} delete tabnum[tabnumvar]
} inctr++
} }
} inclse = inclse "\\)"
} selstr= selstr inclse
} cmd= "echo \\"" selstr @@DQ@