INDEX is the link to access the data from the database very quickly. This index will decide the rows storing place based on the index type so that the data can be easily accessed without any delay.

CREATE INDEX used to create the INDEX. SYSADM or SYSCTRL authority is required to create the index. INDEX is an ordered set of pointers to the rows of table data. DB2 uses indexes to uniqueness and improve the performance.

An index is stored separately from the data in table. Index’s are physically stored in its index space. An Index can be partitioned or non-partitioned.

Advantages:

• To improve performance.
• Access to data is faster.
• To make sure that particular row is unique.
• To cluster the data.
• To determine the data partition.
• To provide index only access to data.

Disadvantages:

• Once the index is created, needs additional processing for inserting, updating and deleting index keys.
• Once the index is created, needs to perform the explicit maintenance like -reorganizing etc,.

Syntax -

CREATE UNIQUE INDEX index-name 
	ON table-name column-name 
	key-expression ASC/DESC/RANDOM
	NOT CLUSTER/CLUSTER
	PARTITIONED
	NOT PADDED/PADDED
	DEFINE YES/NO
	COMPRESS NO/YES
	PARTITION BY partition
	BUFFERPOOL bpname
	CLOSE YES/NO
	DEFER NO/YES
	PIECESIZE integer K/M/G
	COPY NO/YES

Key-Expression:

Is an index which has a key combination. A Key combination has one or more columns.

ASC:

Arranges key combination columns of index in ascending order.

DESC:

Arranges key combination columns of index in descending order.

RANDOM:

Arranges key combination columns of index randomly.

Cluster/Not Cluster:

Specifies that the index of a table is a clustered or not clustered. CLUSTER – Used to specify the index as clustered. NOT CLUSTER – Used to specify the index used should not be clustered.

Partitioned:

Specifies that the index is data partitioned. The types of partitioned indexes are partitioned and secondary. Portioned index can be created on partitioned table space.

Padded/Not Padded:

Specifies how the variable length records are added to the index key.

NOT PADDED – Specifies variable length columns are not padded with their maximum length to the index key. The length of variable length columns stored along with the key.

PADDED - Specifies variable length columns are padded with their maximum length to the index key.

Define:

Specifies about underlying datasets for the index physically created. The valid values are YES and NO. If YES, the datasets will be created when index created. If NO, the datasets will not be created until the data inserted into the table.

Compress:

Specifies that the index needs to be compressed or not after creation. The valid values are YES and NO. If YES, index will be compressed. If NO, index will not be compressed. NO is the default value, if not specified.

Partition By:

Specifies the partition of index will be used for the table. Specifies the range of the partition.

Bufferpool:

Specifies the buffer pool used of index.

Close:

Specifies the data set can be closed when the indexes are not used or maximum open datasets can be reached. The valid values are YES and NO.If YES, the dataset is eligible for closing. If NO, the dataset is not eligible for closing.

Defer:

Specifies whether the index is build during the CREATE INDEX execution. The valid values are YES and NO. If YES, the index is not built during the CREATE INDEX execution. If NO, the index is built during CREATE INDEX execution. The default value is NO.

Piecesize:

Specifies the maximum memory for the data set of non-partitioned index.

K - Specifies in Kilo bytes.

M - Specifies in Mega bytes.

G - Specifies in Giga bytes.

Copy:

Specifies whether the copy utility allows for the index. The valid values are YES and NO.

Index Types:

There are two types of indexes in the current system.

• Clustered index
• Non-clustered index

Clustered Index:

The clustered index is defined on the table, then the rows will group together which have the similar index key. This will happen during the insertion. So the access will be a bit faster than other index.

Non-clustered index:

This index is default index. The rows will be inserted where the space exists and in the order how it is inserted.