Solved

Is it good practice to take statistics without ever doing REORGs? What is recommended Best practice for DB REORG?

Posted on 2011-03-11
5
551 Views
Last Modified: 2012-05-11
Hi all,
I have a colleague who adamantly insist that running statistic only without ever doing REORG;s is perfectly sufficient for DB2 optimizer to construct an efficient access plan. Can this be true in any way? What happens to statistics if this tables are frequently refreshed - resume & also replaced?
Can you give me 5 to 10 reasons for having a regular REORD for DB2?
And finally, what are the critical DB2 columns for DB2 access path development? I also adamantly insist that without REORG, the underlying DB2 dataset may remain fragmented, clustering out of wack if it is in that state already, wasted space not reclaimed where the DB is fragmented, etc.

Can someone help me expand on this iwith solid verifiable arguments for having a consistent and regular REORG?

Thank you
0
Comment
Question by:Okonita001
5 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 300 total points
ID: 35107772
running reorgs and collecting statistics (runstats) are two different things

you would run runstats so db2 catalog statistics will be updated and the optimizer will be able to choose the best access path

you would run reorg to:
1) reclaim space
2) reorder table data (and reduce fragmentation)
3) have db2 apply structural changes (for example adding columns etc)

if you have a log table for example, that data is only appended to, you might not need to reorg that table.
Also reorg will  usually improve performance of sequential scans of the table. One row access using an index will not get better if you reorg the table (it might get better if you reorg the index)

so, it is true that for some tables you might not need to run reorg at all
you can find a list of columns which affect the optimizer in the db2 documentation
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35107775
db2 also provides stored procedures which recommends which tables to reorg, but those procedures are platform specific so you will need to tell me which platform you are using so i can tell you the procedure name
0
 

Author Comment

by:Okonita001
ID: 35112090
The platform is AIX/UNIX 5.3.
0
 
LVL 6

Assisted Solution

by:Tomunique
Tomunique earned 100 total points
ID: 35115817
As momi_sabag said, different issues, and your buddy could be right,

Your question: What happens to statistics if this tables are frequently refreshed - resume & also replaced?

Well, what happens if you DON'T rerun statistics?  The catalog could misrepresent the number of rows in the table, you could be doing table scans to death.  Or using the index on a table with only a couple pages in it.  
If you refresh it regularly, why reorg it?  How was it refreshed?

Also, some tables we have, we don't have the outage window available to do the reorg.  
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 35159976
good points...

1) run/collect statistics regularly... some shops dont actually automatically apply
    the collected stats... (mainframe may wish for a table to get to a certain size
    with an expected ratio of data , to "ensure" consistency of access times (not done as often these days...)

2) reorg only those objects that really need it otherwise you are wasting cycles, when presumably more important business @batch/online processing could be
taking place...


0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now