#!/usr/bin/ksh
###############################################################################
#
#       Module:         extent2.sh
#       Author:         Peter R. Schmidt
#       Description:    Report on table size for an online engine database
#
#       Change Log
#
#         Date    Name               Description.................
#       02/21/98  Peter R. Schmidt   Start Program 
#       07/08/99  Peter R. Schmidt   Make Improvements
#       07/29/99  Peter R. Schmidt   Total Allocated and Used
#
###############################################################################

OUTPUT=extent2.out
TMPFILE=extent2.tmp
PAGESIZE=2
BG=false

###############################################################################

if [ $# != 0 ]
then
        ARG1=$1
        case $ARG1 in 
                1|2|3|4) OPTION1=$ARG1;; 
                *)       OPTION1=2;; 
        esac

	ARG2=$2
        case $ARG2 in 
                1|2|3)   OPTION2=$ARG2;; 
                *)       OPTION2=1;; 
        esac

        BG=true
else

        clear

        while true
        do
                echo "Report on table sizes for an Informix online engine database"
                echo
                echo "1 = Size (allocated)"
                echo "2 = Number of extents"
                echo "3 = Table name"
                echo "4 = Free Space"
                echo "0 = Exit this program"
                echo
                echo "Enter sort sequence desired (0,1,2,3,4)"
                read OPTION1
        
                case $OPTION1 in 0|1|2|3|4) break;; esac
                echo
                echo "Error - you must enter 0,1,2,3 or 4!"
                echo
        done
        
        while true
        do
                echo
                echo "1 = Report in Megs"
                echo "2 = Report in Kbytes"
                echo "3 = Report in Pages"
                echo "0 = Exit this program"
                echo
                echo "Enter reporting unit desired (0,1,2,3)"
                read OPTION2
        
                case $OPTION2 in 0|1|2|3) break;; esac
                echo
                echo "Error - you must enter 0,1,2 or 3!"
                echo
        done
        
fi

case $OPTION1 in
        0)      echo "End requested by user"; exit;;
        1)      SORT=14; ORDER=desc; ORDERBY=Size;;
        2)      SORT=9;  ORDER=desc; ORDERBY=Extents;;
        3)      SORT=2;  ORDER=asc;  ORDERBY=Tablename;;
        4)      SORT=npfree; ORDER=desc;  ORDERBY=Free-Space;;
esac

case $OPTION2 in
        0)      echo "End requested by user"; exit;;
	1)	UNIT=M; UNITDESC=Mbytes;;
	2)	UNIT=K; UNITDESC=Kbytes;;
	3)	UNIT=P; UNITDESC=Pages;;
esac

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

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

echo "Collecting extent info from the sysmaster database..."
        
dbaccess << EOF
database sysmaster;
unload to '$TMPFILE' delimiter "|"
select 
        systabnames.dbsname,
        systabnames.tabname,
        sysptnhdr.*,
	(nptotal - npused) npfree
from
        systabnames, sysptnhdr
where
        systabnames.partnum = sysptnhdr.partnum
  and   systabnames.dbsname <> "sysmaster"
  and   systabnames.dbsname <> "sysutils"
  and   systabnames.tabname <> "TBLSpace"
order by $SORT $ORDER;
EOF

echo "unload completed"

XDATE=`date +%D-%T`

echo 
echo "Completed - formatting report..."
echo

awk ' \
###############################################################################
#
#       Module:         extent2.awk
#       Author:         Peter R. Schmidt
#       Description:    Report on table size for an online engine database
#       Called by:      extent2.sh
#
#       Expected variables passed at runtime:
#
#               pagesize        Informix Page Size
#               xdate           Todays date, format: mm/dd/yy-hh:mm:ss
#               orderby         Sort Order description
#               unit            Reporting Unit (M/K/P)
#
#       Expected Input: Pipe Delimited file with the following fields
#
#               database name
#               table name
#               sysptnhdr.* 
#               pages free
#
#       Change Log
#
#         Date    Name               Description.................
#       02/21/98  Peter R. Schmidt   Start Program 
#       07/08/99  Peter R. Schmidt   Make Improvements
#
###############################################################################
#       INITIALIZE VARIABLES AT BEGINNING

BEGIN {
        cntline=6
        pageno=1
        overflow=55
}

###############################################################################
#       FIRST LINE ONLY

