RE: members in a file

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

The team developing stats collection in 2001 was very concerned with 
the background process - at that time it was something that could 
block other higher-level processes. That was worked on and is solved 
to the best of my knowledge - I left IBM in late 2001.

Stats collection has nothing to do with QMPGDATA - that is 
performance tools - in a new set of clothes! The stats are, as 
Michael Cain pointed out, limited to 8-12K per column for which the 
collection was run. And the data is part of the file object. Also, 
only on the PF, although you probably figured that out already.

Collection is NOT constant, unlike management collection of 
performance data. At least when you have PM running. The stats are 
collected again when something called the Stats Manager decides they 
should be - based on certain amounts of change in the table - like 
number of rows. Michael Cain's article and the redbook probably have 
more information. But there is a concept of "stale" data - and then 
the collection needs to be run again for a particular column in a 
particular table.

HTH
Vern

At 07:29 AM 12/31/2007, you wrote:

> Joe,
>
> I too have concerns about statistics.
> In other areas, QMPGDATA is rather significant.
> Years ago I had a special purpose machine that I was rather loath to
> upgrade past V2R3 because of the size of QADB* files that V3R1 added. And,
> you know me, always love the bleeding edge.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 12/29/2007 11:34 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
> cc
>
> Subject
> RE: members in a file
>
>
>
>
>
>
> > From: BirgittaHauser
> >
> > >>What happens if you stop and start collection of SQL Statistics?
> >
> > Statistics are used by the SQE optimizer to determine the optimal index.
> > The statistics contain information about how much rows in a table meet
> the
> > available key values in an index.
> > For example if you want to select all order from a specified client and
> > around 80% of all orders are from this client, the optimizer will favor
> a
> > table scan instead of using an index. If an other client will be
> selected
> > an index can be used.
>
> > (...snip...)
>
> > BTW the CQE optimizer can only use estimates. For example for an =
> > specified
> > in a where condition 10% of the rows in a table are expected/estimated.
>
> And it is this collection of statistics that makes SQL so powerful.
> Without
> it, a good programmer with knowledge of his database can still use ISAM
> techniques to provide better performance than SQL because he will in
> effect
> be able to make the decisions that the CQE cannot.
>
> As statistics get better, SQL matches and then surpasses the low-level
> programmer because its information is always correct while the programmer
> is
> relying on his/her knowledge of the system, which may be out of date.
> Similarly, though, if you stop the collection of statistics, the SQL
> statistics will become stale and it will begin to make poorer decisions.
>
> And of course, statistics don't help as much in completely ad hoc
> situations, because the SQL engine will have no statistics on queries that
> have never been done.  But in general, SQL queries will just get better
> and
> better.
>
> One thing I do worry about is exactly how much space is taken up to store
> the information required to make the queries faster.  I'd love to see the
> statistics on what percentage of disk space is required to store the
> statistics for a file.  If it's something on the order of a tenth of a
> percent, it's not a big deal.  If it's 10% or more, it's a big deal!
>
> Joe