INFORMIX - Useful database monitoring scripts
Friends,
Following scripts can be used to monitor database activities without typing
onstat commands. This will greatly save your time and show you what is 
happening
on your server.

01. Index printing.
02. Table Vs Dbspace Information
03. User flag reading (onstat -u)
04. Ontape archive log history (report)
05. Lists tables locked by users.
06. Lists all the users waiting for lock.
07. Table Information
08. Check the Dbspace free space status.
09. Check the user activity (onstat -g ses <ses_id>)

These scripts are written using shell/awk tools. Some scripts grep the data 
from
onstat command or some uses Informix SMI tables. If you find anything wrong 
with the
scripts, feel free to contact me so that i can make them more accurate.

For comments, suggestions do write me at pmahale@yahoo.com

Note: Sample output of each script is attached at the end of it.

-Prasad
===========================================================================================

01. Index printing.

List the tables and their indexes with columns. This program accepts two 
parameters -
Database name and table name. If table name is omitted, this will print 
indexes for all
the tables in that database.

Run as - $ indexes2.sh <database_name> [<table name>]

---Cut here 
--indexes2.sh------------------------------------------------------------------

#  Utility to Print table and its indexes with column names
#  Written By - Prasad Mahale
#  Date - 05/01/1999
#  Usage - indexes2 <database name> [<table name>]
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
#############################################################################


if [ ! "$1" ]
then
   echo
   echo "USAGE: $0 <database> [<table>]"
   echo
   exit 2
fi

database_name=$1
if [ ! "$2" ]
then
   table_name="*"
else
   table_name=$2
fi

dbaccess $database_name 2> /dev/null << +
unload to indexes2.out
select A.tabname, B.idxname, B.idxtype, "1" sr, C.colname, B.part1 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part1) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "2" sr, C.colname, B.part2 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part2) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "3" sr, C.colname, B.part3 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part3) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "4" sr, C.colname, B.part4 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part4) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "5" sr, C.colname, B.part5 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part5) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "6" sr, C.colname, B.part6 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part6) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "7" sr, C.colname, B.part7 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part7) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "8" sr, C.colname, B.part8 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part8) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "9" sr, C.colname, B.part9 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part9) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "10" sr, C.colname, B.part10 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part10) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "11" sr, C.colname, B.part11 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part11) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "12" sr, C.colname, B.part12 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part12) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "13" sr, C.colname, B.part13 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part13) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "14" sr, C.colname, B.part14 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part14) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "15" sr, C.colname, B.part15 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part15) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"
union
select A.tabname, B.idxname, B.idxtype, "16" sr, C.colname, B.part16 part
from informix.systables A, informix.sysindexes B, informix.syscolumns C
where B.tabid = A.tabid
and   A.tabid > 99
and   A.tabtype = "T"
and   abs(B.part16) = C.colno
and   C.tabid = A.tabid
and   A.tabname matches "$table_name"

order by 1,2,4
+

echo "TABLE NAME           INDEX NAME           TYPE   #  COLUMNS            
   ORDER"
echo 
"=============================================================================="
cat indexes2.out | awk -F "|" '{
if ($6 < 0)
   { t_order= "desc"}
else
   { t_order=""}

if ($3 == "U")
   { t_type="Unique" }
else
   { t_type="Duplic" }

