分类: DB2/Informix
2005-10-19 13:51:47
#!/usr/bin/ksh
INFORMIXSERVER=on_shm01;export INFORMIXSERVER
ONCONFIG=onconfig.on_01;export ONCONFIG
DB_LOCALE=zh_tw.big5;export DB_LOCALE
SERVER_LOCALE=zh_tw.big5;export SERVER_LOCALE
CLIENT_LOCALE=zh_tw.big5;export CLIENT_LOCALE
LANG=zh_tw.big5;export LANG
echo "INFORMIXSERVER=$INFORMIXSERVER"
echo "ONCONFIG=$ONCONFIG"
INFORMIXDIR=/u1/informix;export INFORMIXDIR
PATH=$INFORMIXDIR/bin:$PATH;export PATH
DBDATE=Y2MD/;export DBDATE
DBCENTURY=C;export DBCENTURY
DBDELIMITER='^A';export DBDELIMITER
LD_LIBRARY_PATH=/u1/informix/lib:/u1/informix/lib/esql;export LD_LIBRARY_PATH
LIBPATH=$LD_LIBRARY_PATH:/usr/local/lib;export LIBPATH
PATH=/usr/local/bin:$PATH;export PATH
INFORMIXC="gcc -lsupc++ -maix64";export INFORMIXC
STMT_CACHE=1;export STMT_CACHE
TERMCAP=$INFORMIXDIR/etc/termcap;export TERMCAP
set -o vi
PS1='$PWD> ';export PS1
CDPATH=.:$INFORMIXDIR;export CDPATH
###############################################################################
DBACCESS=/u1/informix/bin/dbaccess
MAIL_USER=mymailaddress@domain.com
CC_MAIL_LIST="ccmail_address@domain.com"
MAIL_TITLE="The Table Size TOP 60"
OUTPUT=/tmp/r_table_size.out
TMPFILE=/tmp/r_table_size.tmp
PAGESIZE=4
BG=false
SORT="4"
ORDER=desc
ORDERBY=Size
UNIT=M
UNITDESC=Mbytes
###############################################################################
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 first 60
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 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 '
# 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
", udate, utime,
pageno
printf "
"
printf " Number of Size in
"
printf "DBS:Table Name Extents %s
", unitdesc
printf "
"
}
}
# 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
tot_M += size_M
if (num_extents > maxextents) {
maxextents = num_extents
maxdbs = dbs
maxtable = table
}
dbs_table = dbs ":" table
printf "%-30s %3d %10.2f
", dbs_table, num_extents, size_M
cntline++
}
###############################################################################
# TOP OF PAGE
{
if (cntline == 80) {
pageno++
printf "f
"
printf "%s %s Informix Extents Report Page: %d
", udate, utime,
pageno
printf "
"
printf " Number of Size in
"
printf "DBS:Table Name Extents %s
", unitdesc
printf "
"
cntline=5
}
}
###############################################################################
# ON LAST LINE
END {
printf "
"
printf "Total Size: %-10.2f Meg
",tot_M
printf "Number of tables: %d
",NR
printf "Highest number of extents: %d (%s:%s)
", maxextents, maxdbs, maxtable
printf "Using Informix pagesize of: %d K
", pagesize
printf "Sorted by: %s
", orderby
printf "
"
}
###############################################################################
# END OF AWK SCRIPT
'
pagesize=$PAGESIZE
xdate=$XDATE
orderby=$ORDERBY
unit=$UNIT
unitdesc=$UNITDESC
$TMPFILE > $OUTPUT
###############################################################################
mail -s "$MAIL_TITLE $XDATE" -c $CC_MAIL_LIST $MAIL_USER < $OUTPUT
rm -f $TMPFILE
echo
echo "Note: Output report is in $OUTPUT"
################################################################################