Antwort: [DB2-L] Runstats in DB2 V8 z/os

Roy Boxwell

Antwort: [DB2-L] Runstats in DB2 V8 z/os

to help you find the "missing stats" that V8 craves you can do a google on "Statistics HealthCheck" download, install and run...its all fast and free (really

would I lie to DB2-L ???)

 

Roy "still not in sales" Boxwell
Jorg Lueke <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
14.12.2007 03:20 PM GMT
Bitte antworten an DB2 Database Discussion list at IDUG

An: [login to unmask email]
Kopie:
Blindkopie:
Thema: [DB2-L] Runstats in DB2 V8 z/os
 

We've now had several processes where the default setting in V8 resulted in
worse access paths.  One recent application example shows the CPU going
from just under two hours to well over 10 hours,.  In all cases when we run
the correct statistics the performance goes back to V7 levels or improves.
However, I am beginning to suspect that the default stats gathered during a
load are doing more harm than good at this point.  Since the needed runstats
rly heavily on coldist for multiple columns, in some case non-indexed columns,
and the load update doesn't impact these we may need to run corrective
runstats after the loads.  What I'm currently looking at is the CA load and
these workloads are Bi/warehousing large parallel queries.

There's also issues with the physical data design in that the notorious part_nbr
is being used to partition and cluster rather than the date field that is used in
almost all the queries.

The IDUG DB2-L Listserv is only part of your membership in IDUG.  DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab.  While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.  If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

Jorg Lueke

Re: Antwort: [DB2-L] Runstats in DB2 V8 z/os
(in response to Roy Boxwell)
The OSC statistics advisor does a pretty good job

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms