Hi,
DB2 V8.2 Windown 2003

I have to REORG six tables and couple of indexes. Each table contains over 65million rows.
if i reorg one by one the reorg may take two days. So is it possible to REORG two or three tables at the same time?
all tables are in the same schema.

also is it good to use the TEMPSPACE1 to get maximum optimisation?

Thanks, Dgunas

So, you're thinking that if it takes two days for one digger to make a well, putting 6 diggers in the well will get you water by midday? I doubt that.

When I reorg last time, it took more than 24 hours and never finished. Since then I could not reorg again.
So I am planning to reorg again. thats why I am quering that can I run multiple reogr at the same time?

Thanks,

Is there a reason you want to reorganize all of them? Have you run a REORGCHK?
Running all the REORGs together would cause a lot of I/O contention and your other apps might get impacted, so you wouldn't necessarily cut the total time by a sixth.

Are you running online or offline reorgs? You may want to try online reorgs if you have been using offline ones. You can also consider utility priority .... decrease the priority during the online hours and increase it back to higher priority when the system is not busy ...

Just some options to ponder over ...

Thanks Sathyaram.
I have run the REORGCHK and it is indicating to REORG. The reason I want to do this, one of the tablespace is over 90% full (4k page). the HWM is 14.8 millioan. before drop and recreate the indexes i have to reorg the table and indexes. if not i have to move it to bigger tablespace (8K or 32K). i am trying to avoid this option as i have to move all millions of rows.

I am doing the offline REORG. I have just started for one table. is there any way to check what is happening in backround?
Thanks

This is the command currently i am using:

db2 reorg table edb.account use temspace02

Typically, REORGCHK must report a '*' at some place or other anyway, for example "there can be only one..." cluster index per table. So what exactly do you mean by REORGCHK indicating the REORG? Maybe there is just no need at all and you are chasing after the wrong thing?

Furthermore, a tablespace being 90% full can mean that the containers of the TS are getting full. That does not necessarily have to say anything about the usefulness of a REORG. In the worst case, you would do the reorg and not reclaim any free space and still have 90% full TS.

And I don't understand your description on dropping recreating indexes. What does this have to do with your situation? Maybe you could start from the beginning and explain:
- how your tablespace was designed
- how you determine how full it is
- what exactly did reorgchk report
- which tables do you have created and which indexes exist
- ...
In short: everything that helps to initially understand your problem. Without that, you can only get common-place answers what to look out for.

p.s: I guess with 8K or 32K you were referring to the page size and not the tablespace size. I assume that you are aware of the implications? If you have lots of short rows and each page only holds 200+ rows, then moving the data to a tablespace with a bigger page size only results in one thing: you wast more disk space without being able to store more data.

Did you try INPLACE reorg with CLEANUP PAGES, you won't be able to use TEMP SPACE in that case but it should be faster sometimes. Try to allocate suffient space to your tablespace first so that the 90% full type of situation never happens whatever be the situation. Try to increase your UTILITY HEAP SZ as well. INTRA_PARALLEL ON option too helps sometime for faster REORG. As Stolze, Sathyaram mentioned, REORGCHK is the best bet (but definitely with updated Stats). The "*" will tell you whether they are really needed.

My point was that the "*" will not tell you whether reorg is really needed - it just tells you that some non-optimal situation was encountered that a reorg could resolve. But you will never get to the point that all "*"s are gone because you can't optimize data organization according to different, competing criteria.

Hi Stolzes,

The REORGCHK on TABLEs will tell you most of the time whether a REORG is really needed or not with the help of the "*". This I have observed for variety of databases if you are really not missing something big. But yes, REORGCHK on Index will always give you some "*" s which will never go away due to various reasons, one of them is the one for CLUSTERFACTOR. You can't achive CF > 80% for all Indexes for the same table in most of the cases.

Hi,
Rright, the tablespace is DMS which contain only indexe. And 4k page.
I have done the REORG to two master tables (not for indexes) and the TS looks like this now.

Tablespace ID = 6
Name = INDEXSPACE01
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 16515072
Useable pages = 16515024
Used pages = 13003696
Free pages = 3511328
High water mark (pages) = 16384016
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 32
Number of containers = 3
Minimum recovery time = 2002-12-10-10.26.50.000000

BEFORE the REORG the FREE PAGES was 966368.

I have tried to alter/add containers and it is not allowing as the max size has been reached.
The dart report shows the HWM can not be reduced further.

The current UTIL_HEAP_SIZE IS AUTO(33000)

I think we are in agreement. I only want to urge against just relying on REORGCHK without understanding what the output means...

Please see the HWM below which is after the REORG 2 tables. Before the REORG it was 14.1 million pages.
The question is by drop and recreating the indexes the HWM can be reduced ?


Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 16515072
Useable pages = 16515024
Used pages = 13003696
Free pages = 3511328
High water mark (pages) = 16384016
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 32
Number of containers = 3

May be, may be not. Run db2dart with the /LHWM switch, which will suggest ways of lowering the HWM.