What's the free space requirement for reorg the indexes of the table using "db2 reorg indexes all for table xxx allow write access"?

Does it need free space in temporary tablespace or in the tablespace containing the indexes of the table?

My platform is AIX 5.2 + DB2 8 FP 10. Thanks.

Hi,
it depents is you are using SMS or DMS table space.
Grofaty

I believe that index reorg will use the tablespace that contains the index(es).
Here's what I dug out from the DB2 Admin Performance Guide -

REORG INDEXES has the following requirements:
- SYSADM, SYSMAINT, SYSCTRL or DBADM authority, or CONTROL privilege on the indexes and table

- An amount of free space in the table space where the indexes are stored equal to the current size of the index

Consider placing indexes subject to reorganization in a large table space when you issue the CREATE TABLE statement.

- Additional log space REORG INDEXES logs its activity. As a result, the reorganization might fail, especially if the system is busy and other concurrent activity is logged.

Note: If a REORG INDEXES ALL with the ALLOW NO ACCESS option fails, the indexes are marked bad and the operation is not undone. However, if a REORG with the ALLOW READ ACCESS or a REORG with the ALLOW WRITE ACCESS option fails, the original index object is restored.

HTH

-- Jayesh

It's DMS.

From administration guide, it states that it need free space in the tablespace where the indexes are stored.

However, I encounted the error which states another tablespace is full. That tablespace is a temporary tablespace I have created.

I have a feeling that's because DB2 sorted all the index entries which was done in the temp tablespace. So to sum it, you need sufficient space in temp to sort the index entries and additional space in the index tablespace to accomodate the entire new index and the old index.