DB2 10.5 for Linux, UNIX, and Windows

ALTER INDEX statement

The ALTER INDEX statement alters the definition of an index.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • ALTERIN privilege on the schema of the index
  • ALTER privilege on the table on which the index is defined
  • CONTROL privilege on the index
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagram
>>-ALTER INDEX--index-name --COMPRESS--+-NO--+-----------------><
                                       '-YES-'   

Description

INDEX index-name
Identifies the index to be altered. The name must identify an index that exists at the current server (SQLSTATE 42704).
COMPRESS
Specifies whether index compression is to be enabled or disabled. The index must not be an MDC or ITC block index, catalog index, XML path index, index specification, or an index on a created temporary table or declared temporary table (SQLSTATE 56090).
NO
Specifies that index compression is disabled. A compressed index will remain compressed until the index is rebuilt via index reorganization or recreation.
YES
Specifies that index compression is enabled. An uncompressed index will remain uncompressed until the index is rebuilt via index reorganization or recreation.

Example

Alter index JOB_BY_DPT to be compressed index.
     ALTER INDEX JOB_BY_DPT
     COMPRESS YES