Cardinality and distribution statistics DB2 v8.1 z/OS

William Gannon

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

The DB2L code place would be a great place to post this ...

But if you don't wish to do so I would like a copy myself

William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Hecq
Sent: Friday, December 26, 2008 1:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Nicola,
I do not know how frequently you run Runstats, but you should be
generating cardinality and distribution stats for all index columns.
This is done by specifying the Table (owner.tb_name) Column (coln1,
coln2...) clause in your Runstats.

I have a REXX that will generate the Runstats statement for a specific
tablespace. If you are interested, contact me off-list.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nicola Nur
Sent: Tuesday, December 23, 2008 5:01 PM
To: [login to unmask email]
Subject: [DB2-L] 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.

If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.

______________________________________________________________________

* 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
----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail
address released in response to a public records request, do not
send electronic mail to this entity. Instead, contact this office
by phone or in writing.

______________________________________________________________________

* 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

Shery hepp

Re: Cardinality and distribution statistics DB2 v8.1 z/OS
(in response to William Gannon)
Hi Roger- I agree with Bill in that the list is a great place to share
code. However if you prefer sharing on an individual basis instead
please include me for a copy.

Thanks- Shery Hepp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bill Gannon
Sent: Monday, January 05, 2009 5:32 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Hi Roger !

The DB2L code place would be a great place to post this ...

But if you don't wish to do so I would like a copy myself

William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Hecq
Sent: Friday, December 26, 2008 1:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Nicola,
I do not know how frequently you run Runstats, but you should be
generating cardinality and distribution stats for all index columns.
This is done by specifying the Table (owner.tb_name) Column (coln1,
coln2...) clause in your Runstats.

I have a REXX that will generate the Runstats statement for a specific
tablespace. If you are interested, contact me off-list.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nicola Nur
Sent: Tuesday, December 23, 2008 5:01 PM
To: [login to unmask email]
Subject: [DB2-L] 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.

If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.

______________________________________________________________________

* 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
----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail
address released in response to a public records request, do not
send electronic mail to this entity. Instead, contact this office
by phone or in writing.

______________________________________________________________________

* 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

______________________________________________________________________

* 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

Wayne Stevens

Re: Cardinality and distribution statistics DB2 v8.1 z/OS
(in response to Shery hepp)
If you don't put it in code share .. then send me a copy too.

Wayne Stevens
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hepp Shery C
Sent: Monday, January 05, 2009 10:36 AM
To: [login to unmask email]
Subject: Re: Cardinality and distribution statistics DB2 v8.1 z/OS

Hi Roger- I agree with Bill in that the list is a great place to share
code. However if you prefer sharing on an individual basis instead
please include me for a copy.

Thanks- Shery Hepp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bill Gannon
Sent: Monday, January 05, 2009 5:32 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Hi Roger !

The DB2L code place would be a great place to post this ...

But if you don't wish to do so I would like a copy myself

William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Hecq
Sent: Friday, December 26, 2008 1:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Nicola,
I do not know how frequently you run Runstats, but you should be
generating cardinality and distribution stats for all index columns.
This is done by specifying the Table (owner.tb_name) Column (coln1,
coln2...) clause in your Runstats.

I have a REXX that will generate the Runstats statement for a specific
tablespace. If you are interested, contact me off-list.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nicola Nur
Sent: Tuesday, December 23, 2008 5:01 PM
To: [login to unmask email]
Subject: [DB2-L] 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.

If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.

______________________________________________________________________

* 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
----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail
address released in response to a public records request, do not
send electronic mail to this entity. Instead, contact this office
by phone or in writing.

______________________________________________________________________

* 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

______________________________________________________________________

* 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

______________________________________________________________________

* 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

Roger Hecq

Re: Cardinality and distribution statistics DB2 v8.1 z/OS
(in response to Wayne Stevens)
I agree that the code place is the way to go. I am painfully
transitioning from vacation mode to work mode. I hope to get to it
tomorrow.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hepp Shery C
Sent: Monday, January 05, 2009 11:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Hi Roger- I agree with Bill in that the list is a great place to share
code. However if you prefer sharing on an individual basis instead
please include me for a copy.

Thanks- Shery Hepp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bill Gannon
Sent: Monday, January 05, 2009 5:32 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Hi Roger !

The DB2L code place would be a great place to post this ...

But if you don't wish to do so I would like a copy myself

