DB2 - SQL Create Index Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

CREATE [UNIQUE] INDEX index-name
        ON table-name (column-name [ASC | DESC])
        [CLUSTER | NOT CLUSTER]
        [PARTITIONED]
        [PADDED | NOT PADDED]
        [using-specification]
        [free-specification]
        [DEFINE YES | NO]
        [COMPRESS YES | NO]
        [PARTITION BY RANGE partition-element
                      using-specification
                      free-specification]
        [BUFFERPOOL bp-name]
        [CLOSE YES | NO]
        [DEFER YES | NO]
        [PIECESIZE integer]
        [COPY YES | NO]


using-specification
        USING
              VCAT catalog-name |
              STOGROUP stogroup-name
                  PRIQTY integer
                  SECQTY integer
                  ERASE YES | NO

free-specification
        FREEPAGE integer
        PCTFREE integer

partition-element
        PARTITION integer
        ENDING AT (constant/MAXVALUE/MINVALUE)

UNIQUE:

This prevents the table from containing two or more rows with the same value of the index key.

ASC:

• Specifies that the index entries are to be kept in ascending order of the column values.
• This is the default setting.

DESC:

CLUSTER or NOT CLUSTER:

PARTITIONED:

PADDED or NOT PADDED:

Specifies how varying-length string columns are to be stored in the index.

• PADDED: Specifies that varying-length string columns within the index are always padded with the default pad character to their maximum length.

• NOT PADDED: Specifies that varying-length string columns are not to be padded to their maximum length in the index.

Using clause:

• For non-partitioned indexes, the USING clause indicates whether the data sets for the index are to be managed by the user or managed by DB2.

• VCAT: Specifies that the first data set for the index is managed by the user, and that following data sets, if needed, are also managed by the user.

• STOGROUP: Specifies that DB2 will define and manage the index data sets. PRIQTY: Specifies the minimum primary space allocation for the DB2 managed data set.

• SECQTY: Specifies the minimum secondary space allocation for the DB2 managed data set.

• ERASE: Indicates whether the DB2-managed data sets are to be erased when the index is deleted.

FREEPAGE:

Specifies how often to leave a page of free space when index entries are created.

PCTFREE:

Determines the percentage of free space to be left in each non-leaf page and leaf page when entries are added to the index.

DEFINE:

Specifies when the underlying data sets for the index are physically created.

• YES: The data sets are created when the index is created YES is the default.
• NO: The data sets are not created until data is inserted into the index.

COMPRESS:

Specifies whether compression for index data will be used.

• NO: Specifies that no index compression will be used. This is the default.
• YES: Specifies that index compression will be used.

PARTITION BY RANGE:

Specifies the index is the partitioning index.

• PARTITION integer: A PARTITION clause specifies the highest value of the index key in one partition of a partitioning index.

• ENDING AT: Specifies that this is the partitioning index and indicates how the data will be partitioned.

  CONSTANT: Specifies a constant value with a data type that must conform to the rules for assigning that value to the column.

  MAXVALUE: Specifies a value greater than the maximum value for the limit key of a partition boundary.

  MINVALUE: Specifies a value that is smaller than the minimum value for the limit key of a partition boundary.

BUFFERPOOL:

Identifies the buffer pool that is to be used for the index.

CLOSE:

Specifies whether or not the data set is eligible to be closed when the index is not being used and the limit on the number of open data sets is reached.

• YES: Eligible for closing. This is the default
• NO: Not eligible for closing.

DEFER:

Indicates whether the index is built during the execution of the CREATE INDEX statement.

• NO: The index is built. This is the default.
• YES: The index is not built.

PIECESIZE:

Specifies the maximum addressability of each data set for an index.

COPY:

Indicates whether the COPY utility is allowed for the index.

• NO: Does not allows full image or concurrent copies NO is the default.
• YES: Allows full image or concurrent copies.

Example 1:

Create an index on the column TAB1_COL2 of a table TB_TAB1.

CREATE INDEX IX_TAB1_COL2
      ON TB_TAB1(TAB1_COL2);

Create an index named IX_PROJECT_NAME on the TB_PROJECT table. The purpose of the index is to ensure that there are not two entries in the table with the same value for project name (PROJECT_NAME). The index entries are to be in ascending order.

CREATE UNIQUE INDEX IX_PROJECT_NAME
       ON TB_PROJECT(PROJECT_NAME);

Create an index named IX_EMPLOYE_JOB_DEPT on the TB_EMPLOYE table. Arrange the index entries in ascending order by job title (EMPLOYE_JOB) within each department (EMPLOYE_DEPT).

CREATE INDEX IX_EMPLOYE_JOB_DEPT
        ON TB_EMPLOYE (EMPLOYE_DEPT, EMPLOYE_JOB);

Example 2:

Create a unique index, named DSN8910.IX_DEPT, on table DSN8910.TB_DEPT. Index entries are to be in ascending order by the single column DEPT_NO. DB2 is to define the data sets for the index, using storage group DSN8G910.

Each data set should hold 1 megabyte of data at most. Use 512 kilobytes as the primary space allocation for each data set and 64 kilobytes as the secondary space allocation. Make the index padded.

The data sets can be closed when no one is using the index and do not need to be erased if the index is dropped.

CREATE UNIQUE INDEX DSN8910.IX_DEPT
        ON DSN8910.TB_DEPT
              (DEPT_NO ASC)
        PADDED
        USING STOGROUP DSN8G910
                       PRIQTY 512
                       SECQTY 64
                       ERASE NO
       BUFFERPOOL BP1
       CLOSE YES
       PIECESIZE 1M;

Explanation:

The underlying data sets for the index will be created immediately, which is the default (DEFINE YES). Assuming that table DSN8910.TB_DEPT is empty, if we wanted to defer the creation of the data sets until data is first inserted into the index, we would specify DEFINE NO instead of accepting the default behavior.

Specifying PADDED ensures that the varying-length character string columns in the index are padded with blanks.

Example 3:

Create a cluster index, named IX_EMP, on table TB_EMP in database DSN8910. Put the entries in ascending order by column EMP_NO. Let DB2 define the data sets for each partition using storage group DSN8G910. Make the primary space allocation be 36 kilobytes, and allow DB2 to use the default value for SECQTY. If the index is dropped, the data sets need not be erased.

There are to be 4 partitions, with index entries divided among them as follows:

• Partition 1: entries up to H99
• Partition 2: entries above H99 up to P99
• Partition 3: entries above P99 up to Z99
• Partition 4: entries above Z99

Associate the index with buffer pool BP1 and allow the data sets to be closed when no one is using the index. Enable the use of the COPY utility for full image or concurrent copies.

CREATE INDEX DSN8910.IX_EMP
        ON DSN8910.TB_EMP
            (EMP_NO ASC)
        USING
            STOGROUP DSN8G910
                PRIQTY 36
                ERASE NO
        CLUSTER
        PARTITION BY RANGE
            (PARTITION 1 ENDING AT('H99'),
             PARTITION 2 ENDING AT('P99'),
             PARTITION 3 ENDING AT('Z99'),
             PARTITION 4 ENDING AT('999'))
        BUFFERPOOL BP1
        CLOSE YES
        COPY YES;

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!