extent3.sh.txt

管理online的shell程序

#!/usr/bin/ksh
###############################################################################
#
#       Module:         extent3.sh
#       Author:         Peter R. Schmidt
#       Description:    Report on table allocation and extents
#
#       Change Log
#
#         Date    Name               Description.................
#       03/27/99  Peter R. Schmidt   Start Program 
#
###############################################################################

OUTPUT=extent3.out
TMPFILE=extent3.tmp
PAGESIZE=2
BG=false
TABLENAME="*"

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

if [ $# != 0 ]
then
        ARG1=$1
        case $ARG1 in 
                1|2|3|4) OPTION1=$ARG1;; 
                *)       OPTION1=1;; 
        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 Informix table allocation and extents"
            	echo
            	echo "1 = All Tables"
            	echo "2 = Report on a single table"
            	echo "0 = Exit this program"
            	echo
            	echo "Enter sort sequence desired (0,1,2)"
            	read OPTION1
   	 
            	case $OPTION1 in 0|1|2) break;; esac
            	echo
            	echo "Error - you must enter 0, 1, or 2!"
            	echo
    	done

	if [ $OPTION1 = 2 ]
	then
		echo
		echo "Enter table name to report on (wildcards OK)"
		read TABLENAME
	fi
    
        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 $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,
        pe_size,
	pe_extnum
from
        systabnames, sysptnext
where
        partnum = pe_partnum
  and   partnum  > 99
  and   dbsname <> "sysmaster"
--and   dbsname <> "rootdbs"
  and   tabname matches "$TABLENAME"
order by 1,2,4
EOF

XDATE=`date +%D-%T`

echo 
echo "unload completed - formatting report..."
echo

awk ' \
###############################################################################
#
#       Module:         extent3.awk
#       Author:         Peter R. Schmidt
#       Description:    Report on table allocation and extents
#
#       Change Log
#
#         Date    Name               Description.................
#       03/27/99  Peter R. Schmidt   Start Program 
#
###############################################################################
#       INITIALIZE VARIABLES AT BEGINNING

BEGIN {
    cntline=5
    pageno=1
    last_table = ""
    cnt_table = 0
    cnt_ext   = 0
    tot_P     = 0
    tot_K     = 0
    tot_M     = 0
    tot_B     = 0
}

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

{
    if (NR == 1) {
        split (xdate,b,"-")
        udate=b[1]
        utime=b[2]

        printf "%s %s          Informix Table Extents Report             Page: %d\n", udate, utime, pageno
        printf "\n" 
        printf "                               Extent        Size in\n"
        printf "DBS:Table Name                 Number         %s    \n", unitdesc
        printf "\n" 
    }
}

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

{
        split ($1,a,"|")
        
        dbs=a[1]
        table=a[2]
        size_P=a[3]
        ext_num=a[4]
        
    	dbs_table = dbs ":" table

	#------------ BEFORE GROUP -----------------------------------------

	if (dbs_table != last_table) {

		#-------- PRINT TABLE SUBTOTALS IF MULTIPLE EXTENTS -------

		if ((last_table != "") && (cnt_table > 1)) {

    			if (unit == "M") {
            			printf "%-30s            %10.2f* Total %d extents\n", last_table, tot_M, cnt_ext
    			}
		
    			if (unit == "K") {
            			printf "%-30s            %10d* Total %d extents\n", last_table, tot_K, cnt_ext
    			}
		
    			if (unit == "P") {
            			printf "%-30s            %10d* Total %d extents\n", last_table, tot_P, cnt_ext
    			}
		
    			if (unit == "B") {
            			printf "%-30s            %10d* Total %d extents\n", last_table, tot_B, cnt_ext
    			}
		
			printf "\n"

        		cntline += 2


		}

		cnt_table = 0
		cnt_ext   = 0
        	tot_P     = 0
        	tot_K     = 0
        	tot_M     = 0
        	tot_B     = 0
	}
	
	#------------ ON EVERY ROW -----------------------------------------

        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

        ftot_P += size_P
        ftot_K += size_K
        ftot_M += size_M
        ftot_B += size_B

	last_table = dbs_table
	cnt_table++
	cnt_ext++
        
    	if (unit == "M") {
            	printf "%-30s  %3d       %10.2f\n", dbs_table, ext_num, size_M
    	}

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

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

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

        cntline++
}

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

{
    if (cntline == 60) {
        pageno++

        printf "\f\n"
        printf "%s %s          Informix Table Extents Report             Page: %d\n", udate, utime, pageno
        printf "\n" 
        printf "                               Extent        Size in\n"
        printf "DBS:Table Name                 Number         %s    \n", unitdesc
        printf "\n" 

        cntline=5
    }
}

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

END {
	#-------- PRINT TABLE SUBTOTALS IF MULTIPLE EXTENTS -------

	if (cnt_table > 1) {

		if (unit == "M") {
			printf "%-30s            %10.2f* Total %d extents\n", last_table, tot_M, cnt_ext
    		}
		
    		if (unit == "K") {
           		printf "%-30s            %10d* Total %d extents\n", last_table, tot_K, cnt_ext
    		}
		
    		if (unit == "P") {
           		printf "%-30s            %10d* Total %d extents\n", last_table, tot_P, cnt_ext
    		}
		
    		if (unit == "B") {
           		printf "%-30s            %10d* Total %d extents\n", last_table, tot_B, cnt_ext
    		}
	}

        printf "\n"

        if (unit == "M") {
                printf "Total Size:                                 %-10.2f Meg\n",ftot_M 
        }
        if (unit == "K") {
                printf "Total Size:                                 %d K\n",ftot_K
        }
        if (unit == "P") {
                printf "Total Size:                                 %d Pages\n",ftot_P
        }
        if (unit == "B") {
                printf "Total Size:                                 %d Bytes\n",ftot_B
        }

	printf "\n"
        printf "Using Informix pagesize of: %d K\n", pagesize
        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"