Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
566 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 1200 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 400 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 400 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

My previous tech tip, Installing the Solaris OS From the Flash Archive On a Tape (http://www.experts-exchange.com/articles/OS/Unix/Solaris/Installing-the-Solaris-OS-From-the-Flash-Archive-on-a-Tape.html), discussed installing the Solaris Operating S…
Installing FreeBSD… FreeBSD is a darling of an operating system. The stability and usability make it a clear choice for servers and desktops (for the cunning). Savvy?  The Ports collection makes available every popular FOSS application and packag…
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.:
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

618 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