if (t_tab == $1)
   {
   if (t_col == $2)
      { printf("%-20s %-20s %-6s %-2s %-20s %-4s\n","","","",$4,$5,t_order) 
}
   else
      { printf("%-20s %-20s %-6s %-2s %-20s 
%-4s\n","",$2,t_type,$4,$5,t_order) }
   }
else
   { printf( "%-20s %-20s %-6s %-2s %-20s 
%-4s\n",$1,$2,t_type,$4,$5,t_order) }

t_tab=$1
t_col=$2
}'
rm indexes2.out

---Cut here 
-------------------------------------------------------------------------------

*END OF SCRIPT*


Sample Output -
=============

TABLE NAME           INDEX NAME           TYPE   #  COLUMNS              
ORDER
==============================================================================
ps_absence_hist      ps_absence_hist      Unique 1  emplid
                                                 2  empl_rcdn
                                                 3  begin_dt             
desc
                                                 4  absence_type
psx_tree_save        ps_psx_tree_save     Unique 1  setid
                                                 2  tree_name
                                                 3  effdt                
desc
psx_treedefnlng      ps_psx_treedefnlng   Unique 1  setid
                                                 2  tree_name
                                                 3  effdt                
desc
                                                 4  language_cd
psxferitem           ps_psxferitem        Unique 1  menuname
                                                 2  itemname
xlattable            ps_xlattable         Unique 1  fieldname
                                                 2  language_cd
                                                 3  fieldvalue
                                                 4  effdt
                     psaxlattable         Duplic 1  fieldname
                                                 2  language_cd
                                                 3  version
                     psbxlattable         Duplic 1  version
xlattabledel         ps_xlattabledel      Unique 1  fieldname
                                                 2  language_cd
                                                 3  version
                     psaxlattabledel      Duplic 1  version


===========================================================================================

02. Table Vs Dbspace Information
This scripts shows list of Dbspaces (with #chunks and size) and list of 
tables
(with #rows, #extents and size) residing in that dbspace. This also shows 
the
percentage of space occupied by that table in dbspace. Optional Input 
parameter
is <dbspace_name>. If <dbspace_name> is not specified, it will list all the 
dbspaces.

Run as - $ tab2dbsp.sh [<dbspace_name>]

---Cut here 
--tab2dbsp.sh------------------------------------------------------------------

#  Utility to Print list of tables in a dbspace
#  Written By - Prasad Mahale
#  Date - 05/11/1999
#  Usage - tab2dbsp.sh [<dbspace name>]
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
#############################################################################


if [ ! "$1" ]
then
   dbspace_name="*"
else
   dbspace_name=$1
fi

dbaccess sysmaster 2> /dev/null << +
unload to tab2dbsp.out
select trunc(a.partnum/1048576) dbsnum, a.dbsname, a.tabname,
       b.ti_nextns, b.ti_npused, b.ti_nrows
from systabnames a, systabinfo b
where a.partnum = b.ti_partnum;

create temp table t_tab2dbsp1 ( dbsnum integer, dbsname char(10),
tabname char(15), nextns smallint, npused integer, nrows integer);

load from tab2dbsp.out insert into t_tab2dbsp1;

unload to tab2dbsp.out
select a.dbsnum, a.name, count(*), sum(b.chksize)
from sysdbspaces a, syschunks b
where a.dbsnum = b.dbsnum
and name matches "$dbspace_name"
group by 1,2;

create temp table t_tab2dbsp2 ( dbsnum integer, dbspname char(10),
nchunks smallint, dbssiz integer);

load from tab2dbsp.out insert into t_tab2dbsp2;

unload to tab2dbsp.out
select a.dbspname, a.nchunks, a.dbssiz,
       b.dbsname,  b.tabname, b.nextns, b.nrows, b.npused
from   t_tab2dbsp2 a, t_tab2dbsp1 b
where  a.dbsnum = b.dbsnum
order by 1,8 desc;

drop table t_tab2dbsp1;
+

echo
cat tab2dbsp.out | awk -F "|" '{

if ( t_break != $1 )
   { printf("\nDBSPACE: %-15s #CHUNKS: %-3d  SIZE(PAGES): %-7d\n",$1,$2,$3)
     
print("===================================================================")
     print("TABLE/INDEX          DATABASE #EXTNS  #ROWS    #PAGES  % IN 
DBSPACE")
     
print("===================================================================") 
}

printf("%-20s %-10s  %-3d  %-8d  %-8d  %3.2f%\n",$5,$4,$6,$7,$8,$8/$3*100)

t_break=$1

}'
rm tab2dbsp.out

---Cut here 
--tab2dbsp.sh------------------------------------------------------------------

*END OF SCRIPT*


SAMPLE OUTPUT -
=============

DBSPACE: hcdmo7db        #CHUNKS: 1    SIZE(PAGES): 80000
===================================================================
TABLE/INDEX          DATABASE #EXTNS  #ROWS    #PAGES  % IN DBSPACE
===================================================================
pspnlfield           hcdmo7      1    65011     11114     13.89%
TBLSpace             hcdmo7db    37   0         3250      4.06%
sysdistrib           hcdmo7      3    31116     2504      3.13%
psprojectitem        hcdmo7      4    55712     2424      3.03%
psrecfield           hcdmo7      1    58462     2340      2.93%
pspcmprog            hcdmo7      1    14673     2307      2.88%
pspcmname            hcdmo7      1    84893     1393      1.74%
psauthitem           hcdmo7      1    35756     1234      1.54%
ps_pay_check         hcdmo7      2    7385      1232      1.54%
sysconstraints       hcdmo7      52   31907     1113      1.39%
sysobjstate          hcdmo7      52   36657     1107      1.38%
ps_local_tax_tb      hcdmo7      1    21858     1094      1.37%
ps_pay_earnings      hcdmo7      2    13943     931       1.16%
psactivitymap        hcdmo7      1    713       891       1.11%
ps_pay_deductio      hcdmo7      2    45710     776       0.97%
syscolumns           hcdmo7      43   53583     732       0.92%

===========================================================================================

03. User thread monitoring (onstat -u)
This shows the activity of user thread using onstat -u flags. Basically this
describes the status of onstat -u flag. Optional Input parameter is 
<user_id>.
If <user_id> is not specified, it will list all the user threads.

Run as - $ userflag.sh [<user_id>]

---Cut here 
--userflag.sh------------------------------------------------------------------

#  Script to print thread status using "onstat -u" flags
#  Written By - Prasad Mahale
#  Date - 05/10/1999
#  Usage - userflag.sh [<user-id>]
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
#
##############################################################################

echo "Thread activity"
echo 
"================================================================================"
echo "USER     SESS#    WAIT    TXN        ACTVT        PRIM THR   ACTVT     
THR TYP"
echo 
"================================================================================"
if [ ! "$1" ]
then
   user_name="-"
else
   user_name=$1
fi
onstat -u | grep -v "Dynamic Server" | grep -v Userthreads |
grep -v "address  flags" | grep -v "maximum concurrent" | grep -v ^$ |
grep $user_name | awk -F " "
'{

# First flag
t_flag1="--"
if (substr($2,1,1) == "B")
   { t_flag1="Buff" }

if (substr($2,1,1) == "C")
   { t_flag1="Chkpt" }

if (substr($2,1,1) == "G")
   { t_flag1="LLBufwrt" }

if (substr($2,1,1) == "L")
   { t_flag1="Lock" }

if (substr($2,1,1) == "S")
   { t_flag1="Mutx" }

if (substr($2,1,1) == "T")
   { t_flag1="Tran" }

if (substr($2,1,1) == "Y")
   { t_flag1="Cond" }

if (substr($2,1,1) == "X")
   { t_flag1="Rolbk" }


# Second flag
t_flag2="--"
if (substr($2,2,1) == "o")
   { t_flag2="I/O failur" }

# Third flag
t_flag3="--"
if (substr($2,3,1) == "A")
   { t_flag3="Archive" }

if (substr($2,3,1) == "B")
   { t_flag3="Begin Work" }

if (substr($2,3,1) == "P")
   { t_flag3="I*rdy2cmt" }

if (substr($2,3,1) == "X")
   { t_flag3="TPXArdy2cmt" }

if (substr($2,3,1) == "C")
   { t_flag3="Commit" }

if (substr($2,3,1) == "R")
   { t_flag3="Rollback" }

if (substr($2,3,1) == "H")
   { t_flag3="Heuri rolbk" }


# Fourth flag
t_flag4="--"
if (substr($2,4,1) == "P")
   { t_flag4="Prim thrd" }


# Fifth flag
t_flag5="--"
if (substr($2,5,1) == "R")
   { t_flag5="Reading" }

if (substr($2,5,1) == "X")
   { t_flag5="CrtclSect" }


# Sixth flag
t_flag6="--"

# Seventh flag
t_flag7="--"
if (substr($2,7,1) == "B")
   { t_flag7="BtreClnr" }

if (substr($2,7,1) == "C")
   { t_flag7="ThrdClnup" }

if (substr($2,7,1) == "D")
   { t_flag7="Dmon thrd" }

if (substr($2,7,1) == "F")
   { t_flag7="Pg clnr" }

if (substr($2,7,1) == "M")
   { t_flag7="On monit" }


printf("%-10s %-6d %-8s %-10s %-11s %-10s %-9s %-9s\n",$4,$3,t_flag1,
t_flag2,t_flag3,t_flag4,t_flag5,t_flag7)
}'

---Cut here 
--userflag.sh------------------------------------------------------------------

*END OF SCRIPT*

SAMPLE OUTPUT -
=============

Thread activity
================================================================================
USER     SESS#    WAIT    TXN        ACTVT        PRIM THR   ACTVT     THR 
TYP
================================================================================
informix   1      --       --         --          Prim thrd  --        Dmon 
thrd
informix   0      --       --         --          Prim thrd  --        Pg 
clnr
informix   0      --       --         --          Prim thrd  --        Pg 
clnr
informix   0      --       --         --          Prim thrd  --        Pg 
clnr
informix   0      --       --         --          Prim thrd  --        Pg 
clnr
informix   0      --       --         --          Prim thrd  --        Pg 
clnr
informix   0      --       --         --          Prim thrd  --        Pg 
clnr
informix   0      --       --         --          Prim thrd  --        Pg 
clnr
informix   0      --       --         --          Prim thrd  --        Pg 
clnr
informix   9      --       --         --          Prim thrd  --        --
informix   10     --       --         --          Prim thrd  --        
BtreClnr
informix   12     --       --         --          Prim thrd  --        Dmon 
thrd
sysadm     952    Cond     --         --          Prim thrd  --        --
sysadm     947    Cond     --         --          Prim thrd  --        --
sysadm     184    Cond     --         --          Prim thrd  --        --
sysadm     512    Cond     --         --          Prim thrd  --        --
sysadm     948    Cond     --         --          Prim thrd  --        --


===========================================================================================

04. Ontape archive log history
This script grabs the ontape archive history from online log file and 
formats
it into report. You can view archive history as backup level, start time,
finish time and duration of archive by running this script.

Run as - $ archlog.sh

---Cut here 
--archlog.sh-------------------------------------------------------------------

#  Script to print Ontape archive log
#  Written By - Prasad Mahale
#  Date - 05/16/1999
#  Usage - archlog.sh
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
#############################################################################

logfile=`onstat -m | grep "Message Log File" | awk '{ print $4 }' `

echo
echo "Ontape Archive Log"
echo 
"======================================================================="
echo "LEVEL   START DATE/TIME        FINISH DATE/TIME    STATUS      
DURATION"
echo "                                                               
HH:MM:SS"
echo 
"======================================================================="

grep -e Archive -e 2000 $logfile | grep -v "Process exited" | awk '{

   if ( $5 == "2000" )
      { t_date=$1" "$2" "$3 }

   if ( $2 == "Level" )
      { printf("\n  %1s   %-10s %8s    ", $3, t_date, $1)
        s_time=$1 }

   if ( $2 == "Archive" )
      { printf("%-10s %8s   %-10s", t_date, $1, $NF)
        f_time=$1

        #
        #Following is to get archive duration
        #

        #
        #generate start time info
        #

        s_hh=substr(s_time,1,2)
        s_mm=substr(s_time,4,2)
        s_ss=substr(s_time,7,2)

        s_h2s= s_hh * 60 * 60
        s_m2s= s_mm * 60
        s_s2s= s_ss

        s_totsec= s_h2s + s_m2s + s_s2s

        #
        #generate finish time info
        #

        f_hh=substr(f_time,1,2)
        f_mm=substr(f_time,4,2)
        f_ss=substr(f_time,7,2)

        if ( f_hh < s_hh )
           { f_hh= f_hh + 24 }

        f_h2s= f_hh * 60 * 60
        f_m2s= f_mm * 60
        f_s2s= f_ss

        f_totsec= f_h2s + f_m2s + f_s2s

        #
        #Take difference between start time and end time
        #
       d_totsec= f_totsec - s_totsec


        #
        #Calculate the difference in HH:MM:SS
        #

        d_hh= int( d_totsec / 3600 )

        d_totsec= d_totsec - d_hh * 3600

        d_mm= int( d_totsec / 60 )

        d_totsec= d_totsec - d_mm * 60

        d_ss=d_totsec

        if ( length(d_hh) < 2 )
           { d_hh="0"d_hh }

        if ( length(d_mm) < 2 )
           { d_mm="0"d_mm }

        if ( length(d_ss) < 2 )
           { d_ss="0"d_ss }

        d_time=d_hh":"d_mm":"d_ss

        printf("  %8s",d_time)    }

}'

---Cut here 
--archlog.sh-------------------------------------------------------------------

*END OF SCRIPT*


SAMPLE OUTPUT -
===============

Ontape Archive Log
=======================================================================
LEVEL   START DATE/TIME        FINISH DATE/TIME    STATUS      DURATION
                                                               HH:MM:SS
=======================================================================
  0   Sun Apr 30 14:26:39    Sun Apr 30 14:55:16   Completed.  00:28:37
  0   Mon May 1  16:40:09    Mon May 1  16:40:11   ABORTED.    00:00:02
  0   Mon May 1  16:43:02    Mon May 1  17:12:03   Completed.  00:29:01
  0   Tue May 2  16:44:16    Tue May 2  17:13:48   Completed.  00:29:32
  0   Thu May 4  09:19:06    Thu May 4  09:48:12   Completed.  00:29:06
  0   Fri May 5  09:54:21    Fri May 5  10:24:00   Completed.  00:29:39
  0   Tue May 9  13:06:05    Tue May 9  13:35:19   Completed.  00:29:14
  0   Tue May 9  23:02:34    Tue May 9  23:31:53   Completed.  00:29:19
  0   Wed May 10 23:33:27    Thu May 11 00:03:22   Completed.  00:29:55
  0   Thu May 11 22:55:17    Thu May 11 23:24:23   Completed.  00:29:06
  0   Mon May 15 00:33:49    Mon May 15 01:03:59   Completed.  00:30:10
  0   Mon May 15 23:22:16    Mon May 15 23:51:14   Completed.  00:28:58
  0   Tue May 16 11:13:11    Tue May 16 11:42:16   Completed.  00:29:05

===========================================================================================

05. Lists tables locked by users.
This lock status script shows userwise usage of locks along with database 
name,
table name and type of lock.

Run as - $ locks.sh

---Cut here 
--locks.sh---------------------------------------------------------------------

#  Utility to Print lock status
#  Written By - Prasad Mahale
#  Date - 05/04/1999
#  Usage - locks.sh
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
#
#################################################################################
dbaccess sysmaster 2> /dev/null <<+
unload to locks.out
select a.dbsname, a.tabname, a.rowidlk, a.type, a.owner, b.username
from syslocks a, syssessions b
where a.owner = b.sid
order by a.owner;
+
cat locks.out | awk -F "|" '{

t_llevel=$3
if (length($3)<7)
   { t_llevel="ROW" }

if ($3 == "0")
   { t_llevel="TABLE" }

if (substr($3,length($3)-1,2) == "00")
   { t_llevel="PAGE" }

if (length($3)>6)
   { t_llevel="IDX KEY" }

t_ltype=$4
if ($4 == "B")
   { t_ltype="BYTES" }

if ($4 == "S")
   { t_ltype="SHRED" }

if ($4 == "X")
   { t_ltype="EXCLV" }

if ($4 == "I")
   { t_ltype="INTNT" }

if ($4 == "U")
   { t_ltype="UPDAT" }

if ($4 == "IX")
   { t_ltype="INT-EX" }

if ($4 == "IS")
   { t_ltype="INT-SHR" }

if ($4 == "SIX")
   { t_ltype="SHR-INT-EX" }


print($5"|"$6"|"$1"|"$2"|"t_llevel"|"t_ltype"|")

}' > locks1.out

dbaccess sysmaster 2> /dev/null <<+
create temp table t_lloocckk (session integer, usernm char(15), db_name 
char(15)
, tb_name char(20),level char(10), type char(15));
load from locks1.out insert into t_lloocckk;
unload to locks.out
select session, usernm, db_name, tb_name, level, type, count(*)
from t_lloocckk
group by 1,2,3,4,5,6
order by 7 desc;
drop table t_lloocckk;
+

echo "Lock usage report                    Total locks in use" `onstat -k | 
tail
-2 | grep active | awk '{print $1, "out of", $3}'`
echo 
"----------------------------------------------------------------------------"
echo "SESSION  OWNER      DATABASE   TABLE            LEVEL     TYPE      
#LOCKS"
echo 
"----------------------------------------------------------------------------"

cat locks.out | awk -F "|" '{

printf("%8d %-10s %-10s %-18s %-7s %-10s %-6d\n",$1,$2,$3,$4,$5,$6,$7)

}'

rm locks.out
rm locks1.out

---Cut here 
--locks.sh---------------------------------------------------------------------

*END OF SCRIPT*

Sample Output -
=============

Lock usage report                    Total locks in use 10834 out of 500000
----------------------------------------------------------------------------
SESSION  OWNER      DATABASE   TABLE            LEVEL     TYPE      #LOCKS
----------------------------------------------------------------------------
     596 jkonan     hcdmo7       ps_psprojectitem   ROW     EXCLV      4844
     596 jkonan     hcdmo7       psprojectitem      ROW     EXCLV      4844
     596 jkonan     hcdmo7       ps_psprojectitem   PAGE    EXCLV      43
     596 jkonan     hcdmo7       psprojectitem      PAGE    EXCLV      43
     184 tgulch     sysmaster    sysdatabases       ROW     SHRED      1
     597 mkhill     sysmaster    sysdatabases       ROW     SHRED      1
     596 jkonan     hcdmo7       psprojectmsg       ROW     EXCLV      1
     637 rloryl     sysmaster    sysdatabases       ROW     SHRED      1
     596 jkonan     hcdmo7       psprojectmsg       TABLE   INT-EX     1
     596 jkonan     hcdmo7       ps_psprojectmsg    ROW     EXCLV      1
     512 rkumar     sysmaster    sysdatabases       ROW     SHRED      1
     596 jkonan     sysmaster    sysdatabases       ROW     SHRED      1
     596 jkonan     hcdmo7       psprojectitem      TABLE   INT-EX     1

===========================================================================================

06. Lists all the users waiting for lock.
When some batch job runs, others have nothing but to wait for locks. From
onstat -u, letter "L" in first position of flag column indicates that the
session is waiting for lock and from corresponding address in onstat -k
output, you can get owner address of that lock and again from onstat -u,
finally, you can find out who's that culprit. I have tried to simplify
this lengthy process in the following script.

Run as - $ userlock.sh

---Cut here 
--userlock.sh------------------------------------------------------------------

#  Utility to Print sessions waiting for the lock
#  Written By - Prasad Mahale
#  Date - 05/02/1999
#  Usage - userlock.sh
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
#
################################################################################
dbaccess sysmaster 2>/dev/null <<+
unload to userlock.out
select a.us_name, a.us_sid,
       c.us_name, c.us_sid
from sysuserthreads a, syslocktab b, sysuserthreads c
where  b.lk_addr = a.us_lkwait
and    b.lk_owner= c.us_txp;
+

echo "List of users waiting for Lock"
echo 
"-------------------------------------------------------------------------"
echo "USER          SESSION                               USER          
SESSION"
echo 
"-------------------------------------------------------------------------"
cat userlock.out | awk -F "|" '{

printf("%-10s %10s waiting to release lock from  %-10s %10s\n",$1,$2,$3,$4)

}'
rm userlock.out

