Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Online REORG tables and indexes

    Hi all,

    Can some one help me with online REORGs. I need to start on line reorgs on a very busy gaming DB.

    As far as I know we need to have 70% free space in index tablespaces to reorg indexes online.

    And steps as below.

    1. reorgchk to decide which indexes and tables to do reorgs on.
    2. Check tablespaces related to these tables and indexes.
    3. Extend tablespaces if not enough free space ( tables 30% and indexes 70% free needed)
    4. Start online reorgs in groups.

    I have done reorgs online a few times, but this database is high activity database and I believe is sensetive too.

    I have following questions regarding reorgs.

    Q1 What happens if any reorg fails ? what steps might be needed to recover from failed reorgs. Is there any risk regarding database, tablespace or table availability in that situation ?

    Q2 What should I do as a precaution to prevent any reorgs from failing ? How many concurent tables are reccomended for online reorgs ?

    Please correct me if I miss anything or if I am wrong somewhere.

    Regards

    DBFinder

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    The trick in your scenario is to start the reorgs 1-by-1 and wait for 1 reorg to finish before you start the next one. The reorg-utility is something you must trust. You cannot anticipate a 'failure'. You can make sure that only 1 reorg at a time runs and schedule during low-activity, and do not forget to reorg the index as well after the data-reorg has finished.

    Do not be afraid of offline reorgs. They are 20 times faster than online-reorgs and the indexes are also included. Try it on your test-system and see how long each tables takes. My guess that 80% will take less than a minute and most applications can handle such a lock-situation. So your problem will be the remaining 20%. That will be your big-tables and you should know that a MDC definition makes them less reorg-sensitive.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Friends,

    The database that I am going to handle has over 20 tables every week for REORG.
    Indexes mostly are less than 10.

    When we do REORG online,

    What DB metrics need be watched to perform REORG safely. I mean CPU,Disk, Swap or memory ?
    How should we decide as how many tables can be reorged concurrently ??


    Help appreciated.

    DBFinder

    db2v9.5.5 WSE on win2k3

Posting Permissions

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