Solved

Reorgchk, Reorg, Runstats not having any effect after completion. Anyone know why this can be? Any solution?

Posted on 2008-06-23
7
590 Views
Last Modified: 2012-06-27
Hi all,
I am very surprised to see that after doing a Reorgchk followed by reorg of selected tables and concluding with a runstats of the reorged tables, all of the tables continue to be identified and selected as reorg candidates in subsequent/followup reorgchk.

Has anyone had this experience? Can you share with me what you may have found out to the the reason and if possible what are the possible solutions to correct the situation?

This is very important to us to get this tables to their optimal state and I'll greatly appreciate a solution to this issue.

Thanks
0
Comment
Question by:Enuda
  • 3
  • 3
7 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 21851540
Hi Eduda,

My guess is that you're not using the CLEANUP option an the REORG INDEX statements.  That should solve this.

It's annoying to have DB2 suggest that a REORG is still needed, but it shouldn't really affect performance.


Good Luck,
Kent
0
 

Author Comment

by:Enuda
ID: 21853073
Kdo,
Ok thanks. I'l try that and see...
0
 

Author Comment

by:Enuda
ID: 21854362
Kdo,
I have tried CLEANUP ONLY ALL and all but one table/index is left still indicating a need for reorg. Can you suggest what might be the problem here?
I will like to dig further and find out why. Could it be that the page size is not the right page size for the table? Just a thought.

Another question. CLEANUP ONLY says indexes are not rebuilt. What does that really mean? Isn't rebuilding indexes just the whole idea behing reorg indexes? You would think IBM could have made REORG do all CLEANUP tasks without being told or is there a reason for not doing so?

So far so god and thanks again
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

Accepted Solution

by:
Kdo earned 400 total points
ID: 21854618
Hi Enuda,

This isn't a page-size issue.  :(

I'd read that when a reorg leaves a table appearing that another reorg is required, running with the CLEANUP ONLY flag will perform a quick condensing of the index and can solve the problem.

CLEANUP attempts to physically restructure the index pages without scanning the source table and rebuilding them.  It physically deletes index entries that are marked as logically deleted, when it encounters (or generates) an index page with no entries it returns the page to the free pool, and where possible, it merges partial pages to reduce the total number of index pages.

This actually clearing the reorg check needed flag on one table/index is a good thing.  It would have been nice if it had gotten them all, but it did indicate that the issue with reorgchk is minor.

Let me study a bit....

Kent
0
 
LVL 45

Expert Comment

by:Kdo
ID: 21854638

Did you run the reorgs in exclusive mode?
0
 
LVL 16

Assisted Solution

by:theo kouwenhoven
theo kouwenhoven earned 100 total points
ID: 21856648
Hi Enuda,

Did you check the manual about reorganisation of indexes?
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_utl_reorgindex.htm

Special the part "Concurrency and compatibility for REORG INDEX" (told me some new stuff :)
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_concurrencyreorgindex.htm

Good Luck,
Murph
0
 

Author Comment

by:Enuda
ID: 21870836
Kdo, No I did not run in exclusive mode. I allowed READ WRITE access during reorg. Is that what you mean?

murphey2, thanks for the links. I'll read thru them tonight and see what I can learn from it.
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

Suggested Solutions

Title # Comments Views Activity
Assess most serious Linux privilege escalation bug 17 149
Edit linux file using python 4 40
find age for two dates 5 35
mcrypt_create_iv() is deprecated 4 76
This is the error message I got (CODE) Error caused by incompatible libmp3lame 3.98-2 with ffmpeg I've googled this error message and found out sometimes it attaches this note "can be treated with downgrade libmp3lame to version 3.97 or 3.98" …
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…
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…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

895 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

18 Experts available now in Live!

Get 1:1 Help Now