CREATE INDEX statement

The CREATE INDEX statement defines a new index for a table.

CREATE INDEX indexOwner.indexName

This option specifies the fully qualified name of the new index.

SQLID SQLID

This option specifies the current SQLID.

TABLE tableOwner.tableName

This option specifies the table on which the index is being created. A global temporary table cannot be referenced in a CREATE INDEX statement.

KEYCOLUMNS (columnName order, ...)

This option specifies the column list that defines the key for this index.

order values for the KEYCOLUMNS parameter

Value	Description
ASC	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 the BUSINESS_TIME values are unique. The starting and ending columns of BUSINESS_TIME are defined in the table on which the index is created.

PERIOD BUSINESS_TIME parameter values

Value	Description
YES	Specifies that the period of time defined by the starting time and ending time for each row of the table do not overlap.
NO	Specifies that the period of time defined by the starting time and ending time for each row of the table can overlap.

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

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

AUX

This option indicates whether the index is an auxiliary index for a large object (LOB) column in an auxiliary table.

AUX parameter values

Value	Description
NO	Indicates that the index is not an auxiliary index.
YES	Indicates that the index is an auxiliary index.

COMMENT 'string'

This option specifies the comment for the index.

PART 0 indexPartitionParameters

This option specifies a nonpartitioned index according to indexPartitionParameters (see indexPartitionParameters).

NUMPARTS number (PART number indexPartitionParameters)

Defines the number of partitions for the index. For a partitioned index, NUMPARTS defines the parameters for PART number according to indexPartitionParameters (see indexPartitionParameters).

indexOptions

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.

GBPCACHE 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

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	Indicates that Db2 creates the data sets when it alters the index DEFINE YES is the default.
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	Indicates that the index can be closed. CLOSE YES is the default.
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	Indicates not to use a utility to create an image copy. COPY NO is the default.
YES	Indicates to use a utility to create an image copy.

CREATE INDEX statement

indexPartitionParameters

indexOptions

On this page