RUNSTATS with TABLE keyword

Balasubramaniyan Rengan

RUNSTATS with TABLE keyword
Hello,

I recently came across a presentation on RUNSTATS by Brian F Smith from IBM and it had the following question.

Is there any difference between running
RUNSTATS TABLESPACE DB1.TS1 TABLE (ALL) INDEX (ALL)
vs.
RUNSTATS TABLESPACE DB1.TS1 TABLE (ALL)
RUNSTATS INDEX(ALL) TABLESPACE DB1.TS1

I am curious to know the difference. In my shop we have most of the runstats I have seen are done using the first one. So, wondering if there will be any difference in the performance of RUNSTATS between the two. Could you please explain?

Regards
Bala

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Roy Boxwell

Re: RUNSTATS with TABLE keyword
(in response to Balasubramaniyan Rengan)
as he says there is no difference except for the timestamps between the
two runs and any updates to the columns in the indices and any
extra/deleted rows between the two. Personally I always try and get what I
call "consistant" RUNSTATS by doing the TB and IX together and using
FREQVAL to the max with HISTOGRAM if needed (First EXPLAIN all your table
usage without HISTOGRAM and if using between or range predicates a lot
then run a HISTOGRAM run and reEXPLAIN if the new data helps make sure to
keep the HITSOGRAM option up to date.

My preferred syntax for RUNSTATS looks like this:

RUNSTATS TABLESPACE DSNDB04.DSNUMTS
TABLE(ALL)
INDEX(ALL KEYCARD FREQVAL NUMCOLS 1 COUNT 10
FREQVAL NUMCOLS 2 COUNT 10
FREQVAL NUMCOLS 3 COUNT 10
FREQVAL NUMCOLS 4 COUNT 10
FREQVAL NUMCOLS 5 COUNT 10
FREQVAL NUMCOLS 6 COUNT 10
FREQVAL NUMCOLS 7 COUNT 10
FREQVAL NUMCOLS 8 COUNT 10
HISTOGRAM NUMCOLS 1
HISTOGRAM NUMCOLS 2
HISTOGRAM NUMCOLS 3
HISTOGRAM NUMCOLS 4
HISTOGRAM NUMCOLS 5
HISTOGRAM NUMCOLS 6
HISTOGRAM NUMCOLS 7
HISTOGRAM NUMCOLS 8)
SHRLEVEL CHANGE
UPDATE ALL REPORT NO
SORTDEVT SYSALLDA
SORTNUM 0004

Best way to test RUNSTATS is run two versions with REPORT YES and check
for differences - sometimes the amount of new data is shocking!
Remember that you need as many FREQVALs as between columns in all indices
in the tablespace (Or Maximum number of columns in any index in the space
- 1)and that for HISTOGRAM it is the lowest common denominator of common
column order and not allowed at all if you have RANDOM anywhere in the
space.

If you have any questions feel free to email me offlist! Especially for my
ppt "Are you a RUNSTATS Master?" where I go into detail about RUNSTATS...


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Balasubramaniyan Rengan <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
06.02.2011 14:03
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] RUNSTATS with TABLE keyword






Hello, I recently came across a presentation on RUNSTATS by Brian F Smith
from IBM and it had the following question. Is there any difference
between running RUNSTATS TABLESPACE DB1.TS1 TABLE (ALL) INDEX (ALL) vs.
RUNSTATS TABLESPACE DB1.TS1 TABLE (ALL) RUNSTATS INDEX(ALL) TABLESPACE
DB1.TS1 I am curious to know the difference. In my shop we have most of
the runstats I have seen are done using the first one. So, wondering if
there will be any difference in the performance of RUNSTATS between the
two. Could you please explain? Regards Bala


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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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