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.

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.

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......

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.

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.....

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.

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

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

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.