Reorgchk, Reorg, Runstats not having any effect after completion. Why is this?

Okonita via DBMonster.com wrote:

Quote: Originally Posted by 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.

As I suggested before, please post the output from reorgchk -- before
reorg, then after reorg/runstats.

As I explained before, this is very common with indexes. You may also
see it for tables depending on your physical table design / page size
etc. But without more information we can't help you.

On Jun 25, 6:46*am, Ian <ianb...@mobileaudio.comwrote:

Quote: Originally Posted by Okonita via DBMonster.com wrote: Quote: Originally Posted by 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. > Quote: Originally Posted by Has anyone had this experience? Can you share with me what you may havefound out to the the reason and if possible what are the possible solutions to correct the situation? > Quote: Originally Posted by This is very important to us to get this tables to their optimal state and I'll greatly appreciate a > Quote: Originally Posted by solution to this issue. > As I suggested before, please post the output from reorgchk -- before reorg, then after reorg/runstats. > As I explained before, this is very common with indexes. *You may also see it for tables depending on your physical table design / page size etc. *But without more information we can't help you.

I have seen this and it is a problem with the way metrics are used
generically without consideration of some aspects of a table. From
memory the the cluster ratio metric doesn't determine if it is a
clustering index. The other good one is using on-line reorg. It
cannot reduce the table to one page so if the table has a few rows it
always appears as a re-org required regardless of how many times you
do an online reorg. On this if the table is less than a few pages you
are better off performing a offline reorg. Then it goes down to one
page. I gave up using the utility and generated my own SQL statement
to determine the re-org list. It can be used as a base but you have
to mask out certain options.

By the way, it doesn't pick up certain situation. It cannot pick up
defragmentation within the tablespace where you have multiple
objects. We discovered a major performance issue in this area (raised
a PMR on version 9) My suggestion is to use the clause to determine
priority items to re-org and then re-org every table/index that
sustains updates regardless.

Hello Peter,
Thanks for the post. It confirms what seems to be happening here especially
that about tables with few rows. When I look at the tables falls into this
category, it appears to mainly tables with very few rows or some tables
defined for 4K tablespace but has since seen many column additions. Could it
be that the second type of tables are not "fitting in" nicely in the 4K
tablesspace (just like Ian was saying in his last post) and REORGCHK sees
that as needing reorging even if one has just been done? I don't know. I am
not an expert in reorg utility.

If I may ask, could you share your version SQL script to generate reorg list?
That will be appreciated and just as much educational how someone else is
doing his reorgs.

Thanks

peter wrote:

Quote: Originally Posted by Quote: Originally Posted by Quote: Originally Posted by Hi all, I am very surprised to see that after doing a Reorgchk followed by reorg of >[quoted text clipped - 17 lines] Quote: Originally Posted by >see it for tables depending on your physical table design / page size >etc. Â*But without more information we can't help you. > >I have seen this and it is a problem with the way metrics are used >generically without consideration of some aspects of a table. From >memory the the cluster ratio metric doesn't determine if it is a >clustering index. The other good one is using on-line reorg. It >cannot reduce the table to one page so if the table has a few rows it >always appears as a re-org required regardless of how many times you >do an online reorg. On this if the table is less than a few pages you >are better off performing a offline reorg. Then it goes down to one >page. I gave up using the utility and generated my own SQL statement >to determine the re-org list. It can be used as a base but you have >to mask out certain options. > >By the way, it doesn't pick up certain situation. It cannot pick up >defragmentation within the tablespace where you have multiple >objects. We discovered a major performance issue in this area (raised >a PMR on version 9) My suggestion is to use the clause to determine >priority items to re-org and then re-org every table/index that >sustains updates regardless.

--
Message posted via http://www.dbmonster.com

On Jun 29, 2:51*am, "Okonita via DBMonster.com" <u36825@uwewrote:

Quote: Originally Posted by Hello Peter, Thanks for the post. It confirms what seems to be happening here especially that about tables with few rows. When I look at the tables falls into this category, it appears to mainly tables with very few rows or some tables defined for 4K tablespace but has since seen many column additions. Couldit be that the second type of tables are not "fitting in" nicely in the 4K tablesspace (just like Ian was saying in his last post) *and REORGCHK sees that as needing reorging even if one has just been done? I don't know. I am not an expert in reorg utility. > If I may ask, could you share your version *SQL script to generate reorg list? That will be appreciated and just as much educational how someone else is doing his reorgs. > Thanks > > > peter wrote: Quote: Originally Posted by Quote: Originally Posted by Hi all, I am very surprised to see that after doing a Reorgchk followed by reorg of [quoted text clipped - 17 lines] Quote: Originally Posted by see it for tables depending on your physical table design / page size etc. *But without more information we can't help you. > Quote: Originally Posted by I have seen this and it is a problem with the way metrics are used generically without consideration of some aspects of a table. *From memory the the cluster ratio metric doesn't determine if it is a clustering index. *The other good one is using on-line reorg. *It cannot reduce the table to one page so if the table has a few rows it always appears as a re-org required regardless of how many times you do an online reorg. *On this if the table is less than a few pages you are better off performing a offline reorg. *Then it goes down to one page. I gave up using the utility and generated my own SQL statement to determine the re-org list. *It can be used as a base but you have to mask out certain options. > Quote: Originally Posted by By the way, it doesn't pick up certain situation. *It cannot pick up defragmentation within the tablespace where you have multiple objects. *We discovered a major performance issue in this area (raised a PMR on version 9) *My suggestion is to use the clause to determine priority items to re-org and then re-org every table/index that sustains updates regardless. > -- Message posted viahttp://www.dbmonster.com

