V9 CM z/OS: runstats before initial post upgrade binds [SEC=UNCLASSIFIED]

Steve Tennant

V9 CM z/OS: runstats before initial post upgrade binds [SEC=UNCLASSIFIED]
Hi Chris, sorry for the late reply - the usual...

I can't disagree with anything Joe or Terry (obviously) has said. I
just thought I'd share what we are doing as we seem to have similar
issues.

For non v8/v9 reasons we need to rebind every package in our system
(~4000). Due to budgetary and time constraints this has to happen very
quickly and there are insufficient resources to check every changed
access path. About 30% change. We are in the middle of v8/9 upgrade
during this.

So we are doing this...

Copy out our production stats(v8).
Runstat everything over a couple of weeks (a bit easier for
us)(v9 CM)
Copy out the new stats (Log analyzer).
replace the old stats again.

Very soon after that we will be doing a mass rebind, thereby using v8
stats and v9 optimiser. But at least we have had a couple of months to
check the access path differences and we know pretty much what is going
to happen as we bound everything to a dummy collection and extracted the
explain data.

When all that is bedded down we intend to either runstat again or copy
in the ones we collected in step 2 above. Then as time permits we will
check the known, changed access paths in depth to see if it is o.k. to
rebind with v9 stats or determine if we need a hint/index/code change.

This was as changes make there way through the system we have the time
to assess performance as normal, and we have the breathing space to pick
up changed access paths that we know of and which we asses slowly.

As Terry points out we will almost certainly now be missing
opportunities for access paths that _would_ have improved if we were had
the time to have both V9 stats and v9 optimiser _before_ all this, but
needs must.....


One final thing, the pattern we see so far is this. For every hundred
packages bound: Access paths changes 30, of those maybe 3 need close
attention, the rest look like improvements. Hints used: out of every
10 hints, 8 are now redundant. And this is v9cm with v8 stats.

Steve T



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Chris
Hoelscher
Sent: Monday, 24 January 2011 6:09 AM
To: [login to unmask email]
Subject: [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

***************************************************************************************************
IMPORTANT:

* This transmission is intended for the use of the addressee only and might contain sensitive or legally privileged information. If you are NOT the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error, please notify the author immediately by telephone and delete all copies of this transmission together with any attachments.

* The Australian Customs and Border Protection Service DOES NOT AUTHORISE the recipient to further disclose this email or its contents without permission of the originator.

* Unsolicited commercial emails MUST NOT be forwarded to the originator of this transmission unless prior consent has been given.

***************************************************************************************************

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