Re: Better locks info

Thanks for everyone's input.... actually, I was using something called onlocks, but if you're (Rudy's) who-lock.sh and who-access.sh use syslocks, then it will suffer from the same problem...... What I want is a fast script which tells me everything (I know, I always want too much), but we had something pretty close to this where I used to work..... (going from over a year ago's memory, so it might not be perfect) it basically did an onstat -k, ignored anything for partnum 10002 (must have been something like temp tables that didn't affect others? I guess - never saw any docs on that), but then figured out which table was being locked, who was locking it and attempted to show the process which was locking it.....I modified it to avoid the assumption that a user only was running 1 session at a time, but my version suffered from slow-performance - it was not uncommon for the lock and process to be gone by the time it looked for it - oh well, problem solved I guess, but I still want to know what program would lock anything for a noticable amount of time so I can fix it.) anyway, it's not such a big problem that I have a whole lot of time to devote to it, but I have been gradually writing a script to do this..... I'll post it when I'm done, unless someone else beats me to it..... Jacob Salomon wrote: > Danny Wright <dw420@airmail.net> wrote: > > >>Does someone have a better lock monitoring tool besides the one at > >>iiug.org? (onstat -k interpreter?) > >> > >>There was a script there, but frankly, at least in the environment I > >>currently find myself in, it is useless.....It reads syslocks, which > >>I've never happened to have done before, but it does not come back in > >>a reasonable amount of time (over 5 minutes.....by that time, I could > >>have isolated the lock that I was having problems with manually!) > >> > >>I know better tools exist as I have used them before (a place I used > >>to work had a decent shell script for this, which I actually enhanced, > >>but of course that is the intellectual property of that former > >>employer, so I don't have it now - though I could probably recreate it > >>if noone has anything better). > >> > >>Thanks, > >>Danny > > In article <38F36C1C.3CFEAEC7@americasm01.nt.com>, > Rudy Fernandes <rferdy@americasm01.nt.com> replied:> > >You could try the following if "who's locked out by whom?" is what you > >are looking for? The script was designed specifically for sites that > >could have a large number of locks active at any given time. > > > >#!/bin/ksh > ># rudy Thu Mar 4 11:28:22 EST 1999 > ># determines locking interactions - i.e which session is locking out > >another > > > >TMP_FILE=$0.tmp.$$ > >GET_ONSTATK=1 > >FOUND=0 > >ABNORMAL="Abnormal termination. Cleaning up and exiting..." > >trap "echo $ABNORMAL;rm -f $TMP_FILE ; exit 1" 1 2 13 15 > > > >for SESS in `onstat -u | grep "L-" | cut -b 18-26` > >do > > FOUND=1 > > echo "Session $SESS locked by session ... \\c" > > for LOCK in `onstat -u | grep " $SESS " | cut -b 45-52` > > do > > if [ $GET_ONSTATK = 1 ] > > then > > onstat -k | cut -b 1-26 >$TMP_FILE > > GET_ONSTATK=0 > > fi > > LOCKER=`grep "^${LOCK} " $TMP_FILE | awk '{ print $3 }'` > > LOCKER_SESS=`onstat -u | grep "${LOCKER} " | cut -b 18-26` > > echo $LOCKER_SESS > > done> >done > >if [ $FOUND = 0 ] > >then > > echo 'No process waiting on locks' > >fi > >rm -f $TMP_FILE > > > >############ End ############ > > Jake picks up here: > > Danny, > > I presume (at the risk of being presumptuous) that you are referring to > my utilities, who-lock.sh and who-access.sh, which I submitted a long > time ago. I agree, in a busy system with many locks, it does take too > long to get back values. However, its purpose was to determine who the > ^%$#! is stopping me from getting to the specific tables/database I want > to access. Rudy's script answers a slightly different question: Who the > ^%$#! is getting in my way, in general. > > I like Rudy's script - I have already scarfed it and started to modify > some of its code to the style I like. But I do have a proposal for Rudy: > > The wait column in "onstat -u" is not always a lock; it may be a latch > (like waiting for the chance to muck-up a buffer or a checkpoint), which > would not show up in onstat -k but would show up in onstat -s. I also > don't know what other kind of events would show up (as a hex address) in > the wait column of onstat -u. Yeah, I know a long-lived latch is > unlikely but I also know most DBA's have had the pleasure of 10-minute > checkpoints so a latch-aware script would be very helpful here. > > Rudy, wanna fill in some gaps? > -- > +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ > |------------------- Bulletin Board Announcement ----------------------| > | Congregants will please note that the bowl at the back of the church | > | bearing the sign "For the Sick" is for monetary contributions only. | > +----------------------------------------------------------------------+ > > Sent via Deja.com http://www.deja.com/ > Before you buy.