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)
This prevents the table from containing two or more rows with the same value of the index key.
Specifies how varying-length string columns are to be stored in the index.
Specifies how often to leave a page of free space when index entries are created.
Determines the percentage of free space to be left in each non-leaf page and leaf page when entries are added to the index.
Specifies when the underlying data sets for the index are physically created.
Specifies whether compression for index data will be used.
Specifies the index is the partitioning index.
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.
Identifies the buffer pool that is to be used for the index.
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.
Indicates whether the index is built during the execution of the CREATE INDEX statement.
Specifies the maximum addressability of each data set for an index.
Indicates whether the COPY utility is allowed for the index.
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);
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;
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.
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:
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!