RUNSTATS' Alternative?

DB2 DBA

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

Steen Rasmussen

Re: RUNSTATS' Alternative?
(in response to DB2 DBA)
I'll leave out the vendor plug for now J

Why are you doing RUNSTATS every week ?

Are your tables really changing that much (cardinality etc.) - otherwise
I would be more selective and only do RUNSTATS for those objects where
you really had dramatic changes. You can use RTS to determine most of
these.



Steen Rasmussen
CA

Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals

IBM Certified Database Administrator - DB2 9 DBA for z/OS





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of DB2 DBA
Sent: Monday, November 23, 2009 12: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



________________________________

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

Philip Sevetson

Re: RUNSTATS' Alternative?
(in response to Steen Rasmussen)
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 >

_____________________________________________________________________

* 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