---Cut here 
--userlock.sh------------------------------------------------------------------

*END OF SCRIPT*


Sample Output -
=============

List of users waiting for Lock
-------------------------------------------------------------------------
USER          SESSION                               USER          SESSION
-------------------------------------------------------------------------
sfarar            345 waiting to release lock from  radlar            343
mgary             437 waiting to release lock from  radlar            343
thighl            438 waiting to release lock from  radlar            343
skumar            577 waiting to release lock from  tgulch            221

===========================================================================================

07. Table Information
This scripts shows table names with other info like rowsize, no of rows,
no of columns, no of extents...etc. Input parameters are <database_name>
and <table_name>. If <table_name> is omitted, it will list all the tables
in that database. This utility has an option menu to choose sort order on
report columns. i.e. You can format the output as order by table names,
no of columns, no of extents and so on.

Run as - $ tabinfo.sh <database_name> [<table_name>]

---Cut here 
--tabinfo.sh-------------------------------------------------------------------

#  Script to Print table information
#  Written By - Prasad Mahale
#  Date - 05/04/1999
#  Usage - tabinfo <database name> [<table name>]
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
############################################################################

get_order()
{
echo "Order by - 1. Table name"
echo "           2. Rowsize   "
echo "           3. # Columns "
echo "           4. # Rows    "
echo "           5. # Indexes "
echo "           6. # Extents "
echo "           7. Size      "
echo
echo "Enter Choice :"
read choice

case $choice in
1) t_order=1
   t_sort="TABLE NAME"
   ;;
