Re: who's attached to what database?
Patrick Soehl (pnsoehl@snowcrest.net) wrote:
: tbstat -u tells me who's attached to some database in an instance;
: any ideas on how to tell which database they're attached to?
: OnLine 5.0.5; AIX 3.2.5.
: Thanks in advance,
: ^^^^Pnsoehl^^^^
This is very easy to get this information in OnLine DS v.6 and 7
(onstat -g sql, onstat -g ses or through SMI), but in version 5.xx it is
rather difficult.
I wrote shell/awk script which does it. It works on UNIX SYSTEM V, for
BSD UNIX you can make simple changes (in ps command processing).
There is necessary to make some preparation - prepare a file with
database names and rows.
You have to make list of database names with each database name on
separate line. I use directories local/etc and local/bin under
$INFORMIXDIR for my scripts and other files.
Then you have to run simple script "fill_db_row" (as user informix),
which enters each database, runs another simple script "dbase_row", finds
its row in database tblspace (partnum 100002) and writes the row to file
"db_rows".
This is needed preparation steps, then you can run script "who_in_db".
Script "who_in_db" can be run without arguments - then it scans through
file "db_rows" and for each database writes information to stdout about
users, terminals, locks, sqlturbo PID and for local connection also user's
idle time, application PID and application name.
When it has been run with arguments, it supposes database name(s) and does
its work only for those database(s).
The problem is that you have recreate file "db_rows" each time you add
or drop some database.
Hope this helps. If you have some problems with this script, feel free
to mail me.
Regards,
Mirek
-----------Cut here ----------
#!/bin/sh
# Script fill_db_rows
DB_ROWS=$INFORMIXDIR/local/etc/db_rows # file with database and rows list
DB_NAMES=$INFORMIXDIR/local/etc/db_names # file with database list
>$DB_ROWS
for dbase in `cat $DB_NAMES`
do
term=`basename \\`tty\\`` #if you use pseudoterminal (pty, pts
#or simillar), you may have to change
#this line, depending on your UNIX
export term
echo "$dbase: \\c" >> $DB_ROWS
dbaccess $dbase - << END
! dbase_row
END
done
-----------------------
#!/bin/sh
# Script dbase_row
DB_ROWS=$INFORMIXDIR/local/etc/db_rows
tbstat -k | grep `tbstat -u | grep $term | cut -c1-8`|\\
while read lock_addr wait_lck user_addr next_lck lck_type partnum rowid size
do
if [ $partnum = 1000002 ]
then
echo $rowid >> $DB_ROWS
fi
done
-----------------------
#!/bin/sh
# Script who_in_db
trap "rm -f $TMP exit 1" 1 2 3 15
TMPU=/tmp/u$$
TMPK=/tmp/k$$
TMPP=/tmp/p$$
TMPF=/tmp/f$$
TMP="$TMPU $TMPK $TMPP $TMPF"
DB_ROWS=$INFORMIXDIR/local/etc/db_rows
tbstat -k > $TMPK
tbstat -u > $TMPU
ps -ef > $TMPP
finger -i > $TMPF
users_in_db() {
if grep "^$DBASE:" $DB_ROWS 1>/dev/null 2>&1
then
grep "^$DBASE:" $DB_ROWS | while read dbase row
do
echo "Database $DBASE: \\n"
nawk \\
'BEGIN {OFS = "\\t"
print "User", "Term", "Locks", " Idle"
print "\\t" "SrvPID", "AppPID", "Application""\\n"}
$7 ~ /^'$row'$/ {user_addr = $3
while (getline users < "'$TMPU'") {
split(users,u)
pid = u[3]
terminal = u[5]
user = u[4]
locks = u[8]
if (u[1] == user_addr) {
app = ""
idle = ""
if (terminal != "-") {
while (getline finger < "'$TMPF'") {
count=split(finger,f)
if (f[1] == user && f[2] == terminal) {
for (i=7; i<=count; i++) {
idle = idle f[i] " "}
break}}
close("'$TMPF'")
while (getline ps < "'$TMPP'") {
count=split(ps,p)
if (p[2] == pid) {
ppid = p[3]
break}}
close("'$TMPP'")
while (getline ps < "'$TMPP'") {
count=split(ps,p)
if (p[2] == ppid) {
for (i=8; i<=count; i++) {
app = app p[i] " "}
break}}
close("'$TMPP'")
print user, terminal, locks, idle
print "\\t" pid, ppid, app}
else {print user, terminal, locks
print "\\t" pid, "---", "---"}
break}
}}
END {print "\\n"}' $TMPK
done
else
echo "Database $DBASE not found in database list!"
sleep 1
fi
}
if [ $# -ge 1 ]
then
for DBASE
do
users_in_db
done
else
for DBASE in `cut -d: -f1 $DB_ROWS `
do
users_in_db
done
fi
rm -f $TMP
-----------------------------------
--
=================================
Miroslav Lorenc
Database Administrator
Software Applications and Systems Internet: Miroslav.Lorenc@sasprg.cz
Tiskarska 10/257
108 28 Prague Tel: +42 2 705134
Czech Republic Fax: +42 2 705128