V9 CM z/OS: runstats before initial post upgrade binds

Chris Hoelscher

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

Joe Geller

Re: V9 CM z/OS: runstats before initial post upgrade binds
(in response to Chris Hoelscher)
Chris,
For 1), before the upgrade we ran a process to compare the access paths
between V8 NFM & V9 CM (runstats were not run under V9, but our stats
are fairly current with weekly runstats of all tables). We found about 700
statements whose estimated time increased by 20% or more and a similar
number whose estimated time decreased by at least 20% (this is out of
1500 programs - I don't know the total number of statements). An
analysis of these did not find any significant performance issues. A large
percent were the same access path, just a changed estimate. Others
had different access paths, but in most cases were probably better. Of
course, every shop is different so as you ask - it is unpredictable.
But, for 4) if you use plan stability, then go ahead, don't worry. You can
get back the old access path. Bear in mind though, that some analysis
will need to be done because eventually a program change will happen and
the bad access path may come back. Doing proactive analysis as we did
is highly recommended.
2) Can you run the Runstats before the upgrade? To take advantage of
V9's new statistics you need to run the runstats after the upgrade. On the
other hand, if your stats are very out of date, it's a good idea to get them
up to date. On the third hand, if you do that, you should have a project
where you rebind everything after the runstats (on V8) and eliminate any
issues before the upgrade. If you don't bind until after the upgrade you will
have no clue as to whether a performance issue was due to the upgrade,
or the updated statistics (yes, I know that updated statistics should only
help, but that is not always the case).

Joe




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

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