2) t_order=`echo "2 desc"`
   t_sort="ROWSIZE"
   ;;
3) t_order=`echo "4 desc"`
   t_sort="# COLUMNS"
   ;;
4) t_order=`echo "5 desc"`
   t_sort="# ROWS"
   ;;
5) t_order=`echo "6 desc"`
   t_sort="# INDEXES"
   ;;
6) t_order=`echo "9 desc"`
   t_sort="# EXTENTS"
   ;;
7) t_order=`echo "10 desc"`
   t_sort="SIZE"
   ;;
*) t_order=1
   t_sort="TABLE NAME"
   ;;
esac


}

if [ ! "$1" ]
then
   echo
   echo "USAGE: $0 <database> [<table>]"
   echo
   exit 2
fi

database_name=$1
if [ ! "$2" ]
then
   table_name="*"
   get_order
else
   table_name=$2
   t_order=1
   t_sort="TABLE NAME"
fi

dbaccess $database_name 2> /dev/null << +
unload to tabinfo.out
select a.tabname, a.rowsize, a.locklevel, a.ncols, a.nrows, a.nindexes,
a.fextsize, a.nextsize, count(*), sum(b.te_size), trunc(a.partnum/1048576)
from informix.systables a, sysmaster@$INFORMIXSERVER:informix.systabextents 
b
where a.tabid > 99
and   a.tabtype = "T"
and   a.tabname matches "$table_name"
and   b.te_partnum=a.partnum
group by 1,2,3,4,5,6,7,8,11
order by $t_order
+

