return to PRS Technologies website
<![CDATA[
#!/usr/bin/ksh
###############################################################################
#
# Module: extent1.sh
# Author: Peter R. Schmidt
# Description: Report on database size for an online engine database
#
# Change Log
#
# Date Name Description.................
# 02/17/98 Peter R. Schmidt Start Program
# 07/07/99 Peter R. Schmidt Updated
# 07/27/99 Peter R. Schmidt Updated
# 08/18/05 Peter R. Schmidt exclude table "TBLSpace"
#
###############################################################################
OUTPUT=extent1.out
TMPFILE=extent1.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|4) OPTION2=$ARG2;;
*) OPTION2=2;;
esac
BG=true
else
tput clear
while true
do
echo "Report on extent sizes for an Informix online engine database"
echo
echo "Specify Order"
echo
echo "1 = Size"
echo "2 = Number of extents"
echo "3 = Table name"
echo "4 = Database/Table name"
echo "0 = Exit this program"
echo
echo "Enter sort sequence desired (0,1,2,3 or 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 "4 = Report in Bytes"
echo "0 = Exit this program"
echo
echo "Enter reporting unit desired (0,1,2,3,4)"
read OPTION2
case $OPTION2 in 0|1|2|3|4) break;; esac
echo
echo "Error - you must enter 0,1,2,3 or 4!"
echo
done
fi
case $OPTION1 in
0) echo "End requested by user"; exit;;
1) SORT="4"; ORDER=desc; ORDERBY=Size;;
2) SORT="3"; ORDER=desc; ORDERBY="Number-of-Extents";;
3) SORT="2"; ORDER=asc; ORDERBY=Tablename;;
4) SORT="1,2"; ORDER=asc; ORDERBY="Database/Tablename";;
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;;
4) UNIT=B; UNITDESC=Bytes;;
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
dbsname,
tabname,
count(*) num_of_extents,
sum(pe_size) total_size
from
systabnames, sysptnext
where
partnum = pe_partnum
and partnum > 99
and dbsname <> "sysmaster"
and tabname <> "TBLSpace"
--and dbsname <> "rootdbs"
group by 1,2
order by $SORT $ORDER;
EOF
echo "unload completed"
XDATE=`date +%D-%T`
echo
echo "Completed - formatting report..."
echo
awk ' \
###############################################################################
#
# Module: extent1.awk
# Author: Peter R. Schmidt
# Description: Report on database size for an online engine database
# Called by: extent1.sh
#
# Expected variables passed at runtime:
#
# pagesize Informix Page Size
# xdate Todays date, format: mm/dd/yy-hh:mm:ss
# orderby Sort Order description
#
# Expected Input: Pipe Delimited file with the following 4 fields
#
# database name
# table name
# number of extents
# number of pages
#
# Change Log
#
# Date Name Description.................
# 02/17/98 Peter R. Schmidt Start Program
# 07/07/99 Peter R. Schmidt Updated
# 07/27/99 Peter R. Schmidt Updated
#
###############################################################################
# INITIALIZE VARIABLES AT BEGINNING
BEGIN {
cntline=5
pageno=1
maxextents=0
}
###############################################################################
# FIRST LINE ONLY
{
if (NR == 1) {
split (xdate,b,"-")
udate=b[1]
utime=b[2]
printf "%s %s Informix Extents Report Page: %d\n", udate, utime, pageno
printf "\n"
printf " Number of Size in\n"
printf "DBS:Table Name Extents %s \n", unitdesc
printf "\n"
}
}
###############################################################################
# ON EVERY LINE
{
split ($1,a,"|")
dbs=a[1]
table=a[2]
num_extents=a[3]
size_P=a[4]
size_K=size_P*pagesize
size_M=size_K/1024
size_B=size_K*1024
tot_P += size_P
tot_K += size_K
tot_M += size_M
tot_B += size_B
if (num_extents > maxextents) {
maxextents = num_extents
maxdbs = dbs
maxtable = table
}
dbs_table = dbs ":" table
if (unit == "M") {
printf "%-30s %3d %10.2f\n", dbs_table, num_extents, size_M
}
if (unit == "K") {
printf "%-30s %3d %10d\n", dbs_table, num_extents, size_K
}
if (unit == "P") {
printf "%-30s %3d %10d\n", dbs_table, num_extents, size_P
}
if (unit == "B") {
printf "%-30s %3d %10d\n", dbs_table, num_extents, size_B
}
cntline++
}
###############################################################################
# TOP OF PAGE
{
if (cntline == 60) {
pageno++
printf "\f\n"
printf "%s %s Informix Extents Report Page: %d\n", udate, utime, pageno
printf "\n"
printf " Number of Size in\n"
printf "DBS:Table Name Extents %s \n", unitdesc
printf "\n"
cntline=5
}
}
###############################################################################
# ON LAST LINE
END {
printf "\n"
if (unit == "M") {
printf "Total Size: %-10.2f Meg\n",tot_M
}
if (unit == "K") {
printf "Total Size: %d K\n",tot_K
}
if (unit == "P") {
printf "Total Size: %d Pages\n",tot_P
}
if (unit == "B") {
printf "Total Size: %d Bytes\n",tot_B
}
printf "Number of tables: %d\n",NR
printf "Highest number of extents: %d (%s:%s)\n", maxextents, maxdbs, maxtable
printf "Using Informix pagesize of: %d K\n", pagesize
printf "Sorted by: %s\n", orderby
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"
################################################################################
]]>