How to find which user is holding a database table? -Reply
Hi Sujit
Here is a script which will find locks. It is self
explanatory and uses sysmaster.syslocks. I
call it 'whoselocks'. Copyright for personal use
is hereby waived,
HTH
Peter Tashkoff <tashkop@zespri.co.nz>
Project Consultant/DBA
Zespri International Limited.
This posting may not be used by any party for
the vilification of any other.
----------------------------------------------------
#!/bin/ksh
# PBT Fri Aug 9 11:31:59 NZST 1996
# Identifys locks for a table across all sessions
###################################################################
# Mod Who When Why
###################################################################
# 1 PBT Tue Oct 15 11:36:49 NZDT 1996 Add
ability to get Table only
:
set -h
####FUNCTIONS
######################################################
usage_exit()
{
echo
echo
---------------------------------------------------------
echo "whoselocks USAGE:"
echo "This script requires 1 or 2 arguments"
echo "and can be run in three ways"
echo "Arguments are positional arguments
where:"
echo " arg 1 = session id (required)"
echo " arg 2 = Table name (optional)"
echo "If run with a -u switch the arguments
are "
echo "interpreted as;"
echo " arg 1 = user id (required)"
echo " arg 2 = Table name (optional)"
echo "If run with a -t switch the arguments
are "
echo "interpreted as;"
echo " arg 1 = Table name (required)"
echo
echo "The switch can appear in any place but
the "
echo "other arguments must appear in
precedence"
echo
---------------------------------------------------------
echo "Press enter/return to continue"
read UserResponse
exit 2
}
table_given() #Handles Positional Arguments
{
echo "
select *
from syslocks where owner in (
$SESSIONID )
and tabname = '$TABLENAME'
" | dbaccess sysmaster}
just_session() #Handles Positional Arguments
{
echo "
select * from syslocks where owner in (
$SESSIONID )
" | dbaccess sysmaster}
just_table()
{
echo "
select *
from syslocks where
tabname = '$TABLENAME'
" | dbaccess sysmaster}
user_args()
{
if ( [ $1 = "-u" ] )
then
USERID=$2
if ( [ $# -eq 3 ] )
then
if ( [ $# -eq 3 ] )
then
TABLENAME=$3
fi
fi
fi
if ( [ $2 = "-u" ] )
then
USERID=$1
if ( [ $# -eq 3 ] )
then
TABLENAME=$3
fi
fi
if ( [ $# -eq 3 ] )
then
if ( [ $3 = "-u" ] )
then
USERID=$1
TABLENAME=$2
fi
fi
if ( [ $2 = "-t" ] )
then
TABLENAME=$1
JUSTTABLE=true
fi
get_user_sessions
}
positional_args()
{
SESSIONID=$1
TABLENAME=$2
}
#Find out all the session for a user
get_user_sessions()
{
if ( [ $USERID ] )
then
SESSIONID=`onstat -g ses | grep $USERID
| awk '{ print $1 "," }'`
SESSIONID=$SESSIONID"-1"
fi
}
#################### Execution line starts
from here #################
# check Arguments were received
if ( [ $# -eq 0 ] )
then
usage_exit
fi
#Using session id or user login
ARG=`echo $* | grep "-"`
STATUS=$?
#Runner functions
if ([ $STATUS -eq 0 ] ) then
user_args $*
else
positional_args $*
fi
if ( [ $TABLENAME ] )
then
if ( [ $JUSTTABLE ] )
then
just_table $*
else
table_given $*
fi
else
just_session $*
fi
------------------------------------------------------
>>> Sujit Pal <spal@watson.den.csci.csc.com>
3/April/1997 10:48am >>>
Hi All
I have to do schema changes to a database
which has lots of people =
(developers). Most of the time there is no
problem but sometimes I get = the message:
215: Cannot open file for table (tablename)
106: ISAM error: non-exclusive access
from which I understand that somebody is using
the table and hence I = cannot alter its structure
or drop an index from it.
I have written a script which essentially does an
onstat -g ses and = using the session id's
generated, does an onstat -g sql <sessid>. I =first grep the output for the database that I want.
If it is the = database that I want then I grep the
output for the table name, hoping I = will get the
tablename in the "Current SQL statement" and
the "Last =
Parsed SQL statement".
However my script does not find the table which
is giving me these = errors as being contained
in any of the onstat -g sql <sessid> outputs.
I have verified that my script works by feeding it
table names that I = can see manually by doing
an onstat -g sql <sessid>. What am I doing =
wrong? Is there any other way of doing this?
TIA
Sujit Pal