REORG INDEX/INDEXES command using the ADMIN_CMD procedure command

REORG INDEX/INDEXES command using the ADMIN_CMD procedure re organizes index/indexes.

You can reorganize all indexes that are defined on a table by rebuilding the index data into un-fragmented, physically contiguous pages. On a data partitioned table, you can reorganize a specific nonpartitioned index on a partitioned table, or you can reorganize all the partitioned indexes on a specific data partition.

If you specify the <parmname>CLEANUP</parmname> option of the index clause, cleanup is performed without rebuilding the indexes. You can cleanup a specific index on a table, or you can cleanup all of the indexes on the table.

This command cannot be used against indexes on declared temporary tables <ph rev="se022699_d1">or created temporary tables</ph> (SQLSTATE 42995).

Scope

This command affects all database partitions in the database partition group.

Authorization

One of the following authorities:
  • SYSADM
  • SYSCTRL
  • SYSMAINT
  • DBADM
  • SQLADM
  • CONTROL privilege on the table.

Required connection

Database

Command syntax

Please see the REORG INDEX/INDEXES command for command syntax details.

Examples

To clean up the pseudo deleted keys and pseudo empty pages in all the indexes on the EMPLOYEE table while you allow other transactions to read and update the table, enter: 
   db2 reorg indexes all for table homer.employee allow write 
    access cleanup

Usage notes

Please see the REORG INDEX/INDEXES command for the full list of usage details.

Restrictions:

  • Command execution status is returned in the SQLCA resulting from the CALL statement.
  • The REORG utility issue a COMMIT statement at the beginning of the operation, which with Type 2 connections, causes the procedure to return SQL30090N with reason code 2.
  • Please see the REORG INDEX/INDEXES command for the full list of restrictions.