Hi, if you create an index after a table reorg:

REORG TABLE T1;
CREATE INDEX I_T1 ON T1 (C1, C2, C3);

Is it safe to assume that the index is optimal? (as defined in DB2 Universal Database)

I would assume the answer to be yes, but are there any additional REORG parameters or other commands that would ensure the table (plus all indexes) is in an optimal state at the end of the above sql command sequence?

Yes, the index will be in optimal state right after you create it. That would be the same as if you reorged the table (which reorgs all the indexes automatically unless it is an "inplace" reorg). A table reorg actually rebuilds the index from scratch, just like a create index statement.

Interesting, thanks for the insight about reorg rebuilding indexes. I'd assumed (incorrectly) that REORG on a table would not REORG the indexes, but know now its not the case (unless its an inplace REORG as you state).

i m not able to get
inplace is used to reorg online
and reorg table tabname , is classic way which reorgs the table and indexes
means rebuild the indexes
--
as per marcus when we use inplace command it reorgs only the table and not the indexes ?? , kindly correct me on this
redgs
paul

Please check this link:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

That is correct. The indexes obviously get updated with the new RIDs of the rows (which have been moved via the reorg of the table), but the index itself does not get completely reorged.

Is the index optimal? What about if you are creating this new index or recreating old one and it is defined as CLUSTER? Then the table and index are not optimal.

Dave Nance