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
>>-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