Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: DB2 Reorg indexes

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    it depents is you are using SMS or DMS table space.
    Grofaty

  3. #3
    Join Date
    Mar 2004
    Posts
    46
    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

  4. #4
    Join Date
    Sep 2006
    Posts
    4
    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.

  5. #5
    Join Date
    Mar 2004
    Posts
    46
    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.

Posting Permissions

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