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

Hi,

Before release V5R2 the optimizer didn't use any statistics, it only used
estimations (i.e. using equal in the where conditions will return 10% of all
rows, using > or < will return 33% of all rows).

Statistics were introduced with the new SQL Query Engine (SQE) on Release
V5R2. 
Starting in OS/400 V5R2, there is a never-ending job called QDBFSTCCOL. This
job is responsible for collecting the SQE column statistics automatically as
a byproduct of query optimization. By default, this is turned on and
collects statistics by reacting to the queries that you run. The system has
the ability to collect column statistics on demand and learn about the data
over time.

You can view a table?s column statistics through iSeries Navigator.
Application programming interfaces (APIs) are also provided to interface
with the column statistics; this allows your applications to view and work
with this information programmatically.

There are even a lot of SQL-users who do not know that statistics are
collected and that there is an statistics manager that interacts with the
query optimizer.

Indexes are maintained as the underlying data changes and with binary radix
tree indexes the tree is always optimized, i.e. it is not necessary to
reorganize indexes or keyed logical files. 

Also you do not have to care about table spaces and buffers, because all is
administered automatically. Most iSeries programmers and SQL-users, never
heard about such objects.
 
This is all possible because of the single storage concept on the
iSeries/System i and because the database is integrated in the operating
system. The database on the iSeries is the easiest to maintain, that's why
most iSeries / System i Jobs do not have any database administrator. The
only thing you have to care about on iSeries/system i is to have your tables
perfectly desinged and a good indexing strategie.

You may check the following links, to get some more information:
Introduction to DB2 for i5/OS query optimization 
http://www-03.ibm.com/servers/enable/site/education/ibp/dada/ 

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) 



-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von PaulMmn
Gesendet: Friday, January 19, 2007 03:10
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL / DB2 and need for "Index Rebuild" or RUNSTATS


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