echo "Table info for database "$database_name"                   Sorted on : 
"$t_sort
echo 
"==============================================================================="
echo "TABLE               Dbs#  ROW LCK #COLS  #ROWS #IDX   FEXT  NEXT  
TOTAL  TOTAL"
echo "                          SIZ LVL                     (Kb)  (Kb)  
#EXTS SIZ(Kb)"
echo 
"==============================================================================="

cat tabinfo.out | awk -F "|" '{
   { printf( "%-20s %2d  %4d  %1s %3d %8d  %2d  %6d %6d %3d  
%8d\n",$1,$11,$2,$3
,$4,$5,$6,$7,$8,$9,$10*4) }

#$10*4 => 4 is page size in kb

}'

rm tabinfo.out

---Cut here 
--tabinfo.sh-------------------------------------------------------------------

*END OF SCRIPT*


Sample Output -
=============

Order by - 1. Table name
           2. Rowsize
           3. # Columns
           4. # Rows
           5. # Indexes
           6. # Extents
           7. Size

Enter Choice :  6

Table info for database hcdmo7                   Sorted on : # EXTENTS
===============================================================================
TABLE               Dbs#  ROW LCK #COLS  #ROWS #IDX   FEXT  NEXT  TOTAL  
TOTAL
                          SIZ LVL                     (Kb)  (Kb)  #EXTS 
