Re: Force Statistics Collection DB2/400?

The business data is being copied to tape from V5R4 and restored to V6R1.
It is then run through an ERP vendor conversion program to move from one
level to another. The box is an i520 currently split into two LPAR's.
Memory and processor are identical between the two...although I/O subsystems
are very different. Yes, I'm sure the access plans may also been
recalculated for just about everything. The reason my eye was on the
statistics was because disk use (as a proporation) was high (as opposed to
CPU) and the QDBFSTCCOL process seems to be the culprit.

Either way...seems unavoidable and eventually works itself out. I'll just
make sure we do stress testing after all CRP's have gone through a couple of
rounds, instead of making it a part of the CRP's.

Thanks

Ryan

"DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx> wrote in
message news:29DE35517ECABA48B733FB0FCD09709E1E9E94@xxxxxxxxxxxxxxxxxxxxxx

Ryan,

Most likely, this is an unavoidable cost of running a "New database" and
applications. This is not really so much related to the statistics,
rather the "access plan" which is the template that DB2 uses to process
the specific queries in you application. Access plans are built on the
first access, and rebuilt whenever DB2 realizes that the environment
looks different (new indexes, key cardinality, etc)... In an embedded
SQL application, the access plan is stored in the PGM object.

Honestly, I think there may be other issues at work. I would recommend
you look at advised indexes in iNavigator (or whatever its called now).
What model IBM i are you running?

-Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On

Behalf Of Ryan Hunt

Sent: Monday, April 19, 2010 4:01 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Force Statistics Collection DB2/400?

Yes, I think most databases these days create and manage statistics
collections (I'm exlcuding things like MySQL, Postgress, etc. - haven't
studied those).

In my particular situation, statistics appear to be lost or deemed
irrelevant based on certain system/data migration activities. Within 10
minutes of users logging in QDBFSTCCOL kicks on and pegs the disks -
immediately resulting in users complaining of a slow system. Certainly,
being able to generate a base set of stats on large files using a sample
selection the night before is a nice feature - indepedant of whether you
would ever do it on a stable fully migrated system.

I believe this is one of the reasons other RDBMS's have this type of
feature.

Just my two cents.

"Musselman, Paul" <pmusselman@xxxxxxxxxxxxxxxx>

wrote

in message

news:2CEA9B835E89634AB7FEB5E4251ABF1C18ACD16E@xxxxxxxxxxxxxxxxxxxxxxxxxx

com...

!!! I went around and around with a software vendor a few years back--
they insisted that I contact my "database administrator" and
"reorganize/optimize" the database to optimize performance and

eliminate

a problem we were having.

This was a Java-based application using DB2/400 to store its data.

They

insisted we needed to reorganize the database to optimize the trees
because You Just Have To Do These Things With Any Database!

It was almost impossible to convince them (they didn't believe) that

we

didn't need to those things; that the operating system took care of

such

things -automatically-!

The only thing that comes close to what a traditional database
administrator does (imho) is to use the iSeries Navigator and see if

the

Index Advisor recommends any new indices for your files.

Paul E Musselman
--
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.

--
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.



--
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.