DEFAULT STATS AND STATS REMOVAL

SUBSCRIBE DB2-L Anonymous

DEFAULT STATS AND STATS REMOVAL
Anyone know the default statistics DB2 (V8) uses for a table and index
against which statistics have never been gathered (CARD = -1/NLEAF = -
1) ? Does DB2 dynamically sample the object without stats to gather a
better idea for the optimizer? Has anyone ever removed all stats for an
object in order to improve/restore performance? If so, how is that done?

Also is there any way to gauge what is the approximate breakdown by
percentage in a database between the dynamic SQL versus the static SQL?

Thanks in advance.

_____________________________________________________________________

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

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

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

Roy Boxwell

Re: DEFAULT STATS AND STATS REMOVAL
(in response to SUBSCRIBE DB2-L Anonymous)
Answers in order of questions

NLEAF -1 is equal to SYSTABLES.CARDF / 300
CARDF -1 is equal to 10,000

No it does no sampling *ever*

Yes - Lots of times! I do it two ways (1) Drop and recreate object (2) SQL
UPDATEs against the catalog

Not that I know of!


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: Siegfried Fürst, Gerhard Schubert




David <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
04.01.2010 17:50
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] DEFAULT STATS AND STATS REMOVAL






Anyone know the default statistics DB2 (V8) uses for a table and index
against which statistics have never been gathered (CARD = -1/NLEAF = -
1) ? Does DB2 dynamically sample the object without stats to gather a
better idea for the optimizer? Has anyone ever removed all stats for an
object in order to improve/restore performance? If so, how is that done?

Also is there any way to gauge what is the approximate breakdown by
percentage in a database between the dynamic SQL versus the static SQL?

Thanks in advance.

_____________________________________________________________________

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

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User
Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User
Community
_____________________________________________________________________

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


_____________________________________________________________________

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

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

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

Robert Catterall

Re: DEFAULT STATS AND STATS REMOVAL
(in response to Roy Boxwell)
With respect to getting a handle on the percentage of dynamic versus static
SQL statements executed on a DB2 for z/OS subsystem, you might want to use
your DB2 monitor product to generate a Statistics Long Report (might be
called a Statistics Detail Report -- the name of the report varies somewhat
from one vendor's DB2 monitor to another) for a time period of interest (a
2-hour period of peak activity, a 24-hour period, whatever). In the report,
you'll see a block of figures under the heading SQL DML (as with the name of
the report, the actual wording of the heading might be a little different,
depending on the DB2 monitor that's in use at your site). You could first
calculate the the following sum:

SELECTs + OPEN CURSORs (a form of SELECT) + UPDATEs + INSERTs + DELETEs

Then, to get an idea as to how many of the executed statements were static
versus dynamic, subtract from the above sum the number of dynamic SQL
statements. To get that number of dynamic SQL statements executed:

- If you are NOT using dynamic statement caching (increasingly unusual
these days, as the default value for CACHEDYN, the "CACHE DYNAMIC SQL"
parameter in ZPARM, went to YES from NO starting with DB2 for z/OS V8), you
could simply subtract out the number of PREPAREs as shown in the
above-mentioned SQL DML report block. What you'd have left would be static
SQL statements executed, and the PREPAREs would be the number of dynamic SQL
statements executed.
- If you ARE using dynamic statement caching, go to the report block
labeled DYNAMIC SQL STMT (or something similar). Add the number of PREPARE
REQUESTS to the number of PREPARES AVOIDED to get the number of dynamic SQL
statements executed, and subtract that sum from the sum of DML statements
previously obtained (SELECTs + OPEN CURSORs + UPDATES + etc.) to get the
number of static SQL statements executed.

This should give you a good idea as to the breakdown of static versus
dynamic SQL statements executed on your subsystem. True, it doesn't include
statements such as FETCH and CLOSE CURSOR, but these can't be dynamically
prepared and including them in the SQL DML sum would, I think, skew the
results toward static SQL in a way that would make the static/dynamic
breakdown less meaningful. The same goes for DESCRIBE. I'd think that you'd
be primarily interested in the breakdown as it pertains to SELECT, INSERT,
UPDATE, and DELETE statements (and again, OPEN CURSOR is a form of SELECT).

Robert


On Mon, Jan 4, 2010 at 11:50 AM, David <[login to unmask email]> wrote:

> Anyone know the default statistics DB2 (V8) uses for a table and index
> against which statistics have never been gathered (CARD = -1/NLEAF = -
> 1) ? Does DB2 dynamically sample the object without stats to gather a
> better idea for the optimizer? Has anyone ever removed all stats for an
> object in order to improve/restore performance? If so, how is that done?
>
> Also is there any way to gauge what is the approximate breakdown by
> percentage in a database between the dynamic SQL versus the static SQL?
>
> Thanks in advance.
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User
> Groups,
> there is probably one near you!
> Regional User Groups are your local connection to the Worldwide DB2 User
> Community
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

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

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

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

SUBSCRIBE DB2-L Anonymous

Re: DEFAULT STATS AND STATS REMOVAL
(in response to Robert Catterall)
Thank you, Roy and Robert, for your very prompt and helpful responses.

Is there a utility or sample code in the Admin manual to use to restore stats
on an object back to the default? In our case re-creating the object is not
currently an option.

In V8 and V9, if the RTS is not being externalized to tables, does DB2 still
collect the RTS stats by default every 30 mins? I recall reading that RTS
may take an additional 4-5% overhead. Does this overhead apply to the
externalization alone or the ongoing collection whether externalized or not?

_____________________________________________________________________

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

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

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

Roy Boxwell

Re: DEFAULT STATS AND STATS REMOVAL
(in response to SUBSCRIBE DB2-L Anonymous)
not that I am aware of....to reset an objects statistics is not that
hard...

Just set the relevant optimizer used columns in the catalog to their
default values. To find the columns use the UTILITY guide and check out
RUNSTATS updates for access - from these docu tables you can see that you
have to update six catalog tables
SYSCOLUMNS (COLCARDF, HIGH2KEY, LOW2KEY)
SYSCOLSTATS (COLCARD, HIGHKEY, LOWKEY) - If PK62804 is applied
SYSINDEXES (CLUSTERRATIOF, FIRSTKEYCARDF, FULLKEYCARDF, NLEAF, NLEVELS if
in V9 DATAREPEATFACTOR)
SYSTABLES (CARDF, NPAGES, NPAGESF, PCTROWCOMP)
SYSTABLESPACE (NACTIVE, NACTIVEF)
SYSTABSTATS (CARDF, NPAGES)
and finally delete all rows for your objects(s) from the SYSCOLDIST

In 9 there is the new SYSKEYTARGETS (HIGH2KEY, LOW2KEY, STATS_FORMAT) and
you must also delete all rows from SYSKEYTGTDIST.

There are other optimizer used columns but they are "non-updateable" eg
LIMITKEY in SYSINDEXPART, CARDF in SYSKEYTARGETS, CLUSTERING in SYSINDEXES
or EDPROC in SYSTABLES

Hope that helps!


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: Siegfried Fürst, Gerhard Schubert

_____________________________________________________________________

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

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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