SIZ(Kb)
===============================================================================
psprojectitem        12   172  R  16    13839   1      32     32   4      
9696
pst_pnlfields        12   524  R  62       15   1      32     32   3       
160
ps_pa_hst_pay_earn   12    79  R  21      889   2      32     32   2        
96
ps_pa_eacct_accum    12   174  R  25     1580   1      32     32   2       
320
ps_pa_clc_soc_ay     12    96  R  13      892   1      32     32   2        
96
ps_pay_earnings      12   254  R  59    13943   3      32     32   2      
3744
ps_ins_earns_bal     12    68  R  16     8327   1      32     32   2       
608
ps_leave_accrual     12    86  R  17     7546   1      32     32   2       
704
ps_personal_data     12  1212  R  89      371   5      32     32   2       
512
ps_pay_oth_earns     12    82  R  24     4098   2      32     32   2       
384
ps_pers_data_effdt   12  1033  R  52      380   5      32     32   2       
512
ps_sqlstmt_tbl       12    72  R   4     2086   1      32     32   2      
1152
ps_st_contract_cls   12   326  R   5       37   1      32     32   2        
64
ps_health_benefit    12    91  R  19     1050   2      32     32   2       
128

===========================================================================================

08. Check the Dbspace free space status. Combination of two part output of
    onstat -d

