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
548 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
Comment Utility
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
Comment Utility
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
Comment Utility
The platform is AIX/UNIX 5.3.
0
 
LVL 6

Assisted Solution

by:Tomunique
Tomunique earned 100 total points
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I have been running these systems for a few years now and I am just very happy with them.   I just wanted to share the manual that I have created for upgrades and other things.  Oooh yes! FreeBSD makes me happy (as a server), no maintenance and I al…
Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
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.

763 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

11 Experts available now in Live!

Get 1:1 Help Now