The script evolved into a java UDF which does a lot more than just
selecting a re-org list. It works out an optimal schedule for a re-
org window based on past performance details, resources required,
running multiple streams and so on. For example, is there enough
space to do a inline re-org or should temporary area be used. Has
exclusion lists. Some tables such data propagator control table or
using table for event monitors cause issues. Don´t mind telling
people what it does and why but I want to maintain ownership of the
techniques I have used for obvious reasons. Sorry.

What do you mean by "For example, is there enough space to do a inline re-org
or should temporary area be used". ?

If you have space to do a reorg, you have space to do a reorg in-line/off-
line, does it matter?
If you don't have enough space, you don't have enough space. Both reorg
method need space to work with, right? Please correct me in this matter as I
am still learning some of the finer points of this tool...

Thanks

peter wrote:

Quote: Originally Posted by Quote: Originally Posted by >Hello Peter, >Thanks for the post. It confirms what seems to be happening here especially >[quoted text clipped - 39 lines] Quote: Originally Posted by >-- >Message posted viahttp://www.dbmonster.com > >The script evolved into a java UDF which does a lot more than just >selecting a re-org list. It works out an optimal schedule for a re- >org window based on past performance details, resources required, >running multiple streams and so on. For example, is there enough >space to do a inline re-org or should temporary area be used. Has >exclusion lists. Some tables such data propagator control table or >using table for event monitors cause issues. Don´t mind telling >people what it does and why but I want to maintain ownership of the >techniques I have used for obvious reasons. Sorry.

--
Message posted via http://www.dbmonster.com

On Jun 29, 7:40*pm, "Okonita via DBMonster.com" <u36825@uwewrote:

Quote: Originally Posted by What do you mean by "For example, is there enough space to do a inline re-org or should temporary area be used". ? > If you have space to do a reorg, you have space to do a reorg in-line/off- line, does it matter? If you don't have enough space, you don't have enough space. Both reorg method need space to work with, right? Please correct me in this matter as I am still learning some of the finer points of this tool... > Thanks > > > peter wrote: Quote: Originally Posted by Quote: Originally Posted by Hello Peter, Thanks for the post. It confirms what seems to be happening here especially [quoted text clipped - 39 lines] Quote: Originally Posted by -- Message posted viahttp://www.dbmonster.com > Quote: Originally Posted by The script evolved into a java UDF which does a lot more than just selecting a re-org list. *It works out an optimal schedule for a re- org window based on past performance details, resources required, running multiple streams and so on. *For example, is there enough space to do a inline re-org or should temporary area be used. *Has exclusion lists. *Some tables such data propagator control table or using table for event monitors cause issues. * Don´t mind telling people what it does and why but I want to maintain ownership of the techniques I have used for obvious reasons. *Sorry. > -- Message posted viahttp://www.dbmonster.com

I should have said inplace. Offline reorg needs to create an new
image. If it can't fit into the tablespace (inplace) then you have to
build the image in another tablespace and once the image is built the
old image can be removed. The new image has to be brought back into
the orginal tablespace. Obviously takes more time but means you don't
have to have large tablespaces. When your tables are large you don't
what to have double allocation just to do re-orgs. With automatic
storage management this equation changes. Note there are the free
space bit map issues to understand. If the table space isn't
specified as large you may hit the the table space size limit.

The other issue is with index sorts. If it is a big index it will use
temporary area for the sorts. If not enough area is avaliable it will
fail. Running several at once will cause more demand on temporary
tablespace.

Got it and thank you.

peter wrote:

Quote: Originally Posted by Quote: Originally Posted by >What do you mean by "For example, is there enough space to do a inline re-org >or should temporary area be used". ? >[quoted text clipped - 25 lines] Quote: Originally Posted by >-- >Message posted viahttp://www.dbmonster.com > >I should have said inplace. Offline reorg needs to create an new >image. If it can't fit into the tablespace (inplace) then you have to >build the image in another tablespace and once the image is built the >old image can be removed. The new image has to be brought back into >the orginal tablespace. Obviously takes more time but means you don't >have to have large tablespaces. When your tables are large you don't >what to have double allocation just to do re-orgs. With automatic >storage management this equation changes. Note there are the free >space bit map issues to understand. If the table space isn't >specified as large you may hit the the table space size limit. > >The other issue is with index sorts. If it is a big index it will use >temporary area for the sorts. If not enough area is avaliable it will >fail. Running several at once will cause more demand on temporary >tablespace.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums...bm-db2/200806/1