Informix Monitoring program ( tbrpt )
TBRPT. Here is a program I developed to track Online usage. Especially
useful for trying to resolve lock problems. It combines many of the
tbstat report options, and uses the hex partnumber from the systables table
to link with "tblnum" output from tbstat -k & tbstat -t. Hope it is helpful.
No guarantees are implied! May need tweeking to work on your system. I used
Solaris 2.4, running Informix 5.05.UC2. If you are working on version 6 or
above substitute tbstat with onstat. I think the onstat options are the same
as tbstat.
Consists of two files. tbrpt & hex_part.sql. Sorry, it's not shar'd.
The only edit needed is set the DATABASE variable in tbrpt. Enter every
database on your system seperated by a space.
Any comments or suggestions will be gratefully accepted. I will try to answer
any questions.
Pete Stiglich
CTG
stiglich@interserv.com
#include <disclaimer.h>
Sample output. The LOCK's display may be very long depending on activity.
If it looks wierd its because of my newsreader.
----------------------------------------------------------------------------
RSAM Version 5.05.UC2 -- On-Line -- Up 3 days 04:11:42 -- 848 Kbytes
__________________________________________________________
<< SERVERNUM 0 >> ============= << DBSERVERNAME ONLINE >>
__________________________________________________________
USERID FLAGS PID ADDRESS TTY TFLAG TADDR ISOLATION
informix --B-R-- 9544 10001da4 pts/3 A-B-- 89360 COMMIT
LOCKS:
ADDRESS TYPE DBASE TABLE NROWS USE LOCK SCOPE
1000b94c HDR+X db1 db1_customer 3888 1 TABLE LOCK
1000b970 HDR+S db1 db1_phone TABLE LOCK
OPEN TABLES NOT ASSOCIATED WITH A LOCK
TBLNUM NROWS USAGE DATABASE TABLE
1000060 45 1 db1 systables
100007d 3882 1 db1 db1_address
# Note: Latches will also be displayed if there are any active ( tbstat -s ).
-----hex_part.sql-------------------%<------------------------cut here-------
select hex(partnum),tabname, dbservername from systables;
-----tbrpt-------------------------%<-------------------------cut here-------
#----------------------------------------------------------------------------
# tbrpt -- Monitor Online activity. Users, transactions, locks & tables.
# Will show which's databases & tables are open or associated with
# a user/lock
#
# Used Informix 5.05.UC2 & a Sun Spark 20 / Solaris 2.4 for
# development.
#
# Author: Pete Stiglich 2/22/96
#----------------------------------------------------------------------------
# Enter the name of every database regardless of server instance
# e.g. DATABASES="db1 db2 db3"
DATABASES=""
while getopts aurst: OPTS 2>/dev/null
do
case $OPTS in
r) REFRESH="Y";; # Refresh whenever a database changes
t) if [ ! -s "$OPTARG" ]
then
echo "ERROR: $OPTARG does not exist"
exit 1
else
USEFILE=$OPTARG
fi;;
s) SAVETBOUT="Y";;
a) ALLUSERS="Y";;
?|u) echo "USAGE: \\"tbrpt -a -r -s -t filename\\""
echo "\\t\\ta Show all USERS, including daemons"
echo "\\t\\tr Refresh HEX part number file "
echo "\\t\\ts Save tbstat.out that is created for this program "
echo "\\t\\t (saved as tbstat.PID, where PID is the process number)"
echo "\\t\\tt Use \\"filename\\" as input for tbstat "
echo "\\t\\tu Show this message"
exit 1;;
esac
done
if [ ! "$USEFILE" ]
then
tbstat -o tbstat.out
fi
TBSTAT=${USEFILE=tbstat.out}
# Build or refresh the part number table. This takes a little while
#--------------------------------------------------------------------------
# If there are no partnum records for the current server, rebuild the file
#--------------------------------------------------------------------------
SERVER=`tbstat -c $TBSTAT | grep SERVERNUM | awk ' { print $2 } '`
if [ ! -s /tmp/DB_PART.$SERVER -o "$REFRESH" = "Y" ]
then
if [ "$REFRESH" = "Y" ]
then
cat /dev/null > /tmp/DB_PART.$SERVER
fi
for i in `echo $DATABASES`
do
isql $i -qr hex_part 2>/dev/null | sed '1,4d' | sed 's/^0x0//g' |\\
grep -v "VERSION" | tr '[A-Z]' '[a-z]' |\\
awk ' { printf ("%s\\t%s\\t%s\\t%s\\n", $1,$2,DB,$3) } ' DB=$i \\
>>/tmp/DB_PART.$SERVER
done
fi
# Get users
tbstat -u $TBSTAT | awk '
/Users/ { users=3 }
{
if (users > 1) { users-- }
if (users == 1)
{ FLAG=substr($2,7,1)
if (FLAG == "-" || ALL== "Y")
{
printf ("%-8s\\t%-7s\\t%5s\\t%-8s\\t%-8s\\n", $1,$2,$3,$4,$5 )
}
}
} ' ALL="$ALLUSERS" >/tmp/tb.users
# Get the DBSERVERNAME ( instance )
tbstat -c $TBSTAT | awk '/SERVERNUM/{ printf
("\\t__________________________________________________________\\n\\n")
printf ("\\t<< %s %s >> ============= ", $1,$2)
getline
printf ("<< %s %s >>\\n", $1,$2)
printf ("\\t__________________________________________________________\\n\\n")
} ' >/tmp/tb.rpt
# Get transactions
tbstat -u $TBSTAT | awk '
/Transactions/ { tflag=3 }
{
if (tflag > 1) { tflag-- }
if (tflag == 1)
{ if ( NF == 7) printf ("%-8s\\t%-5s\\t%-8s\\t%-9s\\n", $3,$2,$1,$6 )
else printf ("%-8s\\t%-5s\\t%-8s\\t%-9s\\n", $3,$2,$1,$7 )
}
} ' >/tmp/tb.trans
# Get locks
tbstat -k $TBSTAT | awk '
/Locks/ { locks=3 }
{
if (locks > 1) { locks-- }
if (locks == 1)
printf ("%-7s\\t%-8s\\t%-9s\\t%-8s\\t%-8s\\n", $6,$1,$5,$7,$3 )
} ' > /tmp/tb.lock_pre
# Get active tablespace's
tbstat -t $TBSTAT | sed '1,5d' |\\
awk ' { printf ("%s\\t%s\\t%s\\n", $5,$10,$4) } ' >/tmp/tb.tblnm
sort -o /tmp/tb.users /tmp/tb.users
sort -o /tmp/tb.trans /tmp/tb.trans
sort -o /tmp/DB_PART.$SERVER /tmp/DB_PART.$SERVER
sort -o /tmp/tb.lock_pre /tmp/tb.lock_pre
sort -o /tmp/tb.tblnm /tmp/tb.tblnm
#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-#
# +------------------------ FIELDS -----------------------------+ #
# FILE | -- 1 -- | -- 2 -- | -- 3 -- | -- 4 -- | -- 5 -- | #
#=============+=============+============+==========+=========+=============| #
# | | | | | | #
# tb.users |User Address |Flags |PID |User | TTY | #
# tb.trans |User