473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Runstats Utility

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
2 2573
docdiesel
297 Expert 100+
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
i work on db2 on mainframes. V8
Sep 4 '07 #3

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

Similar topics

7
by: Todd McNeill | last post by:
Hi- Something was just pointed out to me this morning. According to the V8 Command Reference, the RUNSTATS command no longer uses the SHRLEVEL CHANGE/REFERENCE clauses, and it looks to be...
3
by: Gilda | last post by:
Does anyone know what entity (utility?) checks if statistics collections are required, if the Automatic RUNSTATS is enabled, in version 8.2? How often are these checks done? Thanks.
1
by: raqfg | last post by:
Hi. I am trying to test out the automatic maintanance with notification feature of DB2 v8.2. I have enabled the auto maint with notification. The problem I am facing is that I only get email...
5
by: Jari Korkiakoski | last post by:
Hello, What kind of difference does it make to update statistics via 'REORGCHK UPDATE STATISTICS ON TABLE ALL' or by running directly 'runstats on table xxx.yyy with distribution and detailed...
1
by: alex.mcshane | last post by:
Hi - Via the UDB / LUW Control Centre I have tried in vain to identify when a utility was last run. (e.g. Clicking on a given Table) Any idea as to how this information can be retrieved? ...
4
by: kenfar | last post by:
I've got a large table on db2 8.2.1 that I rarely perform runstats on. It has about 600 million rows organized in a single MDC time dimension on a non-dpf warehouse. Anyhow, we recently ran...
4
by: db2udb | last post by:
Hi, I have just taken over as the DBA for a database that has not had any reorgs/runstats run against it for the last three years. As a first step, I have just run reorg/runstats against the system...
3
by: Norm | last post by:
If RUNSTATS is called directly, we can specify UTIL_IMPACT_PRIORITY to limit CPU consumption. For automatic runstats, there is no place to specify this paramater. Will it use the DBM value of...
7
by: Okonita via DBMonster.com | last post by:
Hi all, I am very surprised to see that after doing a Reorgchk followed by reorg of selected tables and concluding with a runstats of the reorged tables, all of the tables continue to be...
0
by: rajdb2 | last post by:
I am getting the following error for runstats for a few large tables in my prod database. I have increased the stats heap size to 6500 from 4382,but it didnot resolve the issue.Any help is...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.