[ad] Re: [DB2-L] V9 CM z/OS: runstats before initial post upgrade binds

Roy Boxwell

[ad] Re: [DB2-L] V9 CM z/OS: runstats before initial post upgrade binds
download and run our StatisticsHealthCheck -- It will tell you which
runstats you need now. Ask us *very* nicely and you can get the version
that creates corrective RUNSTATS
Do all of the above for the Critical and Serious problems and execute the
RUNSTATS - then as Joe said - Do a REBIND analysis in V8. Then port your
stats onto a sandbox V9 and do a REBIND analysis - if all is ok then you
need do no further work as your access path will be ok. The major reason
in V8 and V9 migration for REBINDs were the "PROCS" rebuilding. You will
have to do this
in 9 but first switch on your PLAN STABILITY (If you can to "extended" of
course!!) and then you should have no real problems!

Hope that all 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: Gerhard Schubert



Chris Hoelscher <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
23.01.2011 20:09
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] V9 CM z/OS: runstats before initial post upgrade binds






I have been asked to post the following questions:

First an explanation -although we are well aware of the v9 optomizer's
increased sensitivity to accurate statistics, it has been suggested to me
that we simply do not have time or resources to runstat our entire
enterprise in a limited amount of time (between corp-mandated freezes and
other no-upgrade zones(system upgrades and application upgrades), we only
have a few periods to get our upgrades done)

1) if we chose NOT to runstat all tables before our initial V9 rebinds -
has it been experience that access paths become less efficient - or no
changes - or is it all unpredictable

2) if we choose to runstat all tables before our initial v9 rebinds - can
the runstats be done before the upgrade to v9 - or has the runstat utility
changed at V9 so that the statistics generated would be more meaningful
done AFTER upgrade to v9?

3) You might ask - if no runstat - why rebind at all? To pick up PTFS that
require rebinds to pick up software/path changes - is that not a good
enough reason to rebind without runstats?

4) if it will take us 6 months to runstat entirely - should we hold off on
initial binds, or go ahead knowing we can get back to the v8 access path
via plan stability ??

Thank a bunch

Chris Hoelscher
IDMS & DB2 Database Administrator
502-476-2538

I refuse to repeat gossip - so listen closely the first time


The information transmitted is intended only for the person or entity to
which it is addressed and may contain CONFIDENTIAL material. If you
receive this material/information in error, please contact the sender and
delete or destroy the material/information.

_____________________________________________________________________
* 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!
*
** 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


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to 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 Listserv

Terry Purcell

Re: V9 CM z/OS: runstats before initial post upgrade binds
(in response to Roy Boxwell)
I sent this reply to Chris off-list and was going to simplify the answers for consumption by the list - but never got around to simplify.......so here was my answers to Chris' questions:

The one commonality in the answer I would provide is - it is preferable for the V9 optimizer to be given V9 statistics.

I would also point out that DB2 9 is no more sensitive to statistics than any prior release. What happens each release is that the optimizer is enhanced (opening up new opportunities and evolving the cost formulas). A query may have been relying on a limitation to choose the correct path, and when that limitation is resolved, the query is exposed as a problem. The underlying statistics issue may have always been there, but never surfaced.

1) if we chose NOT to runstat all tables before our initial V9 rebinds - has it been experience that access paths become less efficient - or no changes - or is it all unpredictable

ANSWER: A lot depends on the type of application. For example, traditional OLTP systems were developed with performance in mind - I refer to these as disciplined applications. Queries were written with knowledge of the indexes and vice versa - so access path changes are less common. A packaged application may be less disciplined and has the standard set of indexes plus additional created by the customer. Each query may have many indexes that look viable, and thus the new statistics are important for optimizer to distinguish. So you may be able to answer your question if you can judge how disciplined your applications are.

2) if we choose to runstat all tables before our initial v9 rebinds - can the runstats be done before the upgrade to v9 - or has the runstat utility changed at V9 so that the statistics generated would be more meaningful done AFTER upgrade to v9?

ANSWER: As Joe responded, RUNSTATS has changed for indexes significantly in V9. I have not thought about it in detail, but it is theoretically possible to RUNSTAT tables (and indexes) before migration and only RUNSTAT indexes immediately after. The risk however is that changes in data volumes between these 2 sets of RUNSTATS

3) You might ask - if no runstat - why rebind at all? To pick up PTFS that require rebinds to pick up software/path changes - is that not a good enough reason to rebind without runstats?

ANSWER: The main reason to REBIND is to get back the runtime optimizations such as SPROCS. These can buy you 2-7% performance gain for V9, which means you lose that same amount when you migrate and do NOT rebind. I would not say rebinding on V9 with V8 stats is a valid way to expose yourself to the new optimizer.

4) if it will take us 6 months to runstat entirely - should we hold off on initial binds, or go ahead knowing we can get back to the v8 access path via plan stability ??

ANSWER: Much depends on how well you can pro-actively analyze, or react when there is a problem. Rebind on V9 with V8 stats gets you the new V9 runtime structures (and buys back the few percentage points you lost) - but you are not getting the real V9 optimizer experience. So there may be access path issues. Some time later, you RUNSTAT and rebind again, and you have a whole new opportuntity for access path changes in every query. So it's like a 2nd migration. Plan stability helps here as you point out.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* 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