By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

Runstats Utility

P: 4
analysing the catalog tables in db2 is there any parameter/parameters by which we can know that the statistics for teh optimizer have to be collected.

eg: an application developer is doing huge insertions and deletions on the table. the dba team do not know this or the appln team also does not convey this.
By monitoring the catalog or is there a way to query the catalog and know that massive changes have been done to the table and statistics have to be gathered for the optimizer to be efficient.

thanks in advance
Sep 3 '07 #1
Share this Question
Share on Google+
2 Replies


docdiesel
Expert 100+
P: 297
Hi,
don't know what platform you're working on, but on Linux/Unix/Window$ you'll find the command "reorgchk" which gives you some overview about the consistence of the tables and their depending indexes.

Regards,

Bernd

Expand|Select|Wrap|Line Numbers
  1. Example:
  2.  
  3. shell $ db2 reorgchk on table db2inst1.test
  4.  
  5. Doing RUNSTATS ....
  6.  
  7.  
  8. Table statistics:
  9.  
  10. F1: 100 * OVERFLOW / CARD < 5
  11. F2: 100 * (Effective Space Utilization of Data Pages) > 70
  12. F3: 100 * (Required Pages / Total Pages) > 80
  13.  
  14. SCHEMA    NAME                  CARD    OV    NP    FP ACTBLK    TSIZE  F1  F2  F3 REORG
  15. ----------------------------------------------------------------------------------------
  16. Table: DB2SVC03.TEST
  17. DB2SVC03  TEST                     2     0     1     1      -       48   0   - 100 ---
  18. ----------------------------------------------------------------------------------------
  19.  
  20. Index statistics:
  21.  
  22. F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
  23. F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))
  24. F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
  25. F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
  26. F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
  27.  
  28. SCHEMA   NAME                 CARD  LEAF ELEAF  LVLS ISIZE  NDEL   KEYS  F4  F5  F6  F7  F8 REORG
  29. -------------------------------------------------------------------------------------------------
  30. (here would be the list of indexes)
  31. -------------------------------------------------------------------------------------------------
  32.  
  33. CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
  34. for indexes that are not in the same sequence as the base table. When multiple
  35. indexes are defined on a table, one or more indexes may be flagged as needing
  36. REORG.  Specify the most important index for REORG sequencing.
  37.  
  38. Tables defined using the ORGANIZE BY clause and the corresponding dimension
  39. indexes have a '*' suffix to their names. The cardinality of a dimension index
  40. is equal to the Active blocks statistic of the table.
  41.  
Sep 3 '07 #2

P: 4
i work on db2 on mainframes. V8
Sep 4 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.