Results 1 to 9 of 9
  1. #1
    Join Date
    May 2002
    Posts
    43

    Unanswered: Index Reorg for UDB version 7

    I have found two conflicting replies on this forum to the following ?

    When reorging a table, does the associated indexes also get reorged?

    I was under the assumption that they did not. But assumption are usually wrong. Here is a copy of the two different replies. Can someone verify this for me....

    Thanks in advance.....
    Koz

    1) In version 7, re-orging the table reorgs all the indexes.

    There must always be a unique index on a defined Primary Key that is used by DB2 to enforce uniqueness. Obviously, it usually is good for performance also. If you create an index before the Primary Key is defined (defined later with Alter Table) that is on the proper primary key columns (for a primary key to be defined later) and the index is defined as unique, then DB2 will use the index you created instead of creating its own.

    2) In version 7.2, the indexes would not be reorganized. If you want to reorg your indexes, you must drop and recreate them.

    You need sufficient tempspace to hold a copy of the table data for that node.

    I don't see any benefit to dropping indexes before reorg. Indeed, you may want to reorg according to your most heavily used index.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since I provided answer number 1), I will add the following quote from the Administration Guide: Performance for version 7.2 (SC09-2945-01), on page 266 to support my answer:

    "The REORG utility requires that all other applications that would normally be working against the affected table data and indexes be offline. You may have a work environment where you wish to limit the amount of time your applications cannot work against the data. In this environment, you might consider using the online index reorganization utility.

    The log space required for index rebuilding that takes place during a reorganization is calculated using:

    2 * (10500 + ((number of index pages / extent size) * 110) + (number of index pages * 45) + (number of index pages / 16000) * 64 ))

    The various parts of the calculations are to determine the different types of overhead associated with what is created and recorded in the logs as the indexes are being rebuilt."

    Note that they also discuss on-line index reorganization, which is a slightly different topic.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2002
    Posts
    43
    Thanks for the info.... We no longer have DB support from IBM and the answers we get sometimes are misleading. We created a script to reorg tables and indexes each week using the reorgchk and where hoping to simplify it. We really need to go to version 8 and be able to reorg tables or indexes online......

    Originally posted by Marcus_A
    Since I provided answer number 1), I will add the following quote from the Administration Guide: Performance for version 7.2 (SC09-2945-01), on page 266 to support my answer:

    "The REORG utility requires that all other applications that would normally be working against the affected table data and indexes be offline. You may have a work environment where you wish to limit the amount of time your applications cannot work against the data. In this environment, you might consider using the online index reorganization utility.

    The log space required for index rebuilding that takes place during a reorganization is calculated using:

    2 * (10500 + ((number of index pages / extent size) * 110) + (number of index pages * 45) + (number of index pages / 16000) * 64 ))

    The various parts of the calculations are to determine the different types of overhead associated with what is created and recorded in the logs as the indexes are being rebuilt."

    Note that they also discuss on-line index reorganization, which is a slightly different topic.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can reorg indexes on-line in 7.2, but be careful because it is really a background task that periodically checks for index disorganization and could potentially affect performance.

    Also keep in mind that indexes are always in the correct order. The only problem regarding index disorganization is that index page splits or deletes can occur.

    Page splits occur when rows are inserted into the table and there is no more space on the correct index page (remember that indexes must always be in the exact correct order). Then DB2 puts half the index entries on one page, and half on a new page (called a page split), to make room for the new entries. This makes the 2 index pages only half full, and slightly degrades performance while the split occurs. The insert problem can be alleviated by making enough freespace defined in the index (PCTFREE) to have room for inserts. The freespace is re-created after a reorg.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2002
    Posts
    43

    online index reorganization utility. for Version 7

    Now I am totally confused..... When I search for the "online index reorganization utility" it shows up as a Version 8 enhancement.... So where can I find information about it for version 7.2?????? Where they jumping the gun in the documents, because I could not find that utility in the admin guide.....

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think you misunderstand. On-line index reorganization is not a utility that you run when you want it to occur. It is a parameter (MINPCTUSED) set with the CREATE INDEX that is always running. Check the SQL manual for 7.2. This is a dangerous parameter to mess with and could degrade performance when you least expect it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

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

    Re: Index Reorg for UDB version 7

    In V7.2 Performance Tuning Guide, under 'Performance Tips for Administering indexes',

    Reorganizing an Index
    To get the best performance you can from your indexes, you should
    consider reorganizing your indexes periodically. Updates to your tables
    may cause index page prefetch to become less effective. To keep the
    effectiveness of index page prefetch you must reorganize the index.
    You can reorganize the index by either dropping and re-creating the index,
    or by using the REORG utility. For more information, see


    Version 8 onwards, you can do an inplace (aka online) table REORG ... In this case explict index REORG is necessary ...

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

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

    Re: Index Reorg for UDB version 7

    In V8, what's new, the terminology change is mentioned:


    Before Version 8, the term online index reorganization was used to describe the process of merging index leaf pages while the index was online. This function is now referred to as online index defragmentation of leaf pages. A more complete online index reorganization facility has been added for Version 8
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Just to clarify again, the REORG utility with INPLACE option is different than the index reorganization with MINPCTUSED set with the CREATE INDEX.

    MINPCTUSED is available in version 7.2 onwards, and REORG INPLACE is available in version 8.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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