{
if (NR == 1) {
        split (xdate,b,"-")
        udate=b[1]
        utime=b[2]
        printf "\n"
        printf "%s %s    Informix Table Size Report  (Numbers in %s)   Page: %d\n", udate, utime, unitdesc, pageno
        printf "\n"
        print "                      %              First     Next   # of  "
        print "Allocated    Free   Free      Used  Extent   Extent Extents  dbs:table\n"
        }
}

###############################################################################
#       ON EVERY LINE

{
        split ($1,a,"|")
        
        dbs=a[1]
        table=a[2]
        extents=a[9]
        fextsiz=a[12]
        nextsiz=a[13]
        alloc=a[14]
        used=a[15]
        data=a[16]
        unused1=a[5]
        
        free = (alloc - used);
        nondata = (used - data);

        alloc_K=alloc*pagesize

        free_K=free*pagesize
        used_K=used*pagesize
        fextsiz_K=fextsiz*pagesize
        nextsiz_K=nextsiz*pagesize

        alloc_M=alloc_K/1024
        free_M=free_K/1024
        used_M=used_K/1024
        fextsiz_M=fextsiz_K/1024
        nextsiz_M=nextsiz_K/1024

	tot_alloc_P += alloc
	tot_alloc_K += alloc_K
	tot_alloc_M += alloc_M

	tot_free_P += free
	tot_free_K += free_K
	tot_free_M += free_M

	tot_used_P += used
	tot_used_K += used_K
	tot_used_M += used_M

	tot_extents += extents

        perfree = 0;
        if (alloc > 0) {
                perfree = ((free / alloc) * 100)
        }

        if (extents > maxextents) { 
                maxextents = extents 
                maxtable   = table
        }
        
	if (unit == "M") {
        	printf "%8.1f  %7.1f  %5.1f  %8.1f %7.1f  %7.1f  %6d  %s:%s\n", alloc_M, free_M, perfree, used_M, fextsiz_M, nextsiz_M, extents, dbs, table
	}

	if (unit == "K") {
        	printf "%8d  %7d  %5.1f  %8d %7d  %7d  %6d  %s:%s\n", alloc_K, free_K, perfree, used_K, fextsiz_K, nextsiz_K, extents, dbs, table
	}

	if (unit == "P") {
        	printf "%8d  %7d  %5.1f  %8d %7d  %7d  %6d  %s:%s\n", alloc, free, perfree, used, fextsiz, nextsiz, extents, dbs, table
	}

        cntline++
}

###############################################################################
#       TOP OF PAGE

{
if (cntline == overflow) {
        pageno++
        printf "\f\n"
        printf "%s %s    Informix Table Size Report  (Numbers in %s)   Page: %d\n", udate, utime, unitdesc, pageno
        printf "\n"
        print "                      %              First     Next   # of  "
        print "Allocated    Free   Free      Used  Extent   Extent Extents  dbs:table\n"
        cntline=6
        }
}

###############################################################################
#       ON LAST LINE

END {
        perfree = 0;

        if (tot_alloc_P > 0) {
                perfree = ((tot_free_P / tot_alloc_P) * 100)
        }

        printf "\n"

	if (unit == "M") {
        	printf "%8.1f  %7.1f  %5.1f  %8.1f                   %6d\n", tot_alloc_M, tot_free_M, perfree, tot_used_M, tot_extents
	}

	if (unit == "K") {
        	printf "%8d  %7d  %5.1f  %8d                   %6d\n", tot_alloc_K, tot_free_K, perfree, tot_used_K, tot_extents
	}

	if (unit == "P") {
        	printf "%8d  %7d  %5.1f  %8d                   %6d\n", tot_alloc_P, tot_free_P, perfree, tot_used_P, tot_extents
	}

        printf "\n"
        printf "Number of tables:           %d\n",NR
        printf "Highest number of extents:  %d (%s)\n", maxextents, maxtable
        printf "Using Informix pagesize of: %d K\n", pagesize
        printf "Sorted by:                  %s\n", orderby
        printf "Reporting Unit              %s\n", unitdesc
        printf "\n"
}

###############################################################################
#       END OF AWK SCRIPT
' \
pagesize=$PAGESIZE \
xdate=$XDATE \
orderby=$ORDERBY \
unit=$UNIT \
unitdesc=$UNITDESC \
$TMPFILE > $OUTPUT 

if [ $BG = false ]
then
        pg $OUTPUT
fi

rm -f $TMPFILE

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