################################################################################
#
#  Module:	check_dbs_extents.sh
#
#  Description:	Check Informix extent info 
#       	Note: this program used extent1.sh as a model for collecting
#		the Informix info (uses the sysmaster database)
#		
#  Author:	Peter R. Schmidt
#
#  Change Log
#
#    Date       Person                  Description 
#
#  12/21/1999   Peter Schmidt		Start program
#  01/10/2000   Peter Schmidt		Do not run db.env if $INFORMIXDIR is set
#
################################################################################

if [ $# != 2 ]
then
	echo "Usage: check_dbs_extents.sh [WARNING LIMIT] [EMERGENCY LIMIT]"
	exit 1
fi

WARNING_LIMIT=$1
EMERGENCY_LIMIT=$2
EXCLUDE_LIST=":sys|TBLSpace|th_overflow_ffffff"

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

CODE_NAME=check_dbs_extents

if [ "x$INFORMIXDIR" = "x" ]
then
	. db.env			# Set the informix environment
fi

TMPFILE1=${CODE_NAME}_1.tmp
TMPFILE2=${CODE_NAME}_2.tmp
TMPFILE3=${CODE_NAME}_3.tmp

rm -f $TMPFILE1 $TMPFILE2 $TMPFILE3

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

SORT="1,2"
ORDER=asc;
UNIT=K
UNITDESC=Kbytes
PAGESIZE=2

dbaccess <<-EOF >/dev/null 2>&1
database sysmaster;
unload to '$TMPFILE3' delimiter "|"
select 
        dbsname,
        tabname,
	sysptnhdr.fextsiz,
	sysptnhdr.nextsiz,
        count(*) num_of_extents,
        sum(pe_size) total_size
from
        systabnames, sysptnext, sysptnhdr
where
        systabnames.partnum = pe_partnum
  and   systabnames.partnum = sysptnhdr.partnum
  and   systabnames.partnum  > 99
  and   systabnames.dbsname <> "sysmaster"
--and   systabnames.dbsname <> "rootdbs"
group by 1,2,3,4
order by 1,2,3,4
EOF

awk ' \

{
        split ($1,a,"|")
        
        dbs=a[1]
        table=a[2]
	fextsize_P=a[3]
	nextsize_P=a[4]
        num_extents=a[5]
        size_P=a[6]

	if (num_extents == 1) continue
        
        size_K=size_P*pagesize
        size_M=size_K/1024
        size_B=size_K*1024

	fextsize_K=fextsize_P*pagesize
	nextsize_K=nextsize_P*pagesize

        tot_P += size_P
        tot_K += size_K
        tot_M += size_M
        tot_B += size_B

    dbs_table = dbs ":" table
        
    if (unit == "M") {
            printf "%-30s  %3d        %10.2f %d %d\n", dbs_table, num_extents, size_M, fextsize_M, nextsize_M
    }

    if (unit == "K") {
            printf "%-30s  %3d        %10d %d %d\n", dbs_table, num_extents, size_K, fextsize_K, nextsize_K
    }

    if (unit == "P") {
            printf "%-30s  %3d        %10d %d %d\n", dbs_table, num_extents, size_P, fextsize_P, nextsize_P
    }

    if (unit == "B") {
            printf "%-30s  %3d        %10d %d %d\n", dbs_table, num_extents, size_B, fextsize_B, nextsize_B
    }

        cntline++
}

###############################################################################
#       END OF AWK SCRIPT

' \
pagesize=$PAGESIZE \
unit=$UNIT \
unitdesc=$UNITDESC \
$TMPFILE3 > $TMPFILE1

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

> $STAT_FILE

while read DBS_TABLE EXTENTS SIZE FIRST NEXT
do                                    

	CHK_SYS_TABLE=`echo $DBS_TABLE | egrep "${EXCLUDE_LIST}" | wc -l`

	if [ $CHK_SYS_TABLE = 1 ]
	then
		continue		# SKIP REPORTING on 'system' tables
	fi

	DATABASE=`echo $DBS_TABLE | cut -d":" -f1`
	TABLE=`echo $DBS_TABLE | cut -d":" -f2`

	if [ $EXTENTS -gt $EMERGENCY_LIMIT ]
	then
		echo "******* EMERGENCY ******* (TABLE: ${TABLE})"
		echo "The number of Informix extents for table: ${TABLE}"
		echo "has exceeded the emergency limit of ${EMERGENCY_LIMIT} extents."
		echo "The table has ${EXTENTS} extents and is ${SIZE} ${UNITDESC} in size."
		echo "The FIRST extent size is set to ${FIRST} and the NEXT extent size is set to ${NEXT}"
		echo "The table is in the: $DATABASE database."
		echo
	else
		if [ $EXTENTS -gt $WARNING_LIMIT ]
		then
			echo "******* WARNING ******* (TABLE: ${TABLE})"
			echo "The number of Informix extents for table: ${TABLE}"
			echo "has exceeded the warning limit of ${WARNING_LIMIT} extents."
			echo "The table has ${EXTENTS} extents and is ${SIZE} ${UNITDESC} in size."
			echo "The FIRST extent size is set to ${FIRST} and the NEXT extent size is set to ${NEXT}"
			echo "The table is in the: $DATABASE database."
			echo
		fi
	fi

done < $TMPFILE1

rm -f $TMPFILE1 $TMPFILE2 $TMPFILE3