RE: determining who has locks on tables
The script looks complete to me.
I have a few worries about the script itself.
The lines:
> { if ( $2 == "active,")
> {
> while(getline)
> }
...
> tmpctr=1
worry me.
Should this be:
$2 == "active" { next } # consume current line - read next line
# see my comments on getline below
$8 > 1 {
username[ctr]=$4
sessionid[ctr]=$3
useraddr[ctr]=$1
ctr++
}
{
tmpctr = 1
# rest of script ...
In normal circumstances, while(getline) will consume the rest of the input.
In other words, after reading the first "active" no more of the input will
be used until the last line is read.
If this is desired, change to:
while(getline > 0)
Note: getline will return -1 on an error, which would cause an eternal loop!
I have doubts about
> while("onstat -g sql"|getline)
and it's associated close
> close("onstat -g sql ")
Consider
onstat = "onstat -g sql"
while( getline < onstat > 0 )
...
close(onstat)
There's a while(cmd|getline) ... under END which needs similar treatment.
-----Original Message-----
From: hanna_shaw@my-deja.com [mailto:hanna_shaw@my-deja.com]
Sent: 12 December 2000 16:30
To: informix-list@iiug.org
Subject: Re: determining who has locks on tables
Hi Gary,
I'm interested in the script. But it looks like not finished. Will you
be able to paste the left part of it? Thanks.
In article <5TY2OpcTVWiOP3TZqEi6nc6q1eHq@4ax.com>,
Gary Quiring <garyq@emcosales.com> wrote:
> On Thu, 30 Nov 2000 15:21:50 +0200, Brett Geer <brett@brabys.co.za>
wrote:
>
> >
> >Meet my problem, I know onstat -k shows who has locks on records,
> >but sometimes I don't see the locks shown (7.31.UC4 on AIX),
> >does anyone have scripts that show who has locks on what and
> >who is waiting for a lock on a record?
> >
> >brett
> Here's a script I found and modified from the Informix users group:
>
> Gary Q
>
> #!/bin/sh
>
########################################################################
> ########
> #
> # Shell Script: lockrep
> #
> # Description: Program to find out tables which is locked by users.
> #
> # Author: Jayakumar George Date: 12/08/97
> #
> # Ver 1.1 Modified the script(31/10/97) to access the database
once
> for
> # each database since the script is very slow and also
lists
> # user created temp tables if it is locked.
> # Ver 1.2 Modified output for just released locks to fix output
bug
> # 08/14/2000 Gary Quiring
>
########################################################################
> ########
>
> 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 TTY Host/IP Sess.
ID
> Database Table(s) "
> print "------- --- ------------- -------
-
> -------- ----------------"
> for(forctr=1;forctr< ctr;forctr++)
> {
> cmd="onstat -g ses " sessionid[forctr]
> while(cmd | getline)
> {
> if($1 == "partnum")
> {
> while(cmd | getline)
> {
> tmptabname[$1]=$2
> }
> break
> }
> }
> close(cmd)
> cmd=""
> "listusers|grep " username[forctr] | getline
uname
> printf "%-36s%8d %-15s",uname
> ,sessionid[forctr],substr(databases[sessionid[forctr]],1,15)
> close("listusers")
> cmd = "onstat -k | grep " useraddr[forctr]
> dbtabflag = 0
> printnull = 0
> while(cmd | getline)
> {
> if($4 != 0)
> {
> if(tmptabname[$6])
> tmptabs_locked[$6]=tmptabname[$6]
> else
> {
> dbtabflag++
> hextoint($6)
>
> select_tablename(databases[sessionid[forctr]])
>
> tabname_arr[res]=dbtab_arr[databases[sessionid[forctr]],res]
> }
> }
> }
> close(cmd)
> ctrcmd2=1
> if(dbtabflag)
> {
> for (tabname in tabname_arr)
> {
> if(ctrcmd2 == 1)
> printf "%-18s",tabname_arr[tabname]
> else
> printf "%60c%-18s",32,tabname_arr
[tabname]
> ctrcmd2++
> printf "\\n"
> printnull = 1
> delete tabname_arr[tabname]
> }
> }
> for(tmptabvar in tmptabs_locked)
> {
> if(ctrcmd2 == 1)
> printf "(T) %-18s",tmptabs_locked
[tmptabvar]
> else
> printf "%60c(T)
> %-18s",32,tmptabs_locked[tmptabvar]@@N