DB2 index syntax

The syntax of the DB2 index is the basic knowledge of the DB2 database. The following describes the syntax of the DB2 index in detail, hoping to help you learn the syntax of the DB2 index.

CREATE INDEX
The create index statement is used to CREATE an INDEX for the DB2 Everyplace table.

Call

This statement can be used in applications that use the DB2 CLI function or by CLP.

Syntax

>-CREATE--INDEX -- index-name -- ON -- table-name -------------------->

.-,-----------------------------------------.
V.-ASC --. |
> -- (---- +-Column-name ----------------- + -- + ------ +-+ --) ---------> <
+-UCASE -- (-- | expression | --)-+ '-DESC -'
'-LCASE -- (-- | expression | --)-'

Description

INDEX index-name
Name the index.
ON table-name
Table-name: name of the table to be indexed.
Column-name
For an index, the column name identifies the column to be part of the index key.
Each column name must be an unlimited name that identifies a table column. Use eight or fewer columns; do not repeat the column name SQLSTATE 42711 ).

Each specified column must be within 1024 bytes.

ASC
Sort index entries in ascending order by column. This is the default value.

DESC
Sort index entries in descending order by column.
LCASE/UCASE
The LCASE or LOWER function returns a string in which all SBCS characters have been converted to lowercase characters. That is, characters A to Z will be converted to characters a to z, while characters with distinguished characters will be converted to lower case equivalent items if they exist ).

The independent variable must be an expression whose value is CHAR or VARCHAR data type.

The data type and length attributes of the result of this function are the same as those of the independent variable. If the independent variable can be empty, the result can be empty. If the independent variable is empty, the result is null.

Make sure that the characters in the column JOB Value in the EMPLOYEE table are returned in lowercase. For example:

Select lcase (JOB)
FROM EMPLOYEE
Where empno = '000000 ';

Rules

A maximum of 15 indexes can be created for tables without a primary key. A maximum of 14 indexes can be created for tables with primary keys.
If you try to CREATE an INDEX that matches the existing INDEX, the create index statement fails. Two index descriptions are considered repeated in the following cases:

The column sets and their sequence in the index are the same as those in the existing index.
The sorting attributes are the same.
Columns with BLOB data types cannot be used in the create index statement.
Notes

The create index statement can contain a maximum of eight columns.
DB2 Everyplace supports two-way indexing scanning. Although the definitions of the following two indexes are different, their functions are the same.
Create index IDX1 on employee (job asc)
Create index IDX1 on employee (job desc)

Generally, you should create an index without specifying the sorting direction. Generally, the less the index, the lower the index maintenance cost.
DB2 Everyplace supports index prefix scanning. Consider the following example. The following indexes are created.

Create index J1 on t (A, B, C, D, E, F, G, K)

You do not need to create another index for T (A, B, C, D.
If the table does not contain data, create index creates an INDEX description. when inserting data into the table, CREATE an INDEX entry.
To create an index for a dirty index, use the following example:

Create index <index name>
ON <table name>
($ Dirty)

For more information about dirty bits, see ***.

Example

Create an index named JOB_BY_DPT for the EMPLOYEE table. Jobs in each department's WORKDEPT) are sorted in ascending order.

Create index JOB_BY_DPT
On employee (WORKDEPT, JOB)

You can specify the index tablespace when creating a table, for example, create table tabname (...) in tabspace index in tabindexspace.

DB2 rename a table and view table information

DB2 database commands

Four file formats for DB2 data movement

Implementation of DB2 column-to-row

DB2 Incremental backup method