Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Unanswered: Reorganizing Tables

    Hey all,
    again a question regarding Reorganization of tables.

    Im running DB2 V7.2 FP 7 over AIX 4.3.3

    Reading the Administration Guide, I found there some differences between Full Reorganization of a table and Online Index Reorganization.

    When performing a Online Index Reorg, the space freed is available only to be used by other indexes of the table in question.
    In a Full Reorg, the space freed can be used by any other object.

    How does DB2 recognizes which Reorg Im running?
    On the Administration Guide, it states: "The REORG utility requires that all other applications that would normally be working against the affected table data and indexes be offline". Cool.. then the applications that access the table Ill be reorganizing should be offline. But what if theyre online, but not working? just connected?
    Is Full Reorg only launched when there are no other users connected to the Database, besides me?

    And about the MINPCTUSED, which is the parameter that makes Online Index Reorganization available. I was making some tests on my test environment and setting that parameter or not, didnt seem to make a diference.

    Do you know where can I find more about this?

    TIA.
    Fernando Ohana.

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

    I don't know if this helps, but something similar was already discussed in this forum in tread Reorg index .

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Reorganizing Tables

    Grofaty ... thanks for the link ...

    Well, Fernando, let me start with MICPCTUSED, with an example ...If you say MINPCTUSED for an index is 50%, then if the percetage of the index page used falls below 50%, then an online index reorganization occurs ... In this process, the index page is merged with the adjacent pages to get the index use more than this value of the page space ... Hence, if MINPCTUSED is 0, then there is no question of online index reorganization .... From V8, this process is called online index defragmentation ...

    About full REORG, as of version 7, it is REORG Table .. You can base this on index also (as grofaty's link suggests) and the link also talks about the new REORG INDEX Command .. As for how db2 identifies whether the table is online/offline, I assume it will be based on locks on the table ...

    BTW, the former is automatic , ie, you do not need to initiate anything ... The latter is a command ...

    As far as I understand, Non-leaf pages, if a merge causes them to be freed, it can be used only by the table ... If it is freed because of row deletions, it can be used by other tables also ..

    HTH

    Sathyaram



    Originally posted by F.OHANA
    Hey all,
    again a question regarding Reorganization of tables.

    Im running DB2 V7.2 FP 7 over AIX 4.3.3

    Reading the Administration Guide, I found there some differences between Full Reorganization of a table and Online Index Reorganization.

    When performing a Online Index Reorg, the space freed is available only to be used by other indexes of the table in question.
    In a Full Reorg, the space freed can be used by any other object.

    How does DB2 recognizes which Reorg Im running?
    On the Administration Guide, it states: "The REORG utility requires that all other applications that would normally be working against the affected table data and indexes be offline". Cool.. then the applications that access the table Ill be reorganizing should be offline. But what if theyre online, but not working? just connected?
    Is Full Reorg only launched when there are no other users connected to the Database, besides me?

    And about the MINPCTUSED, which is the parameter that makes Online Index Reorganization available. I was making some tests on my test environment and setting that parameter or not, didnt seem to make a diference.

    Do you know where can I find more about this?

    TIA.
    Fernando Ohana.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Reorganizing Tables

    Thansk, both Grofaty and Sathyaram.

    I just got confused with the terms on the Administration Guide, whether an online Index reorg would cause locks on that table or not.
    The point about free space, I also read it on the Administration Guide, here follows the small part of the text in which it is mentioned:

    "Index leaf pages that are freed for use following an online index |reorganization are available for re-use. However, the freed pages are |available only to other indexes in the same table. A full |reorganization of the table will free up pages for other objects when working |with a DMS storage model; or will free up disk space when working with a |SMS storage model.

    Index non-leaf pages are not freed for use following an online index reorganization. However, a full reorganization of the table will make the index as small as possible. The leaf and non-leaf pages are reduced in number as well as the levels of the index."

    Thanks for your time,
    Fernando Ohana.

    Originally posted by sathyaram_s
    Grofaty ... thanks for the link ...

    Well, Fernando, let me start with MICPCTUSED, with an example ...If you say MINPCTUSED for an index is 50%, then if the percetage of the index page used falls below 50%, then an online index reorganization occurs ... In this process, the index page is merged with the adjacent pages to get the index use more than this value of the page space ... Hence, if MINPCTUSED is 0, then there is no question of online index reorganization .... From V8, this process is called online index defragmentation ...

    About full REORG, as of version 7, it is REORG Table .. You can base this on index also (as grofaty's link suggests) and the link also talks about the new REORG INDEX Command .. As for how db2 identifies whether the table is online/offline, I assume it will be based on locks on the table ...

    BTW, the former is automatic , ie, you do not need to initiate anything ... The latter is a command ...

    As far as I understand, Non-leaf pages, if a merge causes them to be freed, it can be used only by the table ... If it is freed because of row deletions, it can be used by other tables also ..

    HTH

    Sathyaram

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Reorganizing Tables

    Thanks Fernando for quoting the admin guide on free space

    Sathyaram

    Originally posted by F.OHANA
    Thansk, both Grofaty and Sathyaram.

    I just got confused with the terms on the Administration Guide, whether an online Index reorg would cause locks on that table or not.
    The point about free space, I also read it on the Administration Guide, here follows the small part of the text in which it is mentioned:

    "Index leaf pages that are freed for use following an online index |reorganization are available for re-use. However, the freed pages are |available only to other indexes in the same table. A full |reorganization of the table will free up pages for other objects when working |with a DMS storage model; or will free up disk space when working with a |SMS storage model.

    Index non-leaf pages are not freed for use following an online index reorganization. However, a full reorganization of the table will make the index as small as possible. The leaf and non-leaf pages are reduced in number as well as the levels of the index."

    Thanks for your time,
    Fernando Ohana.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Reorganizing Tables

    To whom it may interest:

    Online Index Reorganization for DB2 V7.2

    I just found more information about Online Index Reorganization at the Adminstration Guide: Performance

    Unlike the Off-Line Reorg, the Online Index Reorganization is not triggered by a Reorg Table statement. When you set the MINPCTUSED parameter for an index greater than 0 (Defaul value), after any deletes on that table, the index page leaf, that contained the key deleted, will be checked for the amount of space left. If this value is less than the value specified for MINPCTUSED, then the neighbouring page leafs are also checked for free space, to verify the possibility of merging pages and free the space on one leaf to be used for anyother index of that SAME table.

    As the text on the Guide was a bit big, quoting it seemed tiring for reading.. I just hope it is not confused.

    Fernando Ohana.

Posting Permissions

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