return to PRS Technologies website


last_uptstat.sh
#!/usr/bin/ksh ############################################################################### # # Module: last_uptstat.sh # Author: Peter R. Schmidt # Description: Show when the last time update statistics was run # # Change Log # # Date Name Description................. # 10/13/05 Peter R. Schmidt Start Program # ############################################################################### DATABASE=my_database # <<< PUT YOUR DATABASE NAME HERE !! OUTPUT=last_uptstat.out TMPFILE=last_uptstat.tmp XDATE=`date +%D-%T` MACHINE=`uname -n` BG=false BOLD=`tput smso` NORM=`tput rmso` ############################################################################### if [ $# != 0 ] then ARG1=$1 case $ARG1 in 1|2) OPTION1=$ARG1;; *) OPTION1=2;; esac ARG2=$2 case $ARG2 in 1|2) OPTION2=$ARG2;; *) OPTION2=2;; esac BG=true else tput clear while true do echo "Report on the last time update statistics was run" echo echo "Specify Order" echo echo "1 = Table Name" echo "2 = Date when run" echo "0 = Exit this program" echo echo "Enter sort sequence desired (0,1 or 2)" read OPTION1 case $OPTION1 in 0|1|2) break;; esac echo echo "Error - you must enter 0,1 or 2!" echo done while true do echo echo "Specify Detail or Summary" echo echo "1 = Detailed - 1 line per column and index" echo "2 = Summary - 1 line per table" echo "0 = Exit this program" echo echo "Enter detail or summary as desired (0,1 or 2)" read OPTION2 case $OPTION2 in 0|1|2) break;; esac echo echo "Error - you must enter 0,1 or 2!" echo done fi case $OPTION1 in 0) echo "End requested by user"; exit;; 1) SORT="tabname"; ORDER=asc; ORDERBY=Tablename;; 2) SORT="constructed"; ORDER=asc; ORDERBY="Date Run";; esac ############################################################################### if [ -f $OUTPUT ] then rm -f $OUTPUT fi if [ -f $TMPFILE ] then rm -f $TMPFILE fi ############################################################################### if [ ! -f $TMPFILE ]; then echo echo "Collecting sysdistrib info from the database..." dbaccess << EOF database $DATABASE; unload to '$TMPFILE' delimiter "|" select sysdistrib.tabid, sysdistrib.colno, seqno, constructed, mode, type, tabname, colname from sysdistrib,systables,syscolumns where sysdistrib.tabid = systables.tabid and systables.tabid = syscolumns.tabid and syscolumns.colno = sysdistrib.colno and sysdistrib.tabid > 99 order by $SORT $ORDER, tabname, sysdistrib.colno, seqno EOF fi echo echo "Completed - formatting report..." echo #---------------------------------------------------------------------- awk ' \ BEGIN { FS="|" pre_tabid=0 cnt_tables=0 cnt_rows=0 } { if (NR == 1) { split (xdate,b,"-") udate=b[1] utime=b[2] printf "\n%s %s Informix Update Statistics Report for %s@%s\n", udate, utime, server, machine if (option2 == 1) { print " " print " Table Last Column Sequence Column" print " Name Date Mode Type Number Number Name" print " " } else { print " " print " Table Last" print " Name Date" print " " } } tabid = $1 colno = $2 seqno = $3 constructed = $4 mode = $5 type = $6 tabname = $7 colname = $8 #------------ BEFORE GROUP ----------------------------------------- if (tabid != pre_tabid) { cnt_tables++ if (option2 == 2) { printf "%-20s %-10s\n", tabname, constructed } } #------------ ON EVERY ROW ------------------------------------- # #Table Last Column Sequence Column #Name Date Mode Type Number Number Name #TTTTTTTTTTTTTTTTTTTT mm/dd/yyyy M T cccccc ssssssss Name # cnt_rows++ if (option2 == 1) { printf "%-20s %-10s %s %s %6d %8d %s\n", tabname, constructed, mode, type, colno, seqno, colname } pre_tabid = tabid } END { #-------- ON LAST ROW ------------------------------------------- if (tabid != pre_tabid) { cnt_tables++ if (option2 == 2) { printf "%-20s %-10s\n", tabname, constructed } } print "\n" # Note: you cannot easily count tables unless sorted by tablename # If sorted by date, the same table can appear in many date groups if (option1 == 1) { if (option2 == 1) { printf "%d tables, %d lines\n",cnt_tables,cnt_rows } else { printf "%d tables\n",cnt_tables } } else { if (option2 == 1) { printf "%d lines\n",cnt_rows } } print "\n" } ' xdate=$XDATE machine=$MACHINE unit=$UNIT unitdesc=$UNITDESC server=$INFORMIXSERVER option2=$OPTION2 option1=$OPTION1 $TMPFILE > $OUTPUT rm -f $TMPFILE if [ $BG = false ] then pg $OUTPUT fi echo echo "Note: Output report is in $OUTPUT" ################################################################################