Re: What's in my buffers script
Thanks, that is very useful. Gave me a list of tables that need better indexes. :) But... sysbufhdr.pagenum doesn't exist on 9.4. Has it been renamed to offset?
On Fri, 22 Aug 2003 11:18:08 -0400, Kristofer Andersson wrote:
Yup, my 9.4 wasn't up when I tested the script. New version with an
option to filter out the TBLSpace entries. Also now works for all
versions of IDS through 94. and with either dbaccess or sqlcmd.
New file below:
Art S. Kagel
> Thanks, that is very useful. Gave me a list of tables that need better
> indexes. :)
>
> But... sysbufhdr.pagenum doesn't exist on 9.4. Has it been renamed to
> offset?
<FILE: whats_in_buffers>
#!/usr/bin/ksh
if [[ "$1" = "-h" ]]; then
echo "Usage: $0 [-t|-h|-?]"
echo ""
echo " -H Usage"
echo " -t Only report tables filter out dbspace TBLSpace overhead pages"
echo ""
exit 0
fi
which nawk 2>&1 |fgrep "no nawk in" >/dev/null
if [[ $? = 0 ]]; then
nawk=0;
else
nawk=1;
AWK=nawk;
fi
if [[ $nawk -eq 0 ]]; then
which gawk 2>&1 |fgrep "no gawk in" >/dev/null
if [[ $? = 0 ]]; then
gawk=0;
else
gawk=1;
AWK=gawk;
fi
fi
if [[ $nawk -eq 0 && $gawk -eq 0 ]]; then
echo "nawk or gawk needed to run this script. Try converting it to perl"
echo "with a2p if you do not have either."
exit 1
fi
onstat -|fgrep 9.40. >/dev/null 2>&1
if [[ $? -eq 0 ]]; then
Vers94=1
MATCH='bh.chunk = se.chunk and bh.offset between se.offset and (se.offset + se.size - 1)'
else
Vers94=0
MATCH='bh.pagenum between se.start and (se.start + se.size - 1)'
fi
FIFO=${0}.p
mkfifo $FIFO
touch $FIFO
if [[ $1 = -t ]]; then
TABFILT=' and tabname != "TBLSpace" '
else
TABFILE=''
fi
which sqlcmd 2>&1 |fgrep "no sqlcmd in" >/dev/null
if [[ $? = 0 ]]; then
CMD='dbaccess - '
UNLD="unload to $FIFO"
HDRS="select ' dbname','tabname', 'table_count','database_count' from systables where tabid = 1 UNION ALL"
else
CMD='sqlcmd -H '
UNLD="output $FIFO;"
HDRS=''
fi
cat $FIFO |$AWK -F\\| '$1~"^( )*dbsname"{
gsub("_"," ",$0);
printf "\\n\\n%-18s %-18s %-10s %-14s\\n", toupper($1), toupper($2), toupper($3), toupper($4);
printf "%-18s %-18s %-10s %-14s\\n", "------------------", "------------------", "-----------", "--------------";
next;
}
$3==" "{
printf "%-18s %-18s %10s %14d\\n", $1, " ", " ", $4;
next;
}
$4==" "{
printf "%-18s %-18s %10d\\n", " ", $2, $3;
next;
}
{
gsub("_"," ",$0);
printf "\\n\\n%-18s %-18s %10s %14s\\n", toupper($1), toupper($2), toupper($3), toupper($4);
printf "%-18s %-18s %-10s %-14s\\n", "------------------", "------------------", "-----------", "--------------";
next;
}
' &
$CMD 2>/dev/null <<EOF
database sysmaster;
set isolation repeatable read;
select dbsname, tabname, count(*) table_count
from sysextents se, sysbufhdr bh
where $MATCH$TABFILT
group by 1, 2
into temp whats_in_buffer;
$UNLD
$HDRS
select dbsname, tabname, table_count || " " table_count, " " database_count
from whats_in_buffer
UNION ALL
select dbsname, " TOTAL:", " " table_count2, sum(table_count) ||' '
from whats_in_buffer
group by 1, 2, 3
order by 1, 2;EOF
rm $FIFO
<END FILE>