Hi!

We are getting performance issues about reorging huge tables in our database.

Details:

• Db2 9.7
• 4 TB database.
• + 100GB Physical memory.
• Nice disk write and read ratios
• 20% CPU use.

Last weekend after feeling ourselves unable to rebuild the biggest tables, we tried to reorg a 1GB table in a 2,3TB tablespace, it was working hard for 2 hours to reorg that simple table.

It seems that there is a db2 bottleneck but we dont know how to find it.

Could anyone help us? how can i improve directly the reorg performance? How can i know if there is any memory bottleneck?

Thanks in advance for ur help.

Revu.

what is the exact reorg syntax you used? How many rows in the table? Enough temp-space with the required pagesize?
how about db2diag output? numer of logfiles? numer of indexes?
Nice read/write ratios? And the individual disks? Not 1 getting all the I/O?

that's why partitioning is invented so one would not have to reorg/runstats entire huge table.

First of all:

We have already tried to rebuild/reorg another 20 tables (between 1 GB and 100GB sized) and the performance has became as bad as the first one.

That's a bit suspicious, it seems like it was a bottleneck during our reorg operations.

---

what is the exact reorg syntax you used?
offline reorg without using tempspace, using longlobdata option.

All the sap applications were stopped while data was in movement.



How many rows in the table?

14KK

Enough temp-space with the required pagesize?

Yes. There were enough space but we havent used tempspace option

how about db2diag output?

Nothing

numer of logfiles?

Logretaint recovery, 256 logfiles, about 200GB of total log transaction capacity

numer of indexes?

3

Nice read/write ratios?

There were no finding reported during the last storage review (2 weeks ago).

And the individual disks? Not 1 getting all the I/O?

there are 7 different disks, 1 container per disk for each tablespace.Tablespaces are DMS.

All the containers's i/o were similar.



Thanks a lot for u quick answer=)

Do you reorg table offline? There are four phases in a offline table reorgnization.
sort ,build ,replace , index_recreate .
Have you used db2pd or other monitor tools to see the runing time for each phase?

Im in agreement with you, but is impossible to fight against SAP developers...

Yes, we have db2pd'ed all the 4 phases.

There were no difference between them, all of them were reeeeeally slow.

Thx.

and what the io capacity is (10M/s、50M/s or 100M/s ?) when the table reorgnization is runing...

longlobdata :Long field and LOB data are to be reorganized.
this has nothing todo with/without tempspace as I can read

Perhaps the "build" phase was the slowest one.

If we split the time spent to reorg the table we could seen something like these

sort 15%
build 35%
replace 20%
index_recreate 30%

przytula_guy, are you saying that's impossible to reorg longlobdata without using tempspace, or are you suggesting that this could be the bottleneck?

thanks a lot!!!

the option is to force the reorg of long data (if any)
for offline reorg the tempspace is always used, and the tbspace for this can be specified : use tablespacename

Thanks for your answer, but i dont understand you.

Are you trying to say that we need to force the use of tempspace?

Is there any bottleneck using the longlobdata without specifing the temporary tablespace?

Is there a lot of wasted time moving the lobs?

I just need to know any way of monitoring my database in order to find the bottleneck.

Which are the memory pools affected by tables and indexes reorganizations?

Is there any relationship between reorgs and prefetchsize? and util_heap_size? and num_io_servers?

Thx for ur help.

how about reading some doc in
REORG INDEXES/TABLE
and developerswork ...
instead of requesting "spoon feeding"
try first to understand what db2 is doing and request specific questions instead of ..