Subject: Informix Monitoring program ( tbrpt )
From: [email-masked]
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
[email-masked]

#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 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