return to PRS Technologies website
#!/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"
################################################################################