validate proper update statistics
--0-2144078626-1104853420=:29671
Content-Type: text/plain; charset=us-ascii
I am creating an update statistics script for my server. I believe the best practices is to do the following:
for each table, do:
update statistics low for table TABNAME drop distributions ;update statisics medium for table TABNAME distributions only ;
update statistics high for table TABNAME (colname) ;
The last line is for the leading column of all indexes in that table.
Is this the best way to do it ?
If so, I wrote this little script to generate that output. Does this look right ?
=================================================
#!/bin/ksh
get_tabname() {
dbaccess $DBNAME <<eof
unload to tabs.dat delimiter ' '
select tabname from systables
where tabid>99
and tabtype='T'
and tabname NOT MATCHES '*[0-9]*'
order by 1eof
}
generate_statements() {
while read tabname
do
dbaccess $DBNAME <<eof
unload to cols delimiter ' ' select tabname,c.colname
from systables a, sysindexes b, syscolumns c
where a.tabid = b.tabid
and a.tabid = c.tabid
and a.tabname='$tabname'
and b.part1 = c.colno;eof
echo "update statistics low for table $tabname drop distributions ;" >>$outfile
echo "update statistics medium for table $tabname distributions only ;" >>$outfile
while read tabname column
do
echo "update statistics high for table $tabname ($column) ;" >>$outfile
done <cols
done <tabs.dat
}
#############MAINmain
outfile=upd_statements.dat
> $outfile
get_tabname
generate_statements
rm now$$
rm cols
=======================================
Thank You.
========================
-<<Floyd Wellershaus>>-
Database Administrator
Unix Administrator
email: fwellers@yahoo.com
Work: 703-733-4126
Pager: 703-705-9241
Email Pager: 7037059241@my2way.com
Home: 703-430-0805
Cell: 703-477-6045
========================
--0-2144078626-1104853420=:29671
Content-Type: text/html; charset=us-ascii
<DIV>I am creating an update statistics script for my server. I believe the best practices is to do the following: </DIV>
<DIV> </DIV>
<DIV>for each table, do:</DIV>
<DIV>update statistics low for table TABNAME drop distributions ;</DIV>
<DIV>update statisics medium for table TABNAME distributions only ;</DIV>
<DIV>update statistics high for table TABNAME (colname) ; </DIV>
<DIV> </DIV>
<DIV>The last line is for the leading column of all indexes in that table.</DIV>
<DIV> </DIV>
<DIV>Is this the best way to do it ?</DIV>
<DIV> </DIV>
<DIV>If so, I wrote this little script to generate that output. Does this look right ?</DIV>
<DIV>=================================================</DIV>
<DIV>#!/bin/ksh</DIV>
<DIV>get_tabname() {<BR>dbaccess $DBNAME <<eof<BR>unload to tabs.dat delimiter ' '<BR>select tabname from systables<BR>where tabid>99<BR>and tabtype='T'<BR>and tabname NOT MATCHES '*[0-9]*'<BR>order by 1<BR>eof<BR>}</DIV>
<DIV>generate_statements() {<BR>while read tabname<BR>do<BR> dbaccess $DBNAME <<eof<BR> unload to cols delimiter ' ' select tabname,c.colname<BR> from systables a, sysindexes b, syscolumns c<BR> where a.tabid = b.tabid<BR> and a.tabid = c.tabid<BR> and a.tabname='$tabname'<BR> and b.part1 = c.colno;<BR>eof</DIV>
<DIV> echo "update statistics low for table $tabname drop distributions ;" >>$outfile<BR> echo "update statistics medium for table $tabname distributions only ;" >>$outfile</DIV>
<DIV> while read tabname column<BR> do<BR> echo "update statistics high for table $tabname ($column) ;" >>$outfile<BR> done <cols</DIV>
<DIV>done <tabs.dat<BR>}</DIV>
<DIV> </DIV>
<DIV>#############MAINmain<BR>outfile=upd_statements.dat<BR>> $outfile</DIV>
<DIV>get_tabname<BR>generate_statements</DIV>
<DIV>rm now$$<BR>rm cols</DIV>
<DIV>=======================================</DIV>
<DIV> </DIV>
<DIV>Thank You.</DIV><BR><BR><DIV>
<DIV>========================<BR>-<<Floyd Wellershaus>>-<BR>Database Administrator<BR>Unix Administrator</DIV>
<DIV><BR>email: <A href="mailto:fwellers@yahoo.com">fwellers@yahoo.com</A><BR>Work: 703-733-4126</DIV>
<DIV>Pager: 703-705-9241 </DIV>
<DIV>Email Pager: <A href="mailto:7037059241@my2way.com">7037059241@my2way.com</A></DIV>
<DIV>Home: 703-430-0805</DIV>
<DIV>Cell: 703-477-6045<BR>========================</DIV></DIV>
--0-2144078626-1104853420=:29671--
sending to informix-list
Floyd Wellershaus wrote:
> --0-2144078626-1104853420=:29671
> Content-Type: text/plain; charset=us-ascii
>
> I am creating an update statistics script for my server. I believe the best practices is to do the following:
>
> for each table, do:
> update statistics low for table TABNAME drop distributions ;> update statisics medium for table TABNAME distributions only ;
> update statistics high for table TABNAME (colname) ;>
> The last line is for the leading column of all indexes in that table.
>
> Is this the best way to do it ?
>
> If so, I wrote this little script to generate that output. Does this look right ?
<SNIP>
You're reinventing the wheel, Floyd. Go to the IIUG website (www.iiug.org)
click on Software, then on DBA Utilities and look for my package utils2_ak
it contains, along with several other great utilities, the ESQL/C program
dostats.ec which completely implements the recommended protocol (the outline
above is a simplified version) from the Performance Guide plus many options
to make the process easier to run and less expensive. You can compile
dostats with ESQL/C (cSDK) or with C4GL.
If you have neither there are two good scripts there also that do this one
is updstats.
Art S. Kagel