Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: How to speed up reorg

    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?


    Code:
    reorg table DB2INST1.INSTANCEDATA index DB2INST1.INSTANCE_INDX1 use TEMPSPACE1;
    Thanks,
    Charlie

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Having the tablespace on another disk may make it go faster. But there is no real way to spped up a reorg.

    Andy

  3. #3
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    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

  6. #6
    Join Date
    Aug 2007
    Location
    TX, USA
    Posts
    5
    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

  7. #7
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    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...

  8. #8
    Join Date
    Aug 2007
    Location
    TX, USA
    Posts
    5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •