Re: SQL / DB2 and need for "Index Rebuild" or RUNSTATS

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Hi Paul

Here is part of an article by Kent Milligan from 2002 - statistics 
collection was put into the iSeries version of DB2 in 2001 - I was 
there on the database performance team as a contractor.

"For instance, DB2 UDB for iSeries doesn't provide a RUNSTATS utility 
for optimizer statistics because its database manager keeps these 
statistics current at all times."

The link to the whole thing is

http://www-128.ibm.com/developerworks/db2/library/techarticle/0204milligan/0204milligan.html

Index rebuilds - they go on all the time - unless you delay them with 
an attribute on the file. And there'd not be much benefit - as I 
understand it, AIX flavors can have different ways of building 
indexes, and it matters what you choose. We just don't have that 
problem on iSeries that I know of.

There are some statistics controls now, I think, but not RUNSTATS. 
There's the system value QDBFSTCCOL - and it says to look in 
Information Center for more on that system value and related items. 
There is automatic and user-requested collection of statistics.

I suppose one could rebuild indexes - delete logicals and recreate - 
but you take great risk there - shared access paths could be messed 
up - resulting in overuse of disk space and slower response. The 
other thing I can think of if RGZPFM over a commonly used logical. 
Not sure it would matter that much here.

HTH
Vern

At 08:10 PM 1/18/2007, you wrote:

> Ladies and Gentlemen--
>
> We have an application that does most of its work with SQL against
> i5/iSeries/AS400/S38 databases.
>
> We have had some issues, and the recommendation is to run a weekly
> database maintenance consisting of  "rebuilding indexes" and running
> RUNSTATS against the database.  The goal is to make things easier for
> the Optimizer.
>
> We ran the vendor's Database Revitalizer, and it said that index
> rebuilds aren't needed on this machine; it did show RUNSTATS
> statements (but I'm not convinced they actually did anything).
>
> Everything I'm aware of, and the literature I've been able to dig up,
> indicates that rebuilding indexes is not required on the i5/etc.
> machines because the indexes are -always- kept up to date (unless you
> specify maint(*dly) or something).
>
> And I have found some not-quite-definitive statements that indicate
> that RUNSTATS doesn't work (and isn't required) on the i5/etc.  I
> entered STRSQL, and RUNSTATS is an unknown keyword.  I don't know if
> QSHELL has a RUNSTATS hidden away somewhere.
>
> First, comments are welcome; second, I'm looking for links to IBM or
> other documents that definitely say "RUNSTATS isn't needed," and
> neither are routine index rebuilds.
>
> Thanks for your assistance.
>
> --Paul E Musselman
> PaulMmn@xxxxxxxxxxxxxxxxxxxx
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.