Run as - $ dbschk.sh

---Cut here 
--dbschk.sh--------------------------------------------------------------------

#  Utility to Print DBspace free space status
#  Written By - Prasad Mahale
#  Date - 05/02/1999
#  Usage - dbschk.sh
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
#
#####################################################################################
dbaccess sysmaster 2> /dev/null <<+
unload to dbschk.out
select a.name, count(*), sum(b.chksize), sum(b.nfree)
from sysdbspaces a, syschunks b
where a.dbsnum = b.dbsnum
group by 1;
+
echo "DBspace status report"
echo "-------------------------------------------------------"
echo "DBSPACE          #CHUNKS    SIZE(KB)  FREE(KB)    %USED"
echo "-------------------------------------------------------"
cat dbschk.out | awk -F "|" '{
printf("%-20s %2d %10d %10d      %2d\%\n",$1,$2,$3*4,$4*4, ($3-$4)/$3*100)
}'
rm dbschk.out

---Cut here 
--dbschk.sh--------------------------------------------------------------------

*END OF SCRIPT*

Sample Output -
=============

DBspace status report
-------------------------------------------------------
DBSPACE          #CHUNKS    SIZE(KB)  FREE(KB)    %USED
-------------------------------------------------------
hr2llog1              1     960000       9788      98%
hcdmodb               1     320000     158244      50%
hrdmoix               1     319996     208688      34%
hr2root               1      96000      92036       4%
hcdmo7fix             1     320000      86760      72%
hcdmo7ix              1     320000      79412      75%
hcdmoix               1     320000     229868      28%
hrauddb               1     160000     141892      11%
hrdmodb               1     319996     187856      41%
hrcnvix               1    1959996    1950384       0%
hcdmo7fdb             1     320000      19700      93%
hrcnvdb               1    1959996    1953584       0%
hcdmo7db              1     320000      19572      93%
hr2plog               1      64000       1788      97%
hr2temp1              1     320000     319788       0%
hr2temp2              1     320000     319756       0%

