ALTER INDEX statement

The ALTER INDEX statement defines changes to an index.

ALTER INDEX indexOwner ₁ .indexName ₁

This option specifies the fully qualified name of the index to be changed.

OWNER indexOwner ₂

This option specifies the new owner of the index.

NAME indexName ₂

This option specifies the new name of the index.

TABLE tableOwner.tableName

This option specifies the table on which the index is created. If the only change to the index is the table name that it references, BMC AMI Change Manager for Db2 does not generate CDL. If you change the index to reference a different table, Change Manager generates DROP INDEX and CREATE INDEX CDL statements.

KEYCOLUMNS (columnName order, ...)

This option specifies the column list that defines the key for this index. The following table lists valid values for the order variable in the KEYCOLUMNS parameter.

Order values for the KEYCOLUMNS parameter

Value	Description
ASC (default value)	Places the key columns in ascending order. The key columns for indexes that are defined on an XML column and indexes on an expression can be placed only in ascending order.
DESC	Places the key columns in descending order.
RANDOM	Places the key columns in a random order.
INCLUDE	Specifies an additional column to be appended to the index key columns for a unique index.

KEYTEXT (expression)

This option specifies the parts of the XML documents in an XML column that are indexed (XMLPATTERN).

XMLdataType

For an XML index, this option specifies the SQL data type. The XMLdataType can be one of the following types:

VARCHAR(integer)
DECFLOAT
DATE
TIMESTAMP(12)

PERIOD BUSINESS_TIME

This option indicates whether to add starting and ending columns of the BUSINESS_TIME period to the index. The starting and ending columns are defined in the table on which the index is created.parameter.

PERIOD BUSINESS_TIME parameter values

Value	Description
YES	Specifies to add starting and ending columns of the BUSINESS_TIME period.
NO	Specifies not to add starting and ending columns of the BUSINESS_TIME period.

UNIQUE

This option indicates whether index values must be unique.

UNIQUE parameter values

Value	Description
YES	Defines the index as unique.
NO	Specifies to allow duplicates.
NOTNULL	Defines the index as unique and specifies that null values are not equal.

DSSIZE integer G

(SPE2010)

This option specifies the maximum size for each index partition in gigabytes.

COMMENT 'string'

This option specifies the comment for the index.

NOCOMMENT

This option removes the comment for the index.

NUMPARTS number

This option specifies the new number of partitions for the index. NUMPARTS=0 specifies a nonpartitioned index.

ALTER PART partitionNumber indexPartitionParameters

For a partitioned index, this option alters partitionNumber according to the parameters of indexPartitionParameters (see indexPartitionParameters). To alter parameters for a nonpartitioned index, the partitionNumber value must be zero. For example, the following example changes the value for the PCTFREE attribute for a nonpartitioned index to 20.

ALTER INDEX ...
ALTER PART 0 PCTFREE 20

ADD PART BEFORE partitionNumber indexPartitionParameters

For a partitioned index, this option adds a new partitionNumber according to the parameters of indexPartitionParameters (see indexPartitionParameters).

ADD PART AFTER partitionNumber indexPartitionParameters

For a partitioned index,this option adds a new partitionNumber according to the parameters of indexPartitionParameters (see indexPartitionParameters).

DROP PART number

For a partitioned index, this option drops the specified partition.

indexOptions

This option provides additional options for indexes (see indexOptions).

indexPartitionParameters

indexPartitionParameters includes the following parameters.

VCAT catalogName

This option specifies the volume catalog to be used for the index.

STOGROUP

This option specifies the new default storage group for the index.

STOGROUP parameter values

Value	Description
storageGroupName	Specifies the name of the storage group.
<DEFLT>	Specifies a default value. <DEFLT> is a reserved keyword.

PRIQTY number

This option specifies the primary space that is allocated for the Db2 defined data set.

SECQTY number

This option specifies the secondary space that is allocated for the Db2 defined data set.

ERASE

This option indicates whether to erase the data sets when they are deleted by a statement that drops the index.

ERASE parameter values

Value	Description
YES	Specifies to erase the data sets.
NO	Specifies not to erase the data sets.

FREEPAGE number

This option specifies that a free page is added after every number of pages upon index creation or reorganization.

PCTFREE number

This option specifies the percentage of free space left on each page of the index upon creation or reorganization.

GBPCACHE

This option specifies which index pages are cached to the group buffer pool. The following table lists valid values for the GBPCACHE parameter.

TGBPCACHE parameter values

Value	Description
CHANGED	Specifies to cache updated index pages to the buffer pool.
ALL	Specifies to cache all index pages as they are read.
NONE	Specifies not to cache index pages to the buffer pool.

DSSIZE integer G

(SPE2010)

This option specifies the maximum size for the index partition in gigabytes.

VALUES (constant, ...)

This option specifies the replacement key limit list for this part of a partitioned index.

indexOptions

indexOptions includes the following parameters.

CLUSTER

This option indicates whether the index is defined as a clustering index.

CLUSTER parameter values

Value	Description
YES	Specifies to use the index as the clustering index for the table.
NO	Specifies not to use the index as the clustering index for the table.

PADDED

This option indicates how columns that are defined as VARCHAR are stored in the index.

PADDED parameter values

Value	Description
NO	Indicates that the columns should not be padded to the length of the index.
YES	Indicates that the columns should be padded.

COMPRESS

This option indicates whether index data is compressed.

COMPRESS parameter values

Value	Description
NO	Specifies not to use index compression
YES	Specifies to use index compression

DEFINE

This option specifies when the data sets for the index are created.

DEFINE parameter values

Value	Description
YES (default value)	Indicates that Db2 creates the data sets when it alters the index.
NO	Indicates that Db2 creates the data sets when a row is inserted into a table.

PARTITIONED

This option indicates whether the index is partitioned.

PARTITIONED parameter values

Value	Description
YES	Indicates that the index is partitioned.
NO	Indicates that the index is not partitioned.

BUFFERPOOL

This option specifies the new default buffer pool for the index.

BUFFERPOOL parameter values

Value	Description
bufferPoolName	Specifies the name of the buffer pool.
<DEFLT>	Specifies a default value. <DEFLT> is a reserved keyword.

This option indicates whether the index can be closed when the index is not being used.

CLOSE parameter values

Value	Description
YES(default value)	Indicates that the index can be closed.
NO	Indicates that the index cannot be closed.

PIECESIZE integer keyword

This option specifies the maximum PIECESIZE for a nonpartitioned index. The default PIECESIZE is 2 G (2 GB) for indexes backed by non-LARGE table spaces and 4 G (4 GB) for indexes backed by LARGE table spaces. The subsequent keyword (K, G, or M) indicates the value of the specific integer.

PIECESIZE keyword values

Value	Description
K	Specifies the PIECESIZE in kilobytes.
M	Specifies the PIECESIZE in megabtyes .
G	Specifies the PIECESIZE in gigabytes.

COPY

This option indicates whether to use a utility to create an image copy of the index.

COPY parameter values

Value	Description
NO (default value)	Indicates not to use a utility to create an image copy.
YES	Indicates to use a utility to create an image copy.

ALTER INDEX statement

indexPartitionParameters

indexOptions

On this page