Question for DB2 Admins / Experts re RunStats 
Author Message
 Question for DB2 Admins / Experts re RunStats

How do you use RUNSTATS, and how often do you collect them?

On our mainframe, we have a number of production jobs which include a
step to execute a RUNSTATS request, such as the following:
RUNSTATS TABLESPACE &DBNAME..&TBLSPAC REPORT YES INDEX(ALL)

On a typical week, I can find 30 or more of these steps in a report of
"expensive job steps", with CPU usage ranging from 30 seconds up to over
3500 seconds.  This highest figure is for a partitioned table.  The
frequency has been reduced to monthly, but I believe the stats are being
taken on the entire table, not just the newest partition.  The control
statement reads:
RUNSTATS TABLESPACE SECCBS.CBS448A TABLE(ALL)
INDEX(ALL KEYCARD FREQVAL NUMCOLS(1) COUNT(50)
FREQVAL NUMCOLS(2) COUNT(50)
FREQVAL NUMCOLS(3) COUNT(50))
I believe that this table has 13 partitions.  One is added each month,
and the oldest is deleted.

I have not sound any RUNSTATS with SAMPLE.

The same steps in the same jobs run each week, or in a few cases, more
often.  I have read and/or been told that one reason for executing
RUNSTATS is to allow DB2 binds make the best decisions about access
paths, etc.  In addition, the statistics may help DB Admins make
decisions relating to performance in DB2 based applications, where
tables are loaded, or where indexes are created, or where heavy inserts,
deletes, etc. are occurring.

It seems clear that the number of statistics runs we make are far too
numerous to support the number of changes we do to DB2 programs.  I
haven't tried to make any enemies among the DB Admins by asking whether
they have used the statistics on any of these data bases / tables to
improve performance, but I have doubts, knowing our organization and the
reduced number of DB Admins.

Can anyone share their practices concerning running RUNSTATS, or point
me to any guidelines beyond what I read in IBM's DB2 documentation?  (I
haven't tried to locate IBM Redbooks yet, or other sources of
information.)

Thanks in advance for your help and suggestions!



Sun, 31 Jul 2005 02:45:18 GMT
 Question for DB2 Admins / Experts re RunStats

test


Sun, 31 Jul 2005 03:19:31 GMT
 Question for DB2 Admins / Experts re RunStats
The use of RUNSTATS can be a little "confusing". Like everything in DB2
the answer is always "it depends..."
If the table is loaded and then not updated afterwards (or only slightly)
then a single runstats after load is OK. You could also
do this on the partion level if needed. However if the table is changed a
lot then not only a REORG buta RUNSTATS is required
or you could tell REORG to get the stats at REORG time.


Sun, 31 Jul 2005 15:47:42 GMT
 Question for DB2 Admins / Experts re RunStats
Thanks for your reply!

Another question:
Do you use sampling, or full read of the tables?

I've just been reading about this stuff, because I figured we were doing a
"one size fits all" approach, and we have a more or less constant cost
reduction initiative going on.  I'm an applications "answer guy" with very
little DB2 involvement.  But in a couple of hours of reading the DB2
documentation on BookManager, I turned up the possibility of running stats on
a single partition of a table (which applies to one of our bigger apps), and
sampling (which I haven't seen being done in any of our apps).

Quote:

> The use of RUNSTATS can be a little "confusing". Like everything in DB2
> the answer is always "it depends..."
> If the table is loaded and then not updated afterwards (or only slightly)
> then a single runstats after load is OK. You could also
> do this on the partion level if needed. However if the table is changed a
> lot then not only a REORG buta RUNSTATS is required
> or you could tell REORG to get the stats at REORG time.



Mon, 01 Aug 2005 00:51:02 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. question for SYBASE systems admins.

2. Connection question to Progress Admins

3. SAS PEOPLESOFT SAP/R3, COBOL DB2 Y2K CICS EXPERTS AVAILABLE

4. Performance Expert and DB2 UDB ESE V8.1

5. Looking for experts on DB2 to do remote work

6. Where do DB2 experts hang out?

7. An expert : DB2 NT Installation Problem

8. DB2 to DB2 Federated questions

9. Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2

10. DB2/2 (DB2 for OS/2) questions

11. Help on Oracle statistics/runstat

12. REORG, RUNSTATS, BIND Strategy advice


 
Powered by phpBB® Forum Software