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