[DB2-L] RUNSTATS' Alternative?

Walter Janißen

[DB2-L] RUNSTATS' Alternative?
Very good answer, Phil.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Sevetson, Phil
Gesendet: Montag, 23. November 2009 20:13
An: [login to unmask email]
Betreff: Re: [DB2-L] RUNSTATS' Alternative?

Josh,

I frankly don't see why your firm finds it necessary to keep RUNSTATS current for all tables.

If you need to know what tables and indexes are becoming disorganized, Realtime Statistics will keep things up to date and you can build queries to find objects with too many index levels or large NEAROFFPOS and FAROFFPOS (I forget the exact names for the cognates in RTS).

If you're doing Runstats for your optimizer, you'll find very little difference in access paths once a table has become large; and if your tablespace is time-partitioned, your older partitions will tend to be very stable and not need statistics updates. There winds up being a total of three scenarios where you need RUNSTATS on a weekly basis for the optimizer:
1) New tables, rapidly growing via SQL or LOAD/RESUME (Might need RUNSTATS and REBINDs and REORGs frequently in the first week or two)
2) Tables with fast-changing date columns, partitioned (update the recent partitions only) or not
3) Tables with recent structure changes or recent business use changes.

Tables that DO NOT need regular stats updates:
i) Large, stable tables do not need frequent RUNSTATS.
ii) LOAD/REPLACEd tables do not need RUNSTATS (use the inline STATISTICS option of the LOAD statement instead)
iii) Small, stable tables do not need frequent RUNSTATS.
iv) Highly volatile (expanding and contracting) tables may or may not need RUNSTATS; for such tables, find out what specific statistics give best-performing access paths, then set those statistics, document them in a multi-statement UPDATE, and don't do RUNSTATS for that table anymore.

If you can break down your biggest fifteen or twenty tables into the categories above and not do statistics for tables that don't need it (You might want to put those tables on a quarterly schedule), I believe you'll find that your weekend RUNSTATs will take a much shorter time.

Finally, have you tried using the SAMPLE parameter for your really large tables? A low SAMPLE value (i.e. 10 or 5) will greatly reduce the amount of time spent examining the large tables, without loss of optimization quality.

--Phil Sevetson

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2 DBA
Sent: Monday, November 23, 2009 1:36 PM
To: [login to unmask email]
Subject: [DB2-L] RUNSTATS' Alternative?

Hello:

DB2 V7
z/OS 1.4

As we all are aware how resource consuming RUNSTATS is (particularly for large tables), is there any alternative for this with any of the vendors? Or is there a work around?

