An index is a lookup table that optimizes the searching of data. An index defined on any table can increase the query speed. The index can be built up on any column of the table and DB2 will generate a logical structure at backend. This will facilitate the search on the table using indexed columns effectively.
An index can be unique and non-unique. To create a new unique index at any table we can use below command.
CREATE UNIQUE INDEX ENROTAB1 ON TAB1 (ENROLLMENT_ID) ASC;
The CREATE UNIQUE INDEX reserved words are followed by the name of the unique index which we want to create.
The ON parameter is followed by the name of the table on which index has to be built and this is followed by the name of the column in that table.
We have an option to organize the data within the index in ascending or descending order. Accordingly, we can use parameter ACS for ascending and DESC for descending.