每天自动报告数据库中表的空间使用情况

將此shell寫到crontab中每天執行一下,就可以知道informix中top 60的表的使用情況.
當然也 可以根據需要進行添加減少.

#!/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"

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