Results 1 to 4 of 4

Thread: Db2 Reorg

  1. #1
    Join Date
    May 2002
    Posts
    43

    Question Unanswered: Db2 Reorg

    Is there any parameters that can be modified to help with reorg performance. We are running UDB Ver 7.2.

    It has taken as much as 4 hours to reorg a 4Gig table......

  2. #2
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    I will tell u abt a parameter which comes in handy in OS/390. Check to see if there is a corresponding parameter for UDB.

    REUSE - When this parameter is used, it does not delete define the underlying file while doing a Reorg. It just reuses the file and it saves a lot of time because a lot of time spend in deleting and reallocating a large file is saved.

    You may also want to implement 'Conditional Reorgs' to see if running a Reorg is neccessary in the first place.
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Specify the USE tablespace-name clause on the reorg (a system temporary table space in which to store a temporary copy of the table being reorganized). For a 8KB, 16KB, or 32KB table object, the page size of any system temporary table space explicitly specified by the user must match the page size of the tablespace being reorged.

    The temporary tablespace should be larger (preferably several times the size) than the table being reorged (if defined as DMS). A SMS temporary tablespace might take a little longer because it must constantly be increased as needed (but at least it will not run out of space if there is sufficient space on the volume).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    May 2002
    Posts
    43
    I wrote a script that first runs a db2 runstats on all the tables. Then a reorgchk with current stats (this works faster then running a reorgchk with update..... Then I decide if a table or index is up for reorg..... I then run:

    db2 -v reorg table $CREATOR.$TABLE use tempspace1
    or
    db2 -v reorg table $CREATOR.$TABLE INDEX $CREATOR.$INDEX use tempspace1

    and of course, a runstat after it completes.

    I recreate indexes if marked and then reorg the associated table.

    My script handles not reorging any table twice.

    Our tempspace1 is SMS, as recommended, and my data and index tablespaces are DMS. They are on seperate RAID devices with plenty of room.

    I will look into the other suggestions, Thanks for all the input.......
    Last edited by Koz; 01-21-04 at 14:48.

Posting Permissions

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