CREATE INDEX

The CREATE INDEX statement is used to create an index on a DB2 Everyplace table.

Invocation

This statement can be used in an application program using the DB2 CLI functions or issued through the CLP.

Syntax

>>-CREATE--INDEX--index-name--ON--table-name-------------------->
 
      .-,-----------------------------------------.
      V                                  .-ASC--. |
>--(----+-column-name-----------------+--+------+-+--)---------><
        +-UCASE--(--| expression |--)-+  '-DESC-'
        '-LCASE--(--| expression |--)-'

Description

INDEX index-name

Names the index.

ON table-name

The table-name names a table on which an index is to be created.

column-name

For an index, column name identifies a column that is to be part of the index key.

Each column name must be an unqualified name that identifies a column of the table. Use eight columns or fewer; the column names cannot be repeated (SQLSTATE 42711).

The length of each specified column must not be greater than 1024 bytes.

ASC: Puts the index entries in ascending order by the column. This is the default.
DESC: Puts the index entries in descending order by the column.

LCASE / UCASE

The LCASE or LOWER function returns a string in which all the SBCS characters have been converted to lowercase characters. That is, the characters A-Z will be translated to the characters a-z, and characters with diacritical marks will be translated to their lowercase equivalents if they exist.

The argument must be an expression whose value is a CHAR or VARCHAR data type.

The result of the function has the same data type and length attribute as the argument. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Ensure that the characters in the value of column JOB in the EMPLOYEE table are returned in lowercase characters. For example:

SELECT LCASE(JOB)
     FROM EMPLOYEE
     WHERE EMPNO = '000020';

Rules

A maximum of 15 indexes can be created on a table without a primary key. A maximum of 14 indexes can be created on a table with a primary key.
The CREATE INDEX statement will fail if attempting to create an index that matches an existing index. Two index descriptions are considered duplicates if:
- The set of columns and their order in the index is the same as that of an existing index.
- The ordering attributes are the same.
Columns with a BLOB data type cannot be used in a CREATE INDEX statement.

Notes

The CREATE INDEX statement can contain a maximum of 8 columns.
DB2 Everyplace supports bi-directional scanning of indexes. The following two indexes serve the same purpose although they have different definitions.
```
CREATE INDEX IDX1 ON EMPLOYEE (JOB ASC)
CREATE INDEX IDX1 ON EMPLOYEE (JOB DESC) 
```
In general, indexes should be created without specifying the order direction. Fewer indexes typically incurs lower index maintenance cost.
DB2 Everyplace supports prefix-scanning of indexes. Consider the following example. The following index is created.
```
CREATE INDEX J1 ON T (A, B, C, D, E, F, G, K)
```
There is no need to create another index on T (A,B,C,D).
If the table does not contain data, CREATE INDEX creates a description of the index; the index entries are created when data is inserted into the table.
To create an index for the dirty bit index, use the following example:
```
CREATE INDEX <index name>
  ON <table name>
  ($dirty) 
```
See *** for more information about the dirty bit.

Example

Create an index named JOB_BY_DPT on the EMPLOYEE table. Arrange the index entries in ascending order by job title (JOB) within each department (WORKDEPT).

CREATE INDEX JOB_BY_DPT 
ON EMPLOYEE (WORKDEPT, JOB)

Related reference