I am not only worried about the cost (don't blame me for this) but also the 'time'. The existing set up is to have RUNSTATS run on Sundays, when rest of the world 'sleeps'. THIS, usually takes around 4 hours. Now, it is being recommended that the set up be moved to the regular weekdays and try to 'fit it' in the maintenance window which is 2 hours. So, we are planning to split this RUNSTATS job and run it in two different days. However, if there is any delay for any reason, we would have to push it beyond our maintenance window. And this is where we might have problems with the resource consumption.

(Splitting it into 5 different days and running 'em Mon-Fri is ruled out as we planned for some other 'adjustments' during maintenance window for the first 3 days of the week. Well, it doesn't look like a maintenance window any longer...)

Any suggestions/ideas/recommendations are welcome!


-Josh

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* 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

Raymond Bell

Re: RUNSTATS' Alternative?
(in response to Walter Janißen)
Hi Josh,

If you're still on DB2 V7 and z/OS 1.4 I suspect you have bigger issues than the CPU consumption of Runstats. To hopefully accurately summarise some of what Mr. Sevetson quite rightly said, easiest thing might be simply to not run Runstats quite so often. After all, as has been used in a number of contexts, the fastest [insert action being discussed] is no [insert action being discussed]. For those remaining objects that do need Runstats, and for which reducing the Runstats job CPU/elapsed time is important, yes I believe there's at least one ISV that has a Runstats offering.

Cheers,


Raymond

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2 DBA
Sent: 23 November 2009 18:36
To: [login to unmask email]
Subject: [DB2-L] RUNSTATS' Alternative?

Hello:

DB2 V7
z/OS 1.4

As we all are aware how resource consuming RUNSTATS is (particularly for large tables), is there any alternative for this with any of the vendors? Or is there a work around?

I am not only worried about the cost (don't blame me for this) but also the 'time'. The existing set up is to have RUNSTATS run on Sundays, when rest of the world 'sleeps'. THIS, usually takes around 4 hours. Now, it is being recommended that the set up be moved to the regular weekdays and try to 'fit it' in the maintenance window which is 2 hours. So, we are planning to split this RUNSTATS job and run it in two different days. However, if there is any delay for any reason, we would have to push it beyond our maintenance window. And this is where we might have problems with the resource consumption.

(Splitting it into 5 different days and running 'em Mon-Fri is ruled out as we planned for some other 'adjustments' during maintenance window for the first 3 days of the week. Well, it doesn't look like a maintenance window any longer...)

Any suggestions/ideas/recommendations are welcome!


-Josh

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* 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

Adam Baldwin

Re: RUNSTATS' Alternative?
(in response to Raymond Bell)
Following on from what Phil and Raymond have said, you really need to ask
the question "why do I want to run runstats?". As Phil stated there are
specific scenarios when having current statistics is vital. He mentioned
structure / business changes. Ideally you should have statistics as part of
your standard change methodology in these cases - for example, following
an alter to add columns with a reorg with stats.

There are many cases where access paths aren't going to change by having
more up to date statistics. Whatever product you use to maintain your
statistics you will have to evaluate the cost of statistics generation against
the benefits of having up to date stats.

If you currently run statistics for everything then you could look at trends over
a couple of weeks and see what is really changing.

Cheers, Adam

_____________________________________________________________________

* 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

Max Scarpa

Re: RUNSTATS' Alternative?
(in response to Adam Baldwin)
RTS is the first answer that came in my mind. But in a well-know DB2
environment (not always happens) every DBA knows if a table is growing
fast or not or if it's stable in time.

But cardinality, after a table reach a 'knee' needs to increase a lot
before, in general, providing a change in access path so running
RUNSTATS could be useless. Running runstats for a million rows

table increasing cardinality with low number is useless at minimum. I
never used sampling in Runstats, it gave bad result on access path, at
least in my experience. So first ask (as sugegsted) if you really

need runstats so frequently.

And pay attention, running runstats without a subsequent action for
packages is in many case useless; and it could be happen that new access
path is worse than before so it came out you didn't run

RUNSTATS utility but the other well-know inutility called
'RUINSTATS'......

Use RTS if possible.

Just the opinion of a bald,fat,angry DB2 sysprog or something similar.

Max Scarpa
Certified inutilities expert

_____________________________________________________________________

* 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

Bala

Re: RUNSTATS' Alternative?
(in response to Max Scarpa)
Hi Josh,

Same situation here. Somebody (long time ago) wrote a small program to get
ALL the tablespaces out of catalog and write Runstats control card for the
monthly runstats job !

I am thinking of having RTS enabled for a week or month to see the amount of
changes to cut down the analysis to half. Once done with trimming, I am
planning to put the runstats into small jobs and run them in parallel on
single Sunday.

Your question is slightly different but, wanted to share my points along
with other very good suggestions.

See you..

On Tue, Nov 24, 2009 at 12:06 AM, DB2 DBA <[login to unmask email]> wrote:

> Hello:
>
> DB2 V7
> z/OS 1.4
>
> As we all are aware how resource consuming RUNSTATS is (particularly for
> large tables), is there any alternative for this with any of the vendors? Or
> is there a work around?
>
> I am not only worried about the cost (don't blame me for this) but also the
> 'time'. The existing set up is to have RUNSTATS run on Sundays, when rest of
> the world 'sleeps'. THIS, usually takes around 4 hours. Now, it is being
> recommended that the set up be moved to the regular weekdays and try to 'fit
> it' in the maintenance window which is 2 hours. So, we are planning to split
> this RUNSTATS job and run it in two different days. However, if there is any
> delay for any reason, we would have to push it beyond our maintenance
> window. And this is where we might have problems with the resource
> consumption.
>
> (Splitting it into 5 different days and running 'em Mon-Fri is ruled out as
> we planned for some other 'adjustments' during maintenance window for the
> first 3 days of the week. Well, it doesn't look like a maintenance window
> any longer...)
>
> Any suggestions/ideas/recommendations are welcome!
>
>
> -Josh
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

* 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: RUNSTATS' Alternative?
(in response to Bala)
dont run RUNSTATS...very simple...and then only run one when you need to
run one



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




DB2 DBA <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
23.11.2009 19:36
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] RUNSTATS' Alternative?






Hello:

DB2 V7
z/OS 1.4

As we all are aware how resource consuming RUNSTATS is (particularly for
large tables), is there any alternative for this with any of the vendors?
Or is there a work around?

I am not only worried about the cost (don't blame me for this) but
also the 'time'. The existing set up is to have RUNSTATS run on Sundays,
when rest of the world 'sleeps'. THIS, usually takes around 4 hours. Now,
it is being recommended that the set up be moved to the regular weekdays
and try to 'fit it' in the maintenance window which is 2 hours. So, we are
planning to split this RUNSTATS job and run it in two different days.
However, if there is any delay for any reason, we would have to push it
beyond our maintenance window. And this is where we might have problems
with the resource consumption.

(Splitting it into 5 different days and running 'em Mon-Fri is ruled out
as we planned for some other 'adjustments' during maintenance window for
the first 3 days of the week. Well, it doesn't look like a maintenance
window any longer...)

Any suggestions/ideas/recommendations are welcome!


-Josh



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

Peter Vanroose

Re: RUNSTATS' Alternative?
(in response to Roy Boxwell)
A RUNSTATS frequency of once per week, taking 4 hours, is probably too frequent.
This of course depends on the "stability" of your data (statistically
speaking), but having a systematic (full) RUNSTATS once in three months is
probably more than enough.
And indeed, as others pointed out, just selectively do a RUNSTATS on those
tables (and their indexes) that changed drastically (like e.g. a DDL change,
or a serious data move, or an accumulatively large amount of INSERTs or
UPDATEs).
But in most of those cases, you most likely planned a REORG for those
tables, which allows an (almost free, i.e. without much runtine overhead)
RUNSTATS "inside" it (see the STATISTICS param of REORG).

-- Peter Vanroose,
ABIS Training & Consulting.


> [...] usually takes around 4 hours. Now,
>it is being recommended that the set up be moved to the regular weekdays
>and try to 'fit it' in the maintenance window which is 2 hours. So, we are
>planning to split this RUNSTATS job and run it in two different days. [...]

_____________________________________________________________________

* 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

DB2 DBA

Re: RUNSTATS' Alternative?
(in response to Peter Vanroose)
Peter, Roy, Bala, Max, Adam, Bell, Steen AND ofcourse Phil Sevetson... THANK
YOU ALL. I agree, it isn't necessary to run RUNSTATS for anything AND
everything under the sun PLUS as frequently as a week.

Although, I've been thinking about RTS for some time, it just didn't happen.
I never knew that it's available from V7, until I referred the manuals.

Anyway thanks again, for the direction.


-Josh




On Wed, Nov 25, 2009 at 3:15 AM, Peter Vanroose <[login to unmask email]> wrote:

> A RUNSTATS frequency of once per week, taking 4 hours, is probably too
> frequent.
> This of course depends on the "stability" of your data (statistically
> speaking), but having a systematic (full) RUNSTATS once in three months is
> probably more than enough.
> And indeed, as others pointed out, just selectively do a RUNSTATS on those
> tables (and their indexes) that changed drastically (like e.g. a DDL
> change,
> or a serious data move, or an accumulatively large amount of INSERTs or
> UPDATEs).
> But in most of those cases, you most likely planned a REORG for those
> tables, which allows an (almost free, i.e. without much runtine overhead)
> RUNSTATS "inside" it (see the STATISTICS param of REORG).
>
> -- Peter Vanroose,
> ABIS Training & Consulting.
>
>
> > [...] usually takes around 4 hours. Now,
> >it is being recommended that the set up be moved to the regular weekdays
> >and try to 'fit it' in the maintenance window which is 2 hours. So, we are
> >planning to split this RUNSTATS job and run it in two different days.
> [...]
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA < 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-Lis the home of IDUG's DB2-L
>

_____________________________________________________________________

* 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

Aurora Dell'Anno

Re: RUNSTATS' Alternative?
(in response to DB2 DBA)
errrr.... were you calling me Raymond? sorry sorry I was just round the
corner ;-)

Josh,

my 0.0000002c worth: I assume you will have to move from V7 to V8 kind
of soon - and when you get to V8 RTS are always collected, simply not
externalised (at which point of course you will have all the overhead as
part of the process anyway...) - incidentally a lot of DB2 customers are
finding great reductions in their daily process once they move to V9 so
maybe you can plan way ahead.

And yes, CA do offer an alternative to RUNSTATS so if you are interested
please contact me offline, or your local friendly CA rep...


Thanks.

Aurora

Aurora Emanuela Dell'Anno
CA
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
[login to unmask email]
<mailto:[login to unmask email]>

http://www.ca.com/

P please don't print this e-mail unless you really need to!




________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: 24 November 2009 08:42
To: [login to unmask email]
Subject: Re: [DB2-L] RUNSTATS' Alternative?



Hi Josh,



If you're still on DB2 V7 and z/OS 1.4 I suspect you have bigger issues
than the CPU consumption of Runstats. To hopefully accurately summarise
some of what Mr. Sevetson quite rightly said, easiest thing might be
simply to not run Runstats quite so often. After all, as has been used
in a number of contexts, the fastest [insert action being discussed] is
no [insert action being discussed]. For those remaining objects that do
need Runstats, and for which reducing the Runstats job CPU/elapsed time
is important, yes I believe there's at least one ISV that has a Runstats
offering.



Cheers,





Raymond



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2 DBA
Sent: 23 November 2009 18:36
To: [login to unmask email]
Subject: [DB2-L] RUNSTATS' Alternative?



Hello:



DB2 V7

z/OS 1.4



As we all are aware how resource consuming RUNSTATS is (particularly for
large tables), is there any alternative for this with any of the
vendors? Or is there a work around?



I am not only worried about the cost (don't blame me for this) but also
the 'time'. The existing set up is to have RUNSTATS run on Sundays, when
rest of the world 'sleeps'. THIS, usually takes around 4 hours. Now, it
is being recommended that the set up be moved to the regular weekdays
and try to 'fit it' in the maintenance window which is 2 hours. So, we
are planning to split this RUNSTATS job and run it in two different
days. However, if there is any delay for any reason, we would have to
push it beyond our maintenance window. And this is where we might have
problems with the resource consumption.



(Splitting it into 5 different days and running 'em Mon-Fri is ruled out
as we planned for some other 'adjustments' during maintenance window for
the first 3 days of the week. Well, it doesn't look like a maintenance
window any longer...)



Any suggestions/ideas/recommendations are welcome!





-Josh



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

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

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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

DB2 DBA

Re: RUNSTATS' Alternative?
(in response to Aurora Dell'Anno)
Aurora,

I thought RTS ARE available (and only collected, as we need to externalize
'em) in V7 too. Atleast, that's what I remember reading from the admin
guide. Correct me if I am wrong.

Also, based on some of the responses to the original post, I AM considering
a few approaches. Should I decide to get back to my original idea of
"looking" at an alternative, I shall certainly talk to you.

BTW, thanks for your response & suggestion.

-Josh Wawzynzak



On Wed, Dec 2, 2009 at 3:48 PM, Dell'Anno, Aurora <[login to unmask email]>wrote:

> errrr.... were you calling me Raymond? sorry sorry I was just round the
> corner ;-)
>
> Josh,
>
> my 0.0000002c worth: I assume you will have to move from V7 to V8 kind of
> soon - and when you get to V8 RTS are always collected, simply not
> externalised (at which point of course you will have all the overhead as
> part of the process anyway...) - incidentally a lot of DB2 customers are
> finding great reductions in their daily process once they move to V9 so
> maybe you can plan way ahead.
>
> And yes, CA do offer an alternative to RUNSTATS so if you are interested
> please contact me offline, or your local friendly CA rep...
>
>
> Thanks.
>
> Aurora
>
> **
>
> **
>
> **
>
> *Aurora Emanuela Dell'Anno*
> CA
> Sr. Engineering Services Architect
> Tel: +44 (0)1753 577 733
> Mobile: +44 (0)7768 235 339
> *[login to unmask email]
> * <[login to unmask email]>
>
> http://www.ca.com/
>
> P please don't print this e-mail unless you really need to!
>
>
>
>
> ------------------------------
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Bell,
> Raymond
> *Sent:* 24 November 2009 08:42
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] RUNSTATS' Alternative?
>
> Hi Josh,
>
>
>
> If you’re still on DB2 V7 and z/OS 1.4 I suspect you have bigger issues
> than the CPU consumption of Runstats. To hopefully accurately summarise
> some of what Mr. Sevetson quite rightly said, easiest thing might be simply
> to not run Runstats quite so often. After all, as has been used in a number
> of contexts, the fastest [insert action being discussed] is no [insert
> action being discussed]. For those remaining objects that do need Runstats,
> and for which reducing the Runstats job CPU/elapsed time is important, yes I
> believe there’s at least one ISV that has a Runstats offering.
>
>
>
> Cheers,
>
>
>
>
>
> Raymond
>
>
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *DB2 DBA
> *Sent:* 23 November 2009 18:36
> *To:* [login to unmask email]
> *Subject:* [DB2-L] RUNSTATS' Alternative?
>
>
>
> Hello:
>
>
>
> DB2 V7
>
> z/OS 1.4
>
>
>
> As we all are aware how resource consuming RUNSTATS is (particularly for
> large tables), is there any alternative for this with any of the vendors? Or
> is there a work around?
>
>
>
> I am not only worried about the cost (don't blame me for this) but also the
> 'time'. The existing set up is to have RUNSTATS run on Sundays, when rest of
> the world 'sleeps'. THIS, usually takes around 4 hours. Now, it is being
> recommended that the set up be moved to the regular weekdays and try to 'fit
> it' in the maintenance window which is 2 hours. So, we are planning to split
> this RUNSTATS job and run it in two different days. However, if there is any
> delay for any reason, we would have to push it beyond our maintenance
> window. And this is where we might have problems with the resource
> consumption.
>
>
>
> (Splitting it into 5 different days and running 'em Mon-Fri is ruled out as
> we planned for some other 'adjustments' during maintenance window for the
> first 3 days of the week. Well, it doesn't look like a maintenance window
> any longer...)
>
>
>
> Any suggestions/ideas/recommendations are welcome!
>
>
>
>
>
> -Josh
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

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

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

Bala

Re: RUNSTATS' Alternative?
(in response to DB2 DBA)
DB2 v7 had/have the RTS through few PTFs.. Recently (we are still on v7
in one DB2 ), we applied those PTFs and considering having RTS ON as it has
several benefits.

What has been change in DB2 v8 is that RTS is looked up by the IBM DB2
utilities (I think I am right here too). So, if RTS is there, IBM utilities
would look upto RTS for sortkeys, dynamic sort file allocation etc..

With V9 , RTS objects are integrated into DB2 Catalog.. (Right here too ?)

Sometimes working on DB2 v7, v8 and v9 simultaneously confuses me

Regards,
Bala.

On Thu, Dec 3, 2009 at 2:18 AM, Dell'Anno, Aurora <[login to unmask email]>wrote:

> errrr.... were you calling me Raymond? sorry sorry I was just round the
> corner ;-)
>
> Josh,
>
> my 0.0000002c worth: I assume you will have to move from V7 to V8 kind of
> soon - and when you get to V8 RTS are always collected, simply not
> externalised (at which point of course you will have all the overhead as
> part of the process anyway...) - incidentally a lot of DB2 customers are
> finding great reductions in their daily process once they move to V9 so
> maybe you can plan way ahead.
>
> And yes, CA do offer an alternative to RUNSTATS so if you are interested
> please contact me offline, or your local friendly CA rep...
>
>
> Thanks.
>
> Aurora
>
> **
>
> **
>
> **
>
> *Aurora Emanuela Dell'Anno*
> CA
> Sr. Engineering Services Architect
> Tel: +44 (0)1753 577 733
> Mobile: +44 (0)7768 235 339
> *[login to unmask email]
> * <[login to unmask email]>
>
> http://www.ca.com/
>
> P please don't print this e-mail unless you really need to!
>
>
>
>
> ------------------------------
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *Bell,
> Raymond
> *Sent:* 24 November 2009 08:42
>
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] RUNSTATS' Alternative?
>
> Hi Josh,
>
>
>
> If you’re still on DB2 V7 and z/OS 1.4 I suspect you have bigger issues
> than the CPU consumption of Runstats. To hopefully accurately summarise
> some of what Mr. Sevetson quite rightly said, easiest thing might be simply
> to not run Runstats quite so often. After all, as has been used in a number
> of contexts, the fastest [insert action being discussed] is no [insert
> action being discussed]. For those remaining objects that do need Runstats,
> and for which reducing the Runstats job CPU/elapsed time is important, yes I
> believe there’s at least one ISV that has a Runstats offering.
>
>
>
> Cheers,
>
>
>
>
>
> Raymond
>
>
>
> *From:* IDUG DB2-L [mailto:[login to unmask email] *On Behalf Of *DB2 DBA
> *Sent:* 23 November 2009 18:36
> *To:* [login to unmask email]
> *Subject:* [DB2-L] RUNSTATS' Alternative?
>
>
>
> Hello:
>
>
>
> DB2 V7
>
> z/OS 1.4
>
>
>
> As we all are aware how resource consuming RUNSTATS is (particularly for
> large tables), is there any alternative for this with any of the vendors? Or
> is there a work around?
>
>
>
> I am not only worried about the cost (don't blame me for this) but also the
> 'time'. The existing set up is to have RUNSTATS run on Sundays, when rest of
> the world 'sleeps'. THIS, usually takes around 4 hours. Now, it is being
> recommended that the set up be moved to the regular weekdays and try to 'fit
> it' in the maintenance window which is 2 hours. So, we are planning to split
> this RUNSTATS job and run it in two different days. However, if there is any
> delay for any reason, we would have to push it beyond our maintenance
> window. And this is where we might have problems with the resource
> consumption.
>
>
>
> (Splitting it into 5 different days and running 'em Mon-Fri is ruled out as
> we planned for some other 'adjustments' during maintenance window for the
> first 3 days of the week. Well, it doesn't look like a maintenance window
> any longer...)
>
>
>
> Any suggestions/ideas/recommendations are welcome!
>
>
>
>
>
> -Josh
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org/ >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>

_____________________________________________________________________

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

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

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

Max Scarpa

Re: RUNSTATS' Alternative?
(in response to Bala)
I used RTS even in V7.


Massimo Scarpa - Ufficio Sistemi
Cesve Servizi Informatici Bancari
v. Longhin, 1 - 35129 Padova
Tel. 049.8067111 - Fax. 049.8067300
E-Mail: [login to unmask email] - Web: http://www.cesve.it



Il presente messaggio non ha natura personale e le eventuali risposte allo
stesso potranno essere conosciute nell’ambito dell'organizzazione di
appartenenza del mittente. Esso , corredato degli eventuali relativi
allegati , contiene informazioni da considerarsi strettamente riservate ai
sensi della vigente normativa in materia di protezione di dati personali
ed è destinato esclusivamente al destinatario(i) sopra indicato. Chiunque
ricevesse questo messaggio per errore o comunque lo leggesse senza esserne
legittimato è avvertito che trattenerlo, copiarlo, divulgarlo,
distribuirlo a persone diverse dal destinatario è severamente proibito, ed
è pregato di rinviarlo immediatamente al mittente distruggendone
l’originale.



DB2 DBA <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
03/12/2009 18.01
Please respond to
IDUG DB2-L <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] RUNSTATS' Alternative?






Aurora,

I thought RTS ARE available (and only collected, as we need to externalize
'em) in V7 too. Atleast, that's what I remember reading from the admin
guide. Correct me if I am wrong.

Also, based on some of the responses to the original post, I AM
considering a few approaches. Should I decide to get back to my original
idea of "looking" at an alternative, I shall certainly talk to you.

BTW, thanks for your response & suggestion.

-Josh Wawzynzak



On Wed, Dec 2, 2009 at 3:48 PM, Dell'Anno, Aurora <[login to unmask email]>
wrote:
errrr.... were you calling me Raymond? sorry sorry I was just round the
corner ;-)

Josh,

my 0.0000002c worth: I assume you will have to move from V7 to V8 kind of
soon - and when you get to V8 RTS are always collected, simply not
externalised (at which point of course you will have all the overhead as
part of the process anyway...) - incidentally a lot of DB2 customers are
finding great reductions in their daily process once they move to V9 so
maybe you can plan way ahead.

And yes, CA do offer an alternative to RUNSTATS so if you are interested
please contact me offline, or your local friendly CA rep...

Thanks.
Aurora
Aurora Emanuela Dell'Anno
CA
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
[login to unmask email]
http://www.ca.com/
P please don't print this e-mail unless you really need to!



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: 24 November 2009 08:42

To: [login to unmask email]
Subject: Re: [DB2-L] RUNSTATS' Alternative?

Hi Josh,

If you’re still on DB2 V7 and z/OS 1.4 I suspect you have bigger issues
than the CPU consumption of Runstats. To hopefully accurately summarise
some of what Mr. Sevetson quite rightly said, easiest thing might be
simply to not run Runstats quite so often. After all, as has been used in
a number of contexts, the fastest [insert action being discussed] is no
[insert action being discussed]. For those remaining objects that do need
Runstats, and for which reducing the Runstats job CPU/elapsed time is
important, yes I believe there’s at least one ISV that has a Runstats
offering.

Cheers,


Raymond

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2 DBA
Sent: 23 November 2009 18:36
To: [login to unmask email]
Subject: [DB2-L] RUNSTATS' Alternative?

Hello:

DB2 V7
z/OS 1.4

As we all are aware how resource consuming RUNSTATS is (particularly for
large tables), is there any alternative for this with any of the vendors?
Or is there a work around?

I am not only worried about the cost (don't blame me for this) but
also the 'time'. The existing set up is to have RUNSTATS run on Sundays,
when rest of the world 'sleeps'. THIS, usually takes around 4 hours. Now,
it is being recommended that the set up be moved to the regular weekdays
and try to 'fit it' in the maintenance window which is 2 hours. So, we are
planning to split this RUNSTATS job and run it in two different days.
However, if there is any delay for any reason, we would have to push it
beyond our maintenance window. And this is where we might have problems
with the resource consumption.

(Splitting it into 5 different days and running 'em Mon-Fri is ruled out
as we planned for some other 'adjustments' during maintenance window for
the first 3 days of the week. Well, it doesn't look like a maintenance
window any longer...)

Any suggestions/ideas/recommendations are welcome!


-Josh



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



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



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




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