Cardinality and distribution statistics DB2 v8.1 z/OS

Nick Nur

Cardinality and distribution statistics DB2 v8.1 z/OS
Hi colleagues

I have a problem and hope to get some advice and insight from you.
To improve the access path of SQL statements in DB2 v8.1 I have been using Visual Explain Analyze button to generate the cardinality and distribution stats. That is a very time consuming process. My boss asked why can't we
productionize collecting these statistics?

The following quote from the Red Book 'Every thing you want to know about v8' states: 'Cardinality and distribution statistics are collected only on the columns explicitly specified. Cardinality and distribution statistics are not collected if you specify COLUMN ALL'.

Our RUNSTAT jobs specify COLUMN ALL.

That poses a problem for me (constraints of time and lots of SQL stmnts) because I have to take every SQL statement and generate the cardinality and distribution stats and then productionize it.

Can any body help me with an idea or a solution? I do not know even if I made myself clear for you. Thanks and grateful as always.

post script: I wish all of you a Merry Christmas and a Happy New Year.


Nick Nur






__________________________________________________________________
Get the name you've always wanted @ymail.com or @rocketmail.com! Go to http://ca.promos.yahoo.com/jacko/

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Peter Vanroose

Re: Cardinality and distribution statistics DB2 v8.1 z/OS
(in response to Nick Nur)
Instead of going through all SQL, I would go through all tables.
Or actually, all columns of all tables.
Well, of course just the ones where you expect benefits.
That is: where you expect (high) correlation between pairs of columns.
Do you have tables where some logical piece of info is spread over two or
more columns? If so, do you expect certain values in one column to be more
prominently present when the other column has a certain value?
Examples *could* be:
- postal code (zip code) and city name
- date and order number
- product name and product description
- .......
If the correlation is high enough to be a "100% correlation", that column
combination should have been put in a separate table because of
normalisation. So also look for denormalised design: those columns are
candidates for correlation.

Then I would just go for putting the column combinations you came up with
into your runstats, and forget about the potential others.

-- Peter Vanroose
ABIS Training & Consulting.


=
On Tue, 23 Dec 2008 14:01:24 -0800, Nicola Nur wrote:

>I have a problem and hope to get some advice and insight from you.
>To improve the access path of SQL statements in DB2 v8.1 I have been using
Visual Explain Analyze button to generate the cardinality and distribution
stats. That is a very time consuming process. My boss asked why can't we
>productionize collecting these statistics?
>
>The following quote from the Red Book 'Every thing you want to know about
v8' states: 'Cardinality and distribution statistics are collected only on
the columns explicitly specified. Cardinality and distribution statistics
are not collected if you specify COLUMN ALL'.
>
>Our RUNSTAT jobs specify COLUMN ALL.
>
>That poses a problem for me (constraints of time and lots of SQL stmnts)
because I have to take every SQL statement and generate the cardinality and
distribution stats and then productionize it.
>
>Can any body help me with an idea or a solution? I do not know even if I
made myself clear for you. Thanks and grateful as always.

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html