I am using LUW 9.1 and have the following question.

What are the disadvantages (if any) if one runs runstats without reorg after considerable inserts/updates/deletes?

If you don't have many delete/update operations that would cause fragmentation on page level, you may not need a REORG. Have a look at the REORGCHK utility to see which rules are applied there.

OK.

What if REORGCHK utility suggest that the table is to be REORGed but we go ahead and do RUNSTATS?

What are the implications (doing constant RUNSTATS eventhough the REORGCHK suggests that the table has to be REORGed)?

I really don't understand your question. RUNSTATS and REORG are two utilities for two completely different tasks. One does not and cannot replace the other. RUNSTATS collects statistics, the latter modifies the organization of the data on the data pages. Of course, the statistics depend on the current organization.

Since REORGCHK also does a RUNSTATS under the covers, the net result of not doing a REORG but a RUNSTATS instead would be pretty much zero, zip, nada.

I did not articulate my question properly.

I meant to say REORGCHK CURRENT STATISTICS (which does not do RUNSTATS under the cover)....

Let me formulate my question in another way....

"It is my understanding that if you do RUNSTATS on a table that is highly disorganized and fragmanted, db2 sometimes will not use the indexes"

Is the above statement correct?

If you don't have current statistics, DB2 may not choose the optimal access plan. Whether index access is the best access or not is a different question to be decided on a query-by-query basis.

That is EXACTLY what I want to hear.

Thanks much for your reply.

If you run reorgcheck using current stats, it might not produce corret results for the reorg. If you have done many updates your best bet is to run with UPDATE Statistics to make sure that reorgcheck utility has the right information to go on.

If you run reorgcheck using current stats, it might not produce corret results for the reorg. If you have done many updates your best bet is to run with UPDATE Statistics to make sure that reorgcheck utility has the right information to go on.

You need to

1. REORG
2. RUNSTATS
3. BIND/REBIND

Brett, what good is a reorg, wiht out current stats if you have been doing major data movement?

Edit: do you mean an actual reorg or just a check?

Let's say that the *advantage* of doing runstats (without reorg) as compared with none of the two, that the subsequent access paths will be more adapted to the current (disorganized) state of the tablespace, i.e., more performant.

Huh?

Don't you want the stats AFTER the Reorg?

And then REBIND the sprocs so they can use the new stats?

are you doing an actual reorg or reorgcheck? If you are doing an actual reorg then you are correct, you want to follow up with stats. However, if you are doing a reorgcheck, then stats have to be done first(i.e. part of the reorgcheck utility).

I am fully aware that you need to reorg, runstat, rebind etc....

I think you are missing my point.

My question is very simple....

"What happens if I repeatedly do RUNSTATS (without first doing reorg) - assume that the table warrants reorg"