From: Jayakumar George <JayaG@unitedgas.co.uk>
Subject: Detailed Status of Locks ?
Date: Tue, 26 Aug 1997 17:01:16 +0100

From: Jayakumar George <JayaG@unitedgas.co.uk>
Subject: New version of lockrep.
Date: Fri, 31 Oct 1997 11:36:14 -0000

#!/bin/sh
########################################################################
########
#
# Shell Script: lockrep
#
# Description:  Program to find out tables which is locked by users.
#
# Author:       Jayakumar George          Date: 12/08/97
#
# Ver 1.1       Modified the script(31/10/97) to access the database once for 
#               each database since the script is very slow and also lists 
#               user created temp tables if it is locked.
########################################################################
########

onstat -u | nawk ' BEGIN { getline;getline;getline;getline;getline;ctr =
1 }
		  { if ( $2 == "active,") 
		    {
		       while(getline)
		    }
		    else
		    {
		       if ($8 > 1)
		       {
		         username[ctr]=$4
		         sessionid[ctr]=$3
			 useraddr[ctr]=$1
		         ctr++ 
		       }
		    }
		    tmpctr=1
		    while("onstat -g sql"|getline)
		    {
		       if(tmpctr > 5)
		          databases[$1]=substr($0,22,18)
		       tmpctr++
		    }
                    close("onstat -g sql ")
		   } 
	    END { 
		   if (ctr == 1)
		   {
		      print "No User(s) Has a lock"
		      exit 1
		   }
		   print "The following User(s) has lock(s)"
		   print  "User ID         User Name           Sess. ID
Database       Table(s)      "
                   print  "
(T) - Temp Table"
		   print  "-------         ---------           --------
--------       ----------------"
		   for(forctr=1;forctr< ctr;forctr++)
		   {
                       cmd="onstat -g ses " sessionid[forctr]
                       while(cmd | getline)      
                       {                                          
                         if($1 == "partnum")                      
                         {                                        
                           while(cmd | getline)  
                           {                                      
                             tmptabname[$1]=$2
                           }                                      
                           break                                  
                         }                                        
                       }                                          
                       close(cmd)                
                       cmd=""
		      "listusers | grep " username[forctr] | getline
uname
		      printf "%-36s%8d %-15s",uname
,sessionid[forctr],substr(databases[sessionid[forctr]],1,15)
                      close("listusers")
		      cmd = "onstat -k | grep " useraddr[forctr]
                      dbtabflag = 0
		      while(cmd | getline)
		      {
			 if($4 != 0)
			 {
                           if(tmptabname[$6])
                             tmptabs_locked[$6]=tmptabname[$6]
                           else
                           {
                              dbtabflag++
			      hextoint($6)

select_tablename(databases[sessionid[forctr]])

tabname_arr[res]=dbtab_arr[databases[sessionid[forctr]],res]
                           }
			 }
		      }
                      close(cmd)
		      ctrcmd2=1
                      if(dbtabflag)
                      {
	                 for (tabname in tabname_arr)
	                 {
			    if(ctrcmd2 == 1)
			       printf "%-18s",tabname_arr[tabname]
			    else
		               printf
"%60c%-18s",32,tabname_arr[tabname]
			    ctrcmd2++
			    printf "\n"
			    delete tabname_arr[tabname]
		         }
                      }
                      for(tmptabvar in tmptabs_locked)
                      {
			 if(ctrcmd2 == 1)
			    printf "(T) %-18s",tmptabs_locked[tmptabvar]
			 else
		            printf  "%60c(T)
%-18s",32,tmptabs_locked[tmptabvar]
			 ctrcmd2++
			 printf "\n"
			 delete tmptabs_locked[tmptabvar]
                      }
		   }
		}
		
                function hextoint(a)
                {
	           res=0
	           base=16
	           expn=0
	           for(i=length(a);i>0;i--)
	           {
                      val1=substr(a,i,1)	    
	              if(match(val1,"[Aa]"))
	                val1 = 10
	              if(match(val1,"[Bb]"))
	                val1 = 11
	              if(match(val1,"[Cc]"))
	                val1 = 12
	              if(match(val1,"[Dd]"))
	                val1 = 13
	              if(match(val1,"[Ee]"))
	                val1 = 14
	              if(match(val1,"[Ff]"))
	                val1 = 15
	              res=res + (val1 * (base^expn))
	              expn++;
	          }
               } 

	      function select_tablename(dbname)
	      {
		  if(dbtab_arr[dbname ,0])
		  {
		    return
		  }
		  else
		  { 
		    selcmd1="echo select partnum,tabname from systables
| dbaccess " dbname " 2>/dev/null | grep -v \"^$\" | grep -v tabname"
		    while(selcmd1 | getline)
		      dbtab_arr[dbname,$1]=$2
		    close(selcmd1)
		  }
	      }
		'