Differences in Statistics Taken in Runstats VS Reorg

Barbara Nigh

Differences in Statistics Taken in Runstats VS Reorg
Can someone please tell me if the statistics we are collecting during an
on-line conditional REORG are the same as the ones we are collecting when
we execute the RUNSTATS utility prior to running the
conditional REORG? Listed below are the parameters we are using for each
utility:

RUNSTATS Utility -- TABLE INDEX (ALL) KEYCARD UPDATE ALL
REORG Utility -- STATISTICS TABLE (ALL) INDEX (ALL KEYCARD) UPDATE ALL


Barbara J Nigh
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901
______________________________________________________

Phone. 831 754 1400 2048
Fax. 831-759-7073
Mobile. 831-776-6194 Pager [login to unmask email]
Email. [login to unmask email]
Internet. http://www.hsbc.com
______________________________________________________



-----------------------------------------
******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Simpson

Re: Differences in Statistics Taken in Runstats VS Reorg
(in response to Barbara Nigh)
Since you have TABLE(ALL) in the REORG you are gathering cardinality
stats on every column in the table. Since you have left out the (ALL)
on the RUNSTATS version, you are probably only collecting those stats on
the leading columns of indexes.




________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]

For more information about Themis, visit www.themisinc.com
< http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
[login to unmask email]
Sent: Thursday, January 14, 2010 1:31 PM
To: [login to unmask email]
Subject: [DB2-L] Differences in Statistics Taken in Runstats VS Reorg




Can someone please tell me if the statistics we are collecting during an
on-line conditional REORG are the same as the ones we are collecting
when we execute the RUNSTATS utility prior to running the
conditional REORG? Listed below are the parameters we are using for
each utility:

RUNSTATS Utility -- TABLE INDEX (ALL) KEYCARD UPDATE ALL
REORG Utility -- STATISTICS TABLE (ALL) INDEX (ALL KEYCARD) UPDATE ALL


Barbara J Nigh
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901

______________________________________________________

Phone. 831 754 1400 2048
Fax. 831-759-7073
Mobile. 831-776-6194 Pager [login to unmask email]
Email. [login to unmask email]
<mailto:[login to unmask email]>
Internet. http://www.hsbc.com < http://www.hsbc.com/ >

______________________________________________________



________________________________

******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Barbara Nigh

Re: Differences in Statistics Taken in Runstats VS Reorg
(in response to David Simpson)
David,

Would the REORG statistics then override the RUNSTATS statistics and vice
versa? The access path seemed to change when we reran the RUNSTATS
utility after the REORG.

Barbara J Nigh
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901
______________________________________________________

Phone. 831 754 1400 2048
Fax. 831-759-7073
Mobile. 831-776-6194 Pager [login to unmask email]
Email. [login to unmask email]
Internet. http://www.hsbc.com
______________________________________________________




From:
David Simpson <[login to unmask email]>
To:
[login to unmask email]
Date:
01/14/2010 12:07 PM
Subject:
Re: [DB2-L] Differences in Statistics Taken in Runstats VS Reorg
Sent by:
IDUG DB2-L <[login to unmask email]>



Since you have TABLE(ALL) in the REORG you are gathering cardinality stats
on every column in the table. Since you have left out the (ALL) on the
RUNSTATS version, you are probably only collecting those stats on the
leading columns of indexes.

______________________________________________________________________________

David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]
For more information about Themis, visit www.themisinc.com

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
[login to unmask email]
Sent: Thursday, January 14, 2010 1:31 PM
To: [login to unmask email]
Subject: [DB2-L] Differences in Statistics Taken in Runstats VS Reorg


Can someone please tell me if the statistics we are collecting during an
on-line conditional REORG are the same as the ones we are collecting when
we execute the RUNSTATS utility prior to running the
conditional REORG? Listed below are the parameters we are using for each
utility:

RUNSTATS Utility -- TABLE INDEX (ALL) KEYCARD UPDATE ALL
REORG Utility -- STATISTICS TABLE (ALL) INDEX (ALL KEYCARD) UPDATE ALL


Barbara J Nigh
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901

______________________________________________________
Phone. 831 754 1400 2048
Fax. 831-759-7073
Mobile. 831-776-6194 Pager [login to unmask email]
Email. [login to unmask email]
Internet. http://www.hsbc.com
______________________________________________________


