Creating Db2 indexes

When you define an index, Db2 builds and maintains an ordered set of pointers to rows of a base table or an auxiliary table.

Before you begin

Before you define an index, you need to define the table.

Procedure

Begin general-use programming interface information. To create a partitioning index, or a secondary index, and an index space at the current server:

Issue the CREATE INDEX statement, and specify an index key.

Example

The following example creates a unique index on the EMPPROJACT table. A composite key is defined on two columns, PROJNO and STDATE.

 CREATE UNIQUE INDEX XPROJAC1
  ON EMPPROJACT 
	(PROJNO ASC, 
	STDATE ASC)
INCLUDE(EMPNO, FIRSTNAME)

The INCLUDE clause, which is applicable only on unique indexes, specifies additional columns that you want appended to the set of index key columns. Any columns that are included with this clause are not used to enforce uniqueness. These included columns might improve the performance of some queries through index only access. Using this option might eliminate the need to access data pages for more queries and might eliminate redundant indexes.

If you issue SELECT PROJNO, STDATE, EMPNO, and FIRSTNAME to the table on which this index resides, all of the required data can be retrieved from the index without reading data pages. This option might improve performance.

End general-use programming interface information.