==========================================================================================
09. Check the user activity (onstat -g ses <ses_id>)
    This script is similar to onstat -g ses ses_id. Insted of typing 
individual
    session id each time, this script will show you briefly which user is 
running
    what query with additional info.

Run as - $ session.sh

---Cut here 
--session.sh--------------------------------------------------------------------

#  Utility to Print user activities (onstat -g ses)
#  Written By - Prasad Mahale
#  Date - 04/30/1999
#  Usage - session.sh
#  Friends, This utility may not be 100% correct. Feel free to modify this
#  according to your requirement.
#  If you find anything wrong in the logic of script or you have better 
version
#  of this utility or just even to send comments or suggestions, mail me at
#  pmahale@yahoo.com.
#  For sample output of this script, visit 
www.geocities.com/pmahale/inf/tools
#
################################################################################

dbaccess sysmaster 2> /dev/null <<+
unload to session.out
select a.username, b.sqs_sessionid, b.sqs_dbname, b.sqs_iso, b.sqs_lockmode,
b.sqs_sqlerror, b.sqs_isamerror, b.sqs_statement from syssessions a,
syssqlstat b where a.sid = b.sqs_sessionid;
+
cat session.out | awk -F "|" '{

t_iso=$4

if ($4 == "1")
   { t_iso="DR" }

if ($4 == "2")
   { t_iso="CR" }

if ($4 == "3")
   { t_iso="CS" }

if ($4 == "5")
   { t_iso="RR" }

printf("USER  :%-10s  SESS ID :%-10d  DATABASE:%-10s  ISO:%2s   
LOCKS:%2d\n",$1,
$2,$3,t_iso,$5)

printf("SQLERR:%-10d  ISAM ERR:%-10d\n", $6,$7)
print("SQL:", $8)
print("-------------------------------------------------------------------------------")

}'
rm session.out

---Cut here 
--session.sh-----------------------------------------------------------------

*END OF SCRIPT*


Sample Output -
=============

-------------------------------------------------------------------------------
USER  :sysadm      SESS ID :437         DATABASE:hcdmo7f     ISO:RR   
LOCKS:-1
SQLERR:0           ISAM ERR:0
SQL: select * from ps_absence_hist
-------------------------------------------------------------------------------
USER  :sysadm      SESS ID :345         DATABASE:hcdmo7f     ISO:RR   
LOCKS:-1
SQLERR:0           ISAM ERR:0
SQL: select * from ps_absence_hist
-------------------------------------------------------------------------------
USER  :sysadm      SESS ID :343         DATABASE:hcdmo7f     ISO:RR   LOCKS: 
0
SQLERR:0           ISAM ERR:0
SQL: lock table ps_absence_hist in exclusive mode
-------------------------------------------------------------------------------
USER  :sysadm      SESS ID :270         DATABASE:hcdmo7      ISO:CR   
LOCKS:-1
SQLERR:0           ISAM ERR:0
SQL: SELECT 
MENUNAME,BARNAME,BARITEMNAME,PNLITEMNAME,DISPLAYONLY,AUTHORIZEDACTIO
NS, OPRID FROM PSAUTHITEM WHERE OPRID = 'ALLPANLS' AND MENUNAME = 
'APPLICATION_D
ESIGNER' FOR READ ONLY
-------------------------------------------------------------------------------
USER  :sysadm      SESS ID :184         DATABASE:hcdmo       ISO:CR   
LOCKS:-1
SQLERR:0           ISAM ERR:0
SQL: SELECT CURRENT YEAR TO FRACTION (3) FROM PSCLOCK ORDER BY 1 FOR READ 
ONLY
-------------------------------------------------------------------------------

===========================================================================================