Well, in Db2 10 and above you can use TABLESAMPLE for UTS spaces
indeed really sample, unlike SAMPLE that does not... Clear??
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]>
[login to unmask email]
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich
From: Lock Lyon [mailto:[login to unmask email]
Sent: Tuesday, September 11, 2018 1:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS v11: RUNSTATS on Static
IMHO, I think that automating running RUNSTATS is a waste of effort
implements a costly procedure.
I believe that RUNSTATS gathers statistics by reading every row in
/ index(es). If so, it's a costly process both CPU- and
- If you have a lot of statically bound packages, RunStats
help unless you ReBind; and even then, you need to be careful that
path(s) don't regress. Do you have a plan management scheme
- If you have a lot of dynamic SQL, don't you run the risk of
changing access paths without knowing what effects that will have
someone runs a critical on-line application?
- If there has been a significant amount of Ins/Upd/Del
since the last execution of RunStats, ... well, if you implement
automation then volatile tables will get RunStats run regularly. Is
wise? Didn't execution N of RunStats measure the effects of the
won't the N+1 execution give you the same info? (Excepting, of
cardinality changes.) Oh, and if these tables are volatile, aren't
running Reorgs? If so, won't you have a Stats specification as part
Reorg? Do you really want lots of activity on a table to trigger a
and not a Reorg, at least for the indexes?
Last, don't forget publish/subscribe table pairs, replication
pairs, and any
self-built clones. These encompass situations where a table in
location A is
copied verbatim on a regular basis to location B. Example:
tables thatare copied to the data warehouse, DW dimension tables
published to data marts, etc. These target tables may get
daily, yet their contents may not change!
Just some thoughts, may not be applicable to your enterprise.
- Lock Lyon
From: Gallagher,Bill R [mailto:[login to unmask email]
Sent: Monday, September 10, 2018 12:51 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 z/OS v11: RUNSTATS on Static (Unchanging)
We’re looking into enhancing the automation of our RUNSTATS
utilizing the data in the RTS tables to determine if and when
to be run for any particular tablespace.
We’re looking at three basic conditions:
1. RUNSTATS has never been run for a tablespace
2. There has been a significant amount of insert/update/delete
against a tablespace since the last execution of RUNSTATS
3. RUNSTATS hasn’t been run in “x” number of
That last condition is being thrown in as a “catch-all”
condition, just to
make sure that we don’t have any stale statistics (which we
A question came up about the impact of doing that. Basically, is
benefit to running RUNSTATS against tablespaces that contain very
of or no changed data, and conversely, is there any harm in doing
Assuming that we have no odd circumstances which cause access paths
misbehave when the tablespaces or indexes that they access are
RUNSTATS (as far as I know, we do not), my assumptions are the
Benefits: we could pick up different (and hopefully better) access
with current RUNSTATS due to DB2 maintenance or version
Harm: we would be unnecessarily consuming CPU cycles for executing
on a tablespace that has not been updated since the last time
Am I missing anything?
What are others doing for running RUNSTATS on a regular basis?
Bill Gallagher | Senior Systems Engineer, DBA | Data
-----End Original Message-----