******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

******************************************************************
This message originated from the Internet. Its originator may or
may not be who they claim to be and the information contained in
the message and any attachments may or may not be accurate.
******************************************************************



-----------------------------------------
******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Simpson

Re: Differences in Statistics Taken in Runstats VS Reorg
(in response to Barbara Nigh)
I just dug a little deeper on this and found that (ALL) seems to be the
default when you specify TABLE with no other options, so these would
seem to be functionally equivalent.



Keep in mind that access paths could change ANY time you rerun stats and
the data has changed to some degree. Are you seeing access paths
flip-flop when you do the reorg and later runstats?




________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]

For more information about Themis, visit www.themisinc.com
< http://www.themisinc.com/ >



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
[login to unmask email]
Sent: Thursday, January 14, 2010 2:56 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Differences in Statistics Taken in Runstats VS
Reorg




David,

Would the REORG statistics then override the RUNSTATS statistics and
vice versa? The access path seemed to change when we reran the RUNSTATS
utility after the REORG.

Barbara J Nigh
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901

______________________________________________________

Phone. 831 754 1400 2048
Fax. 831-759-7073
Mobile. 831-776-6194 Pager [login to unmask email]
Email. [login to unmask email]
<mailto:[login to unmask email]>
Internet. http://www.hsbc.com < http://www.hsbc.com/ >

______________________________________________________






From:

David Simpson <[login to unmask email]>

To:

[login to unmask email]

Date:

01/14/2010 12:07 PM

Subject:

Re: [DB2-L] Differences in Statistics Taken in Runstats VS Reorg

Sent by:

IDUG DB2-L <[login to unmask email]>



________________________________




Since you have TABLE(ALL) in the REORG you are gathering cardinality
stats on every column in the table. Since you have left out the (ALL)
on the RUNSTATS version, you are probably only collecting those stats on
the leading columns of indexes.

________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email] <mailto:[login to unmask email]>
For more information about Themis, visit www.themisinc.com
< http://www.themisinc.com/ >

From: IDUG DB2-L [mailto:[login to unmask email]
<mailto:[login to unmask email]> ] On Behalf Of [login to unmask email]
Sent: Thursday, January 14, 2010 1:31 PM
To: [login to unmask email]
Subject: [DB2-L] Differences in Statistics Taken in Runstats VS Reorg


Can someone please tell me if the statistics we are collecting during an
on-line conditional REORG are the same as the ones we are collecting
when we execute the RUNSTATS utility prior to running the
conditional REORG? Listed below are the parameters we are using for
each utility:

RUNSTATS Utility -- TABLE INDEX (ALL) KEYCARD UPDATE ALL
REORG Utility -- STATISTICS TABLE (ALL) INDEX (ALL KEYCARD) UPDATE ALL


Barbara J Nigh
CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS)
1441 Schilling Place
Salinas, CA 93901

______________________________________________________

Phone. 831 754 1400 2048
Fax. 831-759-7073
Mobile. 831-776-6194 Pager [login to unmask email]
Email. [login to unmask email]
<mailto:[login to unmask email]>
Internet. http://www.hsbc.com < http://www.hsbc.com/ >

______________________________________________________






________________________________

******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!




________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >

________________________________

******************************************************************
This message originated from the Internet. Its originator may or
may not be who they claim to be and the information contained in
the message and any attachments may or may not be accurate.
******************************************************************

________________________________

******************************************************************
This E-mail is confidential. It may also be legally privileged. If
you are not the addressee you may not copy, forward, disclose or
use any part of it. If you have received this message in error,
please delete it and all copies from your system and notify the
sender immediately by return E-mail.

Internet communications cannot be guaranteed to be timely, secure,
error or virus-free. The sender does not accept liability for any
errors or omissions.
******************************************************************
SAVE PAPER - THINK BEFORE YOU PRINT!



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Joe Geller

Re: Differences in Statistics Taken in Runstats VS Reorg
(in response to David Simpson)
Your original description indicated that you ran the Runstats before the
Reorg. You should see a change in some the statistics because of the
Reorg (clusterratio in particular). Cardinalities should be the same, but the
Reorg could certainly result in a different access path.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L