ALTER INDEX

The ALTER INDEX statement alters the characteristics of an existing index. It is also a CA IDMS extension of the SQL standard. You can change the structure and location of an index through the modification of the following attributes:
idmscu19
The ALTER INDEX statement alters the characteristics of an existing index. It is also a CA IDMS extension of the SQL standard. You can change the structure and location of an index through the modification of the following attributes:
  • Key count
  • Displacement
  • Uniqueness
  • Area association
This article describes the following information:
2
2
Authorization
To issue an ALTER INDEX statement, you must have the ALTER privilege on or own the table on which the index is defined.
Syntax
  ►►─ ALTER INDEX 
index-name
 ON ┬────────────────┬ 
table-identifier
 ────►                               └─ 
schema-name
. ─┘          ┌───────────────────────────────────────────┐  ►─▼─┬─ INDEX BLOCK CONTAINS 
key-count
 KEYS ─┬─┴──────────────────────►◄      ├─ DISPLACEMENT IS 
page-count
 PAGES ────┤      ├─┬───────┬─ UNIQUE ────────────────────┤      │ └─ NOT ─┘                             │      └─ IN 
segment-name
.
area-name
 ───────────┘  
Parameters
This section describes the parameters for the ALTER INDEX statement:
  • key-count
     KEYS
    Establishes the maximum number of entries in each internal index record.
    Key-count must be an unsigned integer in the range 3 through 8180.
    For more information on how to calculate
    key-count
    see INDEX BLOCK CONTAINS section.
  • page-count
    PAGES
    Specifies how far away from the index owner the bottom-level index records are stored.
    If the value of page-count is zero (0), the bottom-level internal index records are not displaced from the index owner.
    Limit: An unsigned integer from 0 - 32,767.
    For more information on how to calculate
    page-count
    see DISPLACEMENT section. 
  • UNIQUE
    Specifies that the index-key value in any given row of the table on which the index is defined must be different from the index-key value in all other rows of the table. The table cannot contain any duplicate index-key values.
    If you specify UNIQUE and the table contains duplicate index-key values, the alter statement will fail.
  • NOT UNIQUE
    Removes the restriction that all values of the index-key within the table must be unique.
    When the UNIQUE restriction is used as an enforcing index on a constraint, you cannot remove it from an index unless another index or CALC key can be used in its place.
    For more information on Enforcing Indexes see the usage section under CREATE TABLE.
  • IN
    Requests a change in the location of the named index.
  • area-name
    Identifies a new area with which the index is to be associated. Area-name must identify an area defined in the dictionary.
  • segment-name
    Identifies the segment associated with the area.
Usage
System tables
You cannot alter an index defined on a table in the SYSTEM schema.
Changing the Number of Entries in an SR8
It is sometimes desirable to change the number of entries in an SR8 system record after an index has been loaded. The ALTER INDEX statement enables the maximum number of entries to be changed without affecting the existing index structure.
Enforcing Indexes
You can not change a UNIQUE enforcing index to NOT UNIQUE.
For more information about index structure and design considerations, see the Structure of Indexes and Allocating Space for Indexes.
Example
In this example, the EMP_LNAME index is moved from its current location to the DEMO.EMPAREA area. Each internal index record will have a maximum of 30 keys and the bottom-level index records will be displaced 40 pages from the top of the index.
alter index emp_lname (last_name) on emp.benefits       displacement is 40 pages            index block contains 30 keys       in area demo.emparea;