DB2 8.2, Windows 2k

I just ran reorg on my table and it took 4 hours to run!!!

The table is approx 70 million records with a clustered index.

I have been searching the internet frantically looking for ways to speed up the reorg to an acceptable time but have been unable to find any.

Should the tablespace you use for the reorg be DMS? On another disk?


Thanks,
Charlie

Having the tablespace on another disk may make it go faster. But there is no real way to spped up a reorg.

Andy

For those out there reorging very large databases, do you have an 8 hour maintanance window? Our application can be down 2-4 hrs at the most. It looks like the IndxRecreat is taking the most time.

One idea I considered was creating an HADR pair of sync'd databases. Then failover to the secondary while performing maintanence on the primary, then swap them back. Anyone else have exp with a solution like this?

Charlie

Why don't you just run an online reorg. You can run it for a specified amount of time each day or each week during your window. Even if it does not finish during your window, running it a little each day will eventually get the job done.

It was my understanding that online reorg did not reorg the indexes. And I believe that is what we really need in order to restore high performance. Second, online reorg tends to be a real pig at taking up resources from what I've seen and takes forever.

Charlie

Have you tried the usual stuff:

1. Make bufferpools for the two tablespaces (including TEMPSPACE1) as large as possible.

2. Is TEMPSPACE1 twice as large as the space required for the table+indexes?

3. Can TEMPSPACE1 be on a different disk?

4. Change the degree of parallelism = number of cpu's.

5. Since the index is clustered, the data rows are already in sorted order. What would happen if you dropped and recreated the index instead of a reorg?

Regence

First thanks for the input. I am the sole DB2 guy here.

1. I just increased IBMDEFAULTBP to 4000 from 250. The tablespaces are all using this default bp.

2. Currently all the tablespaces are SMS

3. Not at this time.

4. I heard that in most cases turning INTRA_PARALLEL on hurt performance.

5. I'm still having issue understanding what is required to maintain a clustered index. After the reorg, the clusterratio (F4) went from 42 to 100. This lead to huge performance gains. Keeping this ratio high appears to be the key in the performance of this application. Is dropping and recreating the index a better strategy in this case? Does that require the db to be offline or just exhibit a slow down until the index is recreated?

Thanks,

Charlie...

1. I think 4000 pages is way too low. Assuming that you have the default 4K page size, 4000 pages = 16MB. I also assume that you have 32 bit Windows. I also assume that you've maxed out memory at 4GB and that the DB2 server does not run any other app. If the above is true, I would bump up the bufferpool to 2GB (i.e. 500,000 pages) or more. The usual recommendation is to use 50%-80% of available memory for bufferpools.

2. How many containers are defined for all the tablespaces that you have?

3. How many io servers and io cleaners are configured?

4. I would strongly recommend that you think about DMS tablespaces.

5. Can you run db2set and let me know the output?

6. What are the PCTFREE and MINPCTUSED values for the index?

7. Are there many inserts to the table? If so, are the rows inserted in an ascending key order or randomly?

8. How often do you run runstats?

9. Using an explicit LOCK TABLE command before reorg or create index may help speed up performance.

Regence