William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Hecq
Sent: Friday, December 26, 2008 1:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Nicola,
I do not know how frequently you run Runstats, but you should be
generating cardinality and distribution stats for all index columns.
This is done by specifying the Table (owner.tb_name) Column (coln1,
coln2...) clause in your Runstats.

I have a REXX that will generate the Runstats statement for a specific
tablespace. If you are interested, contact me off-list.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nicola Nur
Sent: Tuesday, December 23, 2008 5:01 PM
To: [login to unmask email]
Subject: [DB2-L] 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and
delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.

If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

______________________________________________________________________

* 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
----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail address
released in response to a public records request, do not send electronic
mail to this entity. Instead, contact this office
by phone or in writing.

______________________________________________________________________

* 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

______________________________________________________________________

* 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.

______________________________________________________________________

* 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

David Seibert

Re: Cardinality and distribution statistics DB2 v8.1 z/OS
(in response to Roger Hecq)
To work mode, BY TOMORROW already?

Dave


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Hecq
Sent: Monday, January 05, 2009 2:10 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

I agree that the code place is the way to go. I am painfully
transitioning from vacation mode to work mode. I hope to get to it
tomorrow.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hepp Shery C
Sent: Monday, January 05, 2009 11:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Hi Roger- I agree with Bill in that the list is a great place to share
code. However if you prefer sharing on an individual basis instead
please include me for a copy.

Thanks- Shery Hepp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bill Gannon
Sent: Monday, January 05, 2009 5:32 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Hi Roger !

The DB2L code place would be a great place to post this ...

But if you don't wish to do so I would like a copy myself

William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Hecq
Sent: Friday, December 26, 2008 1:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Nicola,
I do not know how frequently you run Runstats, but you should be
generating cardinality and distribution stats for all index columns.
This is done by specifying the Table (owner.tb_name) Column (coln1,
coln2...) clause in your Runstats.

I have a REXX that will generate the Runstats statement for a specific
tablespace. If you are interested, contact me off-list.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nicola Nur
Sent: Tuesday, December 23, 2008 5:01 PM
To: [login to unmask email]
Subject: [DB2-L] 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and
delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.

If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

______________________________________________________________________

* 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
----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail address
released in response to a public records request, do not send electronic
mail to this entity. Instead, contact this office
by phone or in writing.

______________________________________________________________________

* 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

______________________________________________________________________

* 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and
delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.

If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

______________________________________________________________________

* 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

______________________________________________________________________

* 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

Roger Hecq

Re: Cardinality and distribution statistics DB2 v8.1 z/OS
(in response to David Seibert)
No promises. After 2 weeks in the Florida Keys, it is difficult to
adjust to the real world, especially if it is in the cold north.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Monday, January 05, 2009 2:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

To work mode, BY TOMORROW already?

Dave


The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Hecq
Sent: Monday, January 05, 2009 2:10 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

I agree that the code place is the way to go. I am painfully
transitioning from vacation mode to work mode. I hope to get to it
tomorrow.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Hepp Shery C
Sent: Monday, January 05, 2009 11:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Hi Roger- I agree with Bill in that the list is a great place to share
code. However if you prefer sharing on an individual basis instead
please include me for a copy.

Thanks- Shery Hepp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Bill Gannon
Sent: Monday, January 05, 2009 5:32 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Hi Roger !

The DB2L code place would be a great place to post this ...

But if you don't wish to do so I would like a copy myself

William B. Gannon
- IBM Certified Solutions Expert
- IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Hecq
Sent: Friday, December 26, 2008 1:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1
z/OS

Nicola,
I do not know how frequently you run Runstats, but you should be
generating cardinality and distribution stats for all index columns.
This is done by specifying the Table (owner.tb_name) Column (coln1,
coln2...) clause in your Runstats.

I have a REXX that will generate the Runstats statement for a specific
tablespace. If you are interested, contact me off-list.


Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nicola Nur
Sent: Tuesday, December 23, 2008 5:01 PM
To: [login to unmask email]
Subject: [DB2-L] 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and
delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.

If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

______________________________________________________________________

* 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
----------------------------------------- Under Florida law, e-mail
addresses are public records. If you do not want your e-mail address
released in response to a public records request, do not send electronic
mail to this entity. Instead, contact this office
by phone or in writing.

______________________________________________________________________

* 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

______________________________________________________________________

* 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and
delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.

If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.

______________________________________________________________________

* 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

______________________________________________________________________

* 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
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.

______________________________________________________________________

* 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