DB2 - SQL Alter Index Statement

The SQL ALTER INDEX statement is used to alters the definition of an index.

Following is the index alteration syntax.

ALTER INDEX index-name
      [REGENERATE]
      [ADD COLUMN (column-name ASC/DESC)]
      [CLUSTER | NOT CLUSTER]
      [PADDED | NOT PADDED]
      [using-specification]
      [free-specification]
      [COMPRESS YES | NO]
      [ALTER partition-element
            using-specification
            free-specification]
      [BUFFERPOOL bp-name]
      [CLOSE YES | NO]
      [PIECESIZE integer]
      [COPY YES | NO]

Explanation:

• REGENERATE: Specifies that the index will be regenerated.

• ADD COLUMN: Adds column-name to the index.

• ALTER PARTITION: Identifies the partition of the index to be altered.

Example 1:

Alter the index DSN8910.IX_EMP. Indicate that DB2 is not to close the data sets that support the index when there are no current users of the index.

ALTER INDEX DSN8910.IX_EMP
    CLOSE NO;

Alter partitioned index DSN8910.IX_DEPT. For partition 3, leave one page of free space for every 13 pages and 13 percent of free space per page. For partition 5, leave one page for every 25 pages and 25 percent of free space. For all the other partitions, leave one page of free space for every 6 pages and 11 percent of free space.

ALTER INDEX DSN8910.IX_DEPT
USING
    VCAT CATLGG
FREEPAGE 6
PCTFREE 11
ALTER PARTITION 3
    USING VCAT CATLGG
    FREEPAGE 13
    PCTFREE 13,
ALTER PARTITION 5
    USING VCAT CATLGG
    FREEPAGE 25
    PCTFREE 25;

Example 2:

Alter the index DSN8910.IX_PROJ. Use BP1 as the buffer pool that is to be associated with the index, indicate that full image or concurrent copies on the index are allowed, and change the maximum size of each data set to 8 megabytes.

ALTER INDEX DSN8910.IX_PROJ
        BUFFERPOOL BP1
        COPY YES
        PIECESIZE 8M;

Example 3:

Assume that index IX_X1 contains a least one varying-length column and is a padded index. Alter the index to an index that is not padded.

ALTER INDEX IX_X1 NOT PADDED;

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!