Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Unanswered: REORG multiple tables same time

    Hi,
    DB2 V8.2 Windown 2003

    I have to REORG six tables and couple of indexes. Each table contains over 65million rows.
    if i reorg one by one the reorg may take two days. So is it possible to REORG two or three tables at the same time?
    all tables are in the same schema.

    also is it good to use the TEMPSPACE1 to get maximum optimisation?

    Thanks, Dgunas

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, you're thinking that if it takes two days for one digger to make a well, putting 6 diggers in the well will get you water by midday? I doubt that.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Multiple reorg

    Quote Originally Posted by n_i
    So, you're thinking that if it takes two days for one digger to make a well, putting 6 diggers in the well will get you water by midday? I doubt that.
    When I reorg last time, it took more than 24 hours and never finished. Since then I could not reorg again.
    So I am planning to reorg again. thats why I am quering that can I run multiple reogr at the same time?

    Thanks,

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Is there a reason you want to reorganize all of them? Have you run a REORGCHK?
    Running all the REORGs together would cause a lot of I/O contention and your other apps might get impacted, so you wouldn't necessarily cut the total time by a sixth.

    Are you running online or offline reorgs? You may want to try online reorgs if you have been using offline ones. You can also consider utility priority .... decrease the priority during the online hours and increase it back to higher priority when the system is not busy ...

    Just some options to ponder over ...










    Quote Originally Posted by dgunas
    When I reorg last time, it took more than 24 hours and never finished. Since then I could not reorg again.
    So I am planning to reorg again. thats why I am quering that can I run multiple reogr at the same time?

    Thanks,
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Multiple reorg

    Quote Originally Posted by sathyaram_s
    Is there a reason you want to reorganize all of them? Have you run a REORGCHK?
    Running all the REORGs together would cause a lot of I/O contention and your other apps might get impacted, so you wouldn't necessarily cut the total time by a sixth.

    Are you running online or offline reorgs? You may want to try online reorgs if you have been using offline ones. You can also consider utility priority .... decrease the priority during the online hours and increase it back to higher priority when the system is not busy ...

    Just some options to ponder over ...
    Thanks Sathyaram.
    I have run the REORGCHK and it is indicating to REORG. The reason I want to do this, one of the tablespace is over 90% full (4k page). the HWM is 14.8 millioan. before drop and recreate the indexes i have to reorg the table and indexes. if not i have to move it to bigger tablespace (8K or 32K). i am trying to avoid this option as i have to move all millions of rows.

    I am doing the offline REORG. I have just started for one table. is there any way to check what is happening in backround?
    Thanks

  6. #6
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    This is the command currently i am using:

    db2 reorg table edb.account use temspace02

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Typically, REORGCHK must report a '*' at some place or other anyway, for example "there can be only one..." cluster index per table. So what exactly do you mean by REORGCHK indicating the REORG? Maybe there is just no need at all and you are chasing after the wrong thing?

    Furthermore, a tablespace being 90% full can mean that the containers of the TS are getting full. That does not necessarily have to say anything about the usefulness of a REORG. In the worst case, you would do the reorg and not reclaim any free space and still have 90% full TS.

    And I don't understand your description on dropping recreating indexes. What does this have to do with your situation? Maybe you could start from the beginning and explain:
    - how your tablespace was designed
    - how you determine how full it is
    - what exactly did reorgchk report
    - which tables do you have created and which indexes exist
    - ...
    In short: everything that helps to initially understand your problem. Without that, you can only get common-place answers what to look out for.

    p.s: I guess with 8K or 32K you were referring to the page size and not the tablespace size. I assume that you are aware of the implications? If you have lots of short rows and each page only holds 200+ rows, then moving the data to a tablespace with a bigger page size only results in one thing: you wast more disk space without being able to store more data.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Did you try INPLACE reorg with CLEANUP PAGES, you won't be able to use TEMP SPACE in that case but it should be faster sometimes. Try to allocate suffient space to your tablespace first so that the 90% full type of situation never happens whatever be the situation. Try to increase your UTILITY HEAP SZ as well. INTRA_PARALLEL ON option too helps sometime for faster REORG. As Stolze, Sathyaram mentioned, REORGCHK is the best bet (but definitely with updated Stats). The "*" will tell you whether they are really needed.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My point was that the "*" will not tell you whether reorg is really needed - it just tells you that some non-optimal situation was encountered that a reorg could resolve. But you will never get to the point that all "*"s are gone because you can't optimize data organization according to different, competing criteria.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Quote Originally Posted by stolze
    My point was that the "*" will not tell you whether reorg is really needed - it just tells you that some non-optimal situation was encountered that a reorg could resolve. But you will never get to the point that all "*"s are gone because you can't optimize data organization according to different, competing criteria.
    Hi Stolzes,

    The REORGCHK on TABLEs will tell you most of the time whether a REORG is really needed or not with the help of the "*". This I have observed for variety of databases if you are really not missing something big. But yes, REORGCHK on Index will always give you some "*" s which will never go away due to various reasons, one of them is the one for CLUSTERFACTOR. You can't achive CF > 80% for all Indexes for the same table in most of the cases.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  11. #11
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Reorg

    Quote Originally Posted by stolze
    Typically, REORGCHK must report a '*' at some place or other anyway, for example "there can be only one..." cluster index per table. So what exactly do you mean by REORGCHK indicating the REORG? Maybe there is just no need at all and you are chasing after the wrong thing?

    Furthermore, a tablespace being 90% full can mean that the containers of the TS are getting full. That does not necessarily have to say anything about the usefulness of a REORG. In the worst case, you would do the reorg and not reclaim any free space and still have 90% full TS.

    And I don't understand your description on dropping recreating indexes. What does this have to do with your situation? Maybe you could start from the beginning and explain:
    - how your tablespace was designed
    - how you determine how full it is
    - what exactly did reorgchk report
    - which tables do you have created and which indexes exist
    - ...
    In short: everything that helps to initially understand your problem. Without that, you can only get common-place answers what to look out for.

    p.s: I guess with 8K or 32K you were referring to the page size and not the tablespace size. I assume that you are aware of the implications? If you have lots of short rows and each page only holds 200+ rows, then moving the data to a tablespace with a bigger page size only results in one thing: you wast more disk space without being able to store more data.
    Hi,
    Rright, the tablespace is DMS which contain only indexe. And 4k page.
    I have done the REORG to two master tables (not for indexes) and the TS looks like this now.

    Tablespace ID = 6
    Name = INDEXSPACE01
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 16515072
    Useable pages = 16515024
    Used pages = 13003696
    Free pages = 3511328
    High water mark (pages) = 16384016
    Page size (bytes) = 4096
    Extent size (pages) = 16
    Prefetch size (pages) = 32
    Number of containers = 3
    Minimum recovery time = 2002-12-10-10.26.50.000000

    BEFORE the REORG the FREE PAGES was 966368.

    I have tried to alter/add containers and it is not allowing as the max size has been reached.
    The dart report shows the HWM can not be reduced further.

  12. #12
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111
    Quote Originally Posted by JAYANTA_DATTA
    Did you try INPLACE reorg with CLEANUP PAGES, you won't be able to use TEMP SPACE in that case but it should be faster sometimes. Try to allocate suffient space to your tablespace first so that the 90% full type of situation never happens whatever be the situation. Try to increase your UTILITY HEAP SZ as well. INTRA_PARALLEL ON option too helps sometime for faster REORG. As Stolze, Sathyaram mentioned, REORGCHK is the best bet (but definitely with updated Stats). The "*" will tell you whether they are really needed.
    The current UTIL_HEAP_SIZE IS AUTO(33000)

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by JAYANTA_DATTA
    Hi Stolzes,

    The REORGCHK on TABLEs will tell you most of the time whether a REORG is really needed or not with the help of the "*". This I have observed for variety of databases if you are really not missing something big. But yes, REORGCHK on Index will always give you some "*" s which will never go away due to various reasons, one of them is the one for CLUSTERFACTOR. You can't achive CF > 80% for all Indexes for the same table in most of the cases.
    I think we are in agreement. I only want to urge against just relying on REORGCHK without understanding what the output means...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    How to Reduce HWM

    Please see the HWM below which is after the REORG 2 tables. Before the REORG it was 14.1 million pages.
    The question is by drop and recreating the indexes the HWM can be reduced ?


    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 16515072
    Useable pages = 16515024
    Used pages = 13003696
    Free pages = 3511328
    High water mark (pages) = 16384016
    Page size (bytes) = 4096
    Extent size (pages) = 16
    Prefetch size (pages) = 32
    Number of containers = 3

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dgunas
    The question is by drop and recreating the indexes the HWM can be reduced ?
    May be, may be not. Run db2dart with the /LHWM switch, which will suggest ways of lowering the HWM.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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