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

**Ian** · Jun 27 '08, 07:22 PM

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

Okonita via DBMonster.com wrote:

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.

**peter** · Jun 28 '08, 06:55 AM

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

On Jun 25, 6:46 am, Ian <ianb...@mobile audio.comwrote:

Okonita via DBMonster.com wrote:

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 havefound
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.

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.

**Okonita via DBMonster.com** · Jun 28 '08, 04:55 PM

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

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:

Hi all,
I am very surprised to see that after doing a Reorgchk followed by reorg of

>[quoted text clipped - 17 lines]

>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
>defragmentatio n 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

**peter** · Jun 29 '08, 05:05 AM

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

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

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:

Hi all,
I am very surprised to see that after doing a Reorgchk followed by reorg of

[quoted text clipped - 17 lines]

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 viahttp://www.dbmonster.c om

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.

**Okonita via DBMonster.com** · Jun 29 '08, 09:45 AM

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

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:

>Hello Peter,
>Thanks for the post. It confirms what seems to be happening here especially

>[quoted text clipped - 39 lines]

>--
>Message posted viahttp://www.dbmonster.c om

>
>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

**peter** · Jun 29 '08, 10:15 AM

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

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

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:

Hello Peter,
Thanks for the post. It confirms what seems to be happening here especially

[quoted text clipped - 39 lines]

--
Message posted viahttp://www.dbmonster.c om

>

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.c om

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.

**Okonita via DBMonster.com** · Jun 29 '08, 08:35 PM

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

Got it and thank you.

peter wrote:

>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]

>--
>Message posted viahttp://www.dbmonster.c om

>
>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.aspx/ibm-db2/200806/1

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment