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
558 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

This tech tip describes how to install the Solaris Operating System from a tape backup that was created using the Solaris flash archive utility. I have used this procedure on the Solaris 8 and 9 OS, and it shoudl also work well on the Solaris 10 rel…
I promised to write further about my project, and here I am.  First, I needed to setup the Primary Server.  You can read how in this article: Setup FreeBSD Server with full HDD encryption (http://www.experts-exchange.com/OS/Unix/BSD/FreeBSD/A_3660-S…
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.:
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

707 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