Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: tables constantly need to be re-orged what to do?

    Hello DB2 Gurus

    Any reason why our tables constantly need to be reorged? Can I do anything proactively to keep from having to reorg tables on a daily basis? Thanks

  2. #2
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39

    Re: tables constantly need to be re-orged what to do?

    Hi,
    Are there frequent deletes or updates happenning on your tables. If yes then we need to a regular reorg.
    Probably a cron job can be scheduled to run the reorg on a weekly basis.

    Thanks
    Sateesh
    Originally posted by mixxalot
    Hello DB2 Gurus

    Any reason why our tables constantly need to be reorged? Can I do anything proactively to keep from having to reorg tables on a daily basis? Thanks

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Check:
    http://dbforums.com/showthread.php?t...ighlight=reorg

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Mar 2003
    Posts
    343
    I think it is important to understand what is fragmenting the data. If the data is autoloaded, then be aware that autload insert by it's method of operation fragements the table (it does not look for free space - it starts afresh creating a new extent in every container and then plugs in the data).

    It is possible that the data is not autoloaded, but it is still fragemented due to frequent deletes and updates. To work around this alter the table and assign pctfree. If there's no space left on the page and there's an update to the row on that page then it chains over to another page - so for the row to be read, the database needs to pull in two pages instead of 1 - allocating pctfree will reserve some space on each page and the database will use this space to add updates. The other reason this could happen is because the right pagesize was not chosen for the tablespace in which the table resides ( remember that a page can contain a max of 255 rows). If the row length is 1.5k and the pagesize is 4k then that will in itself cause some fragmentation.

    Then there's index fragmentation caused by index page splits as mentioned in Grofaty's link.

    Hope this helps.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First you need to figure out whether rows should be inserted at the end of table, or in the middle, to maintain the correct order you want. If rows should be inserted at the end of the table (because the clustering sequence is always a higher number than existing rows on the table), then the table should be OK, and the clustering index should be OK, but all other indexes will need percent free defined (see below).

    If the rows should be inserted in the middle of table to maintain the correct sequence of the rows, then basically you need to figure out what percent of the rows are inserted each day and how many days you want to go without a reorg. So if 3% of the rows are inserted each day, and you want to go 5 days without a reorg, use 15-20% free for the table.

    The same holds true for the indexes. All indexes are always maintained in the exact order of the index. Any index row that is added at the end of the index each time does not need much freespace (maybe 1-2% is fine), but other indexes should be given a similar amount of freesapce as discussed above for tables (depending on insert activity and number of days between reorgs).

Posting Permissions

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