Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2006
    Posts
    17

    Unanswered: REORG execution time

    OS Version:Welcome to SuSE Linux 9.3
    DB2 UDB Version:"DB2 v8.1.2.104" FixPak "11".


    On what factors does REORG of a table depend on in terms of time.
    A table (TABLEA) is taking less time for REORG now (about 4 min) than before (7 min) inspite of more number of rows. And no new indexes were created/dropped on this table.

    The only thing that has changed was db2 restart. So are there any variables/registry settings that get reset and affect REORG time.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, all subsequent reorgs go faster because the data is in "order" from the first reorg. It basically only has to reorg what is new, not the whole table.

    Andy

  3. #3
    Join Date
    Dec 2006
    Posts
    17
    Hi Andy,
    Thanks for your quick response. But this table gets refreshed every day (export/load). And for couple of months it has been taking 7 min but after the restart its taking 4 min and the number of rows exported/loaded keep increasing day by day.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How often are you doing the reorg?
    What is the tablespace (sms or dms)?
    Is data ever deleted, or just added to the table?
    What about updates?

    Andy

  5. #5
    Join Date
    Dec 2006
    Posts
    17
    How often are you doing the reorg? Daily after export/load
    What is the tablespace (sms or dms)? DMS
    Is data ever deleted, or just added to the table? Load runs with replace option
    What about updates? Not sure how often if any

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Curious. My guess is that the table is laid out very nicely after all the reorgs and the load. Is the data in the load sorted?

    Andy

  7. #7
    Join Date
    Dec 2006
    Posts
    17
    The export is doing a simple select * from TABLE and no filtering is being performed. So I am not sure if the Load from such an export sorts the data.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, it will not sort the data. Has this base table been reorged also? Is it the same structure as TABLEA (clustering indexes, all indexes, table layout)?

    Andy

  9. #9
    Join Date
    Dec 2006
    Posts
    17
    The source table for the export is on AS400 and the target is on AIX. I do not have access to the source database so I am not sure if it was recently reorged , if it has same indexes as target. But the table layout should be same as we are doing a select * in the export and performing the load.

    But do you think if the source has already been reorged then it might impact the reorg time of target.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What I am implying is that the data in the load is already in the order of the clustering index (if you have one) on TABLEA. If you do not have a clustering index, then the order does not matter. Anyway, TABLEA has been reorged daily, so the data pages are laid out so well that subsequent reorgs will be fast.

    Andy

  11. #11
    Join Date
    Dec 2006
    Posts
    17
    Thanks Andy for your response. I have requested more information on the source table and will keep you posted if I hear anything.

Posting Permissions

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