473,287 Members | 1,515 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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

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

Jun 27 '08 #1
7 3256
Ian
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.

Jun 27 '08 #2
On Jun 25, 6:46*am, Ian <ianb...@mobileaudio.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.
Jun 28 '08 #3
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
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

Jun 28 '08 #4
On Jun 29, 2:51*am, "Okonita via DBMonster.com" <u36825@uwewrote:
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.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.
Jun 29 '08 #5
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.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

Jun 29 '08 #6
On Jun 29, 7:40*pm, "Okonita via DBMonster.com" <u36825@uwewrote:
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.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 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.
Jun 29 '08 #7
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.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....m-db2/200806/1

Jun 29 '08 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jari Korkiakoski | last post by:
Hello, What kind of difference does it make to update statistics via 'REORGCHK UPDATE STATISTICS ON TABLE ALL' or by running directly 'runstats on table xxx.yyy with distribution and detailed...
1
by: hikums | last post by:
1. I did a reorgchk, and found that F2 on table and F4 on index indicates a reorg is necessary. 2. Did a reorg. 3. Again I do a reorgchk, the report is the same as in Step 1 4. I do a...
16
by: andy.standley | last post by:
Hi, we are running DB2 V8.2 (8.1.0.80) on redhat linux and I am trying to set the reorg to be online. I use the control center on the box - db2cc and then configure automatic maintenance wizard -...
2
by: EpsilonAurigae | last post by:
does anyone have an idea as to why the reorgchk utility says that a particular table needs to be reorged even after running the reorg command on that particular table. answer is appreciated.
1
by: doug | last post by:
Ok IBM techs. Does the REORGCHK_TB_STAT and REORGCHK_IX_STAT Stored Procedures issue a Runstats. I have ran the old REORGCHK utility with stats and both SPs and it appear that the SPs do not...
2
by: dunleav1 | last post by:
I have a many row and many column table that is in a 16K page size. I have four indexes on the table. I am running row compression on the table. The table does not have a primary key. The table...
0
by: Sam Durai | last post by:
Which runstats options are specified when runstats is invoked from "reorgchk update statistics" Env : DB2 V8.2
2
by: Okonita | last post by:
Hi all, How can I implement using REORGCHK to tell REORG what DB2 UDB v8 tables, etc to perform REORG on? Any example script will be highly appreciated. Okonita
4
by: db2udb | last post by:
Hi, I have just taken over as the DBA for a database that has not had any reorgs/runstats run against it for the last three years. As a first step, I have just run reorg/runstats against the system...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.