#!/usr/bin/ksh
###############################################################################
#
#       Module:         most_active1.sh
#       Author:         Peter R. Schmidt
#       Description:    Determine most active Informix sessions
#
#       Change Log
#
#         Date    Name               Description.................
#       08/24/99  Peter R. Schmidt   Start Program 
#
###############################################################################

OUTPUT=most_active.out
TMPFILE=most_active.tmp

XDATE=`date +%D-%T`
MACHINE=`uname -n`

BOLD=`tput smso`
NORM=`tput rmso`

tput clear              # clear screen

if [ -f $OUTPUT ]
then
        rm -f $OUTPUT
fi

if [ -f $TMPFILE ]
then
        rm -f $TMPFILE
fi

#-------------------------------------------------------------------------------
#   Get seconds to wait between snapshots
#
echo "This program attempts to determine the most active sessions"
echo "by taking two snapshots of systems activity, and seeing who"
echo "had the most activity in that duration."
echo
echo "Enter number of seconds to wait between snapshots (0 is ok)."
read WAITTIME

#-------------------------------------------------------------------------------

echo
echo "Collecting database info from the sysmaster database..."
        
dbaccess <<-EOF
database sysmaster;
create temp table tmp_most_activity (
    snap_num   integer,
    username   char(18),
    session_id integer,
    lockreqs   integer,
    buff_io    integer,
    isam_io    integer,
    sort_io    integer
);

---------------------------------
! echo "Getting 1st snapshot..."
---------------------------------

insert into tmp_most_activity
select  "1",
    username,
        syssesprof.sid session_id,
        lockreqs,
        (bufreads + bufwrites) buff_io,
    (isreads + iswrites + isrewrites + isdeletes + iscommits + isrollbacks) isam_io,
    (total_sorts + dsksorts) sort_io
from    syssesprof, syssessions
where   syssesprof.sid = syssessions.sid;

--------------------------------------
! echo "wait for $WAITTIME seconds..."
! sleep $WAITTIME
! echo "Getting 2nd snapshot..."
--------------------------------------

insert into tmp_most_activity
select  "2",
    username,
        syssesprof.sid session_id,
        lockreqs,
        (bufreads + bufwrites) buff_io,
    (isreads + iswrites + isrewrites + isdeletes + iscommits + isrollbacks) isam_io,
    (total_sorts + dsksorts) sort_io
from    syssesprof, syssessions
where   syssesprof.sid = syssessions.sid;

----------------------------------------------------
! echo "Comparing snapshots for highest activity..."
----------------------------------------------------

unload to '$TMPFILE' delimiter "|"
select  snap1.username,
        snap1.session_id,
        sum (snap2.lockreqs - snap1.lockreqs) xlockreqs, 
        sum (snap2.buff_io  - snap1.buff_io)  xbuff_io,
        sum (snap2.isam_io  - snap1.isam_io)  xisam_io,
        sum (snap2.sort_io  - snap1.sort_io)  xsort_io
from
    tmp_most_activity snap1,
    tmp_most_activity snap2
where
    snap1.snap_num = 1  and
    snap2.snap_num = 2  and
    snap1.session_id = snap2.session_id 
group by 1,2
having  
        sum (snap2.lockreqs - snap1.lockreqs) > 0 or
        sum (snap2.buff_io  - snap1.buff_io)  > 0 or
        sum (snap2.isam_io  - snap1.isam_io)  > 0 or
        sum (snap2.sort_io  - snap1.sort_io)  > 0
order by xbuff_io desc;
    
drop table tmp_most_activity;
EOF

#-------------------------------------------------------------------------------

if [ ! -f $TMPFILE ]
then
    echo "Error: program stopped."
    exit 1
fi

#-------------------------------------------------------------------------------

awk ' \

BEGIN {
    FS="|"
}

{
    if (NR == 1) {
            split (xdate,b,"-")
            udate=b[1]
            utime=b[2]
    
            printf "%s %s    Informix Session Activity Report   for %s@%s\n\n", udate, utime, server, machine
        printf "User               Session   Lock     Buffer    Isam    Sort\n"
        printf "Name                  ID   Requests    I/O       I/O     I/O \n"
        print ""
    }

    username    = $1
    session_id  = $2
    lockreqs    = $3
    buff_io     = $4
    isam_io     = $5
    sort_io     = $6

    printf  "%-18s %6d  %6d    %6d   %6d %6d\n", username, session_id, lockreqs, buff_io, isam_io, sort_io

}

' xdate=$XDATE machine=$MACHINE server=$INFORMIXSERVER $TMPFILE > $OUTPUT

echo
echo "${BOLD}Note: Exit report to see more detail per session.${NORM}"
echo

pg $OUTPUT

for LINE in `cat $TMPFILE`
do
    USER_ID=`echo $LINE | cut -d"|" -f1`
    SESSION_ID=`echo $LINE | cut -d"|" -f2`
    echo
    echo "Press <Enter> to see details for USER $USER_ID (session $SESSION_ID), (<Q> to quit) "
    read answer
    if [ "$answer" = "q" -o "$answer" = "Q" ]

    then
        break
    fi
    echo "${BOLD}                                                                                ${NORM}"
    onstat -g ses $SESSION_ID
    echo "${BOLD}                                                                                ${NORM}"
done

rm -f $TMPFILE

echo
echo "Note: Output report is in $OUTPUT"