From: stiglich@interserv.com Subject: Informix Monitoring program ( tbrpt ) Date: 2 Apr 1996 00:34:18 GMT Newsgroups: comp.databases.informix 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 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 Address |Tran Flags |Tran Addr |Isoltn. | | # # tb.lock_pre |HEX TBLNUM |Lock Addr |Type |Rowid | User Address| # # tb.tblnm |HEX TBLNUM |NROWS |Usage | | | # # DB_PART.? |HEX TBLNUM |Table Name |Database | | | # #-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-# join -o 1.1 1.2 1.3 1.4 1.5 2.2 2.3 2.4 /tmp/tb.users /tmp/tb.trans \ > /tmp/tb.utrans join -o 1.1 1.2 1.3 1.4 1.5 2.2 2.3 /tmp/tb.lock_pre /tmp/DB_PART.$SERVER \ > /tmp/tb.lock2 sort -o /tmp/tb.lock2 /tmp/tb.lock2 join -a1 -o 1.1 1.2 1.3 1.4 1.5 1.6 1.7 2.2 2.3 /tmp/tb.lock2 /tmp/tb.tblnm \ > /tmp/tb.lock join -o 1.1 1.2 1.3 2.2 2.3 /tmp/tb.tblnm /tmp/DB_PART.$SERVER |\ awk ' BEGIN { printf ("\n\t%s\n\n", "OPEN TABLES NOT ASSOCIATED WITH A LOCK"); printf ("%-8s\t%-6s\t%-5s\t%-20s\t%-15s\n\n", "TBLNUM", "NROWS", "USAGE","DATABASE","TABLE") } { printf ("%-8s\t%-6s\t%-5s\t%-20s\t%-15s\n", $1,$2,$3,$5,$4) } END { printf ("\n\n") }' \ > /tmp/tb.alltb_orig cp /tmp/tb.alltb_orig /tmp/tb.alltb # Change the TTY to NETWORK if the user id is not "informix". Used for I-Star/ # I-Net connections for USERADDRESS in `cut -f1 -d' ' /tmp/tb.utrans` do grep "$USERADDRESS" /tmp/tb.utrans | \ awk ' BEGIN { printf ("%-8s %-7s %-5s %-8s %-7s %-5s %8s %-11s\n", "USERID","FLAGS", "PID","ADDRESS","TTY","TFLAG","TADDR","ISOLATION") } { if ($5 == "console" && $4 != "informix" && $4 != "root") TTY="NETWORK" else TTY=$5 printf ("%-8s %7s %5s %8s %7s %5s %8s %-9s\n\n", $4,$2,$3,$1,TTY,$6,$7,$8); } ' grep "$USERADDRESS" /tmp/tb.lock 1>/dev/null 2>&1 if [ $? -eq 0 ] then # Remove any tables from list of all tables that are associated with this # lock TABSASGN=`grep "$USERADDRESS" /tmp/tb.lock | awk '{ printf ("%s ", $1)}'` for TABUSED in `echo $TABSASGN` do grep -v $TABUSED /tmp/tb.alltb > /tmp/tb.$$ mv /tmp/tb.$$ /tmp/tb.alltb done # NOTE: There will only be entries for NROWS & USE if there is an entry # for the table in the tbstat -t output. This indicates that there is # some read/write activity on the table. # The LOCKTYPE is derived from the rowid. grep "$USERADDRESS" /tmp/tb.lock |\ awk ' BEGIN { printf (" %s\n", "LOCKS: ") ; printf (" %-9s %-7s %-12s %-17s %-6s %-3s %-9s\n\n", "ADDRESS", "TYPE","DBASE","TABLE","NROWS","USE","LOCK SCOPE") } { printf (" %-9s %-7s %-12s %-17s %-6s %-3s", $2,$3,$7,$6,$8,$9) ; len = length($4) ; len1 = len - 1 ; if ( $4 == "0" ) LOCKTYPE="TABLE LOCK"; if ( len > 6 ) LOCKTYPE="INDEX KEY LOCK"; if ( substr($4,len1) == "00") LOCKTYPE="PAGE LOCK"; if ( len <= 6 && substr($4,len) != "0") LOCKTYPE="ROW LOCK"; printf (" %s\n", LOCKTYPE); } END { printf ("\n\n") } ' fi done 1>>/tmp/tb.rpt # If there are more than 2 lines include the all table report ( tbstat -t ) if [ `sed -n '/[A-Za-z]/p' /tmp/tb.alltb | wc -l | awk ' { print $1 } '` -gt 2 ] then cat /tmp/tb.alltb >>/tmp/tb.rpt fi LATCH=`tbstat -s $TBSTAT | tee /tmp/tb.latch | wc -l | awk ' { print $1 }'` if [ $LATCH -gt 6 ] then cat /tmp/tb.latch | sed -n '1,3p' >>/tmp/tb.rpt fi # Pipe report to pg if there are more than 22 lines. RPTLEN=`wc -l /tmp/tb.rpt | awk ' { print $1 } '` if [ $RPTLEN -gt 22 ] then pg -20 /tmp/tb.rpt else cat /tmp/tb.rpt fi rm /tmp/tb.* if [ "$SAVETBOUT" ] then mv tbstat.out tbstat.$$ echo "tbstat.out has been moved to tbstat.$$" else rm tbstat.out 2>/dev/null fi