RE: members in a file

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