Know What a Certain User is Doing

Database administrators spend a lot of time monitoring the database, looking for things that could cause problems. After a while, it is usually possible to identify several of your peers who need to be looked in upon occasionally. After about a thousand repetitions of "DBA, I've got a job I want to kill, would you do it?" I developed the following scripts:

The first is called seeuser and it tells you what a certain user is doing in the database. Since I get tired of deciphering the flag codes in the tbstat output, I also put a legend at the bottom of the output telling me what each code means:

JOE 71> cat seeuser
#!/bin/csh
if ( $#argv == 0 ) then
echo -n "Enter the USER NAME you want to check on ( . for all) : "
set target = $<
else
set target = $1
endif

echo Looking at INFORMIX jobs for $target
echo "
Users 
address  flags   pid     user     tty      wait     tout locks nreads nwrites"
echo " "
$INFORMIXDIR/bin/tbstat -u | grep $target | grep -v grep
echo "
         ^ ^ ^ ^ 
         | | | | 
POSITION  1 2 3 4 
1 WAITING ON: B(uffer) C(heckpoint) L(ock) S(latch) X(rollback) G(log)
2 TRANSACTIONS: B(egin work)  T(1n a transaction)   R(ollback)  A(rchive)
3 STATUS:    R(eading)    X(inside a write, checkpoints frozen)
4 PROCESS:   M(Monitor)   D(aemon)  C(Dead, awaiting cleanup) F(PageFlusher)"

You can invoke seeuser with or without parameters. If you use no parameters, the script will prompt you to enter the name of a user to check on, or to enter a period to check on all users. You can also invoke the program with a user name as a parameter. In either case, user could be anything that shows up in a tbstat -u output. For example, you could type a seeuser L and it would show you any users who were waiting for a lock to release (It has an L in the first field of the flag field). Of course, it would also show you anything that user Lincoln or firewaLL was doing, too, but you wouldn't use capital letters in a user name, would you?

There's another variant of the seeuser script called checkon. This gives you all that seeuser does but also includes any UNIX processes owned by the user. Here is the checkon script:

INFORMIX 83> cat checkon
#!/bin/csh
echo LISTING ALL jobs for $1
echo " "
ps -fu $1
echo " "
echo Looking at INFORMIX jobs for $1
echo " "
echo "
address  flags   pid     user     tty           wait        tout locks nreads nwrites"
$INFORMIXDIR/bin/tbstat -u | grep $1 | grep -v grep
echo "
         ^ ^ ^ ^ 
         | | | | 
POSITION  1 2 3 4 
1 WAITING ON: B(uffer) C(heckpoint) L(ock) S(latch) X(rollback) G(log)
2 TRANSACTIONS: B(egin work)  T(in a transaction)   R(ollback)  A(rchive)
3 STATUS:    R(eading)    X(inside a write, checkpoints frozen)
4 PROCESS:   M(Monitor)   D(aemon)    C(Dead,awaiting cleanup) F(PageFlusher)"

This script is a little more particular with input than the seeuser script is. This is because of the UNIX ps command in the third line. Depending on your UNIX flavor, you may need to modify this line anyway because different versions of UNIX have some differences in the ps command. Here, ps is looking for a complete user name. If your user name does not match, the script will fail on the ps command but will give you the tbstat out put anyway. You could change the command to a simple ps command piped into grep something like this.

ps -aux | grep $1

instead of the ps -fu $1. This would allow you to use a partial username.