CREATE INDEX

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• The privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
• Administrative authority

The privileges held by the authorization ID of the statement must include at least one of the following:

• The following system authorities:
  • *USE to the Create Logical File (CRTLF) command
  • *CHANGE to the data dictionary if the library into which the index is created is an SQL schema with a data dictionary
• Administrative authority

The privileges held by the authorization ID of the statement must also include at least one of the following:

• For the referenced table:
  • The INDEX privilege on the table
  • The system authority *EXECUTE on the library containing the table
• Administrative authority

If SQL names are specified and a user profile exists that has the same name as the library into which the table is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:

• The system authority *ADD to the user profile with that name
• Administrative authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

• For each distinct type identified in the statement:
  • The USAGE privilege on the distinct type, and
  • The system authority *EXECUTE on the library containing the distinct type
• Administrative authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Description

UNIQUE

Prevents the table from containing two or more rows with the same value of the index key. When UNIQUE is used, all null values for a column are considered equal. For example, if the key is a single column that can contain null values, that column can contain only one null value. The constraint is enforced when rows of the table are updated or new rows are inserted.

The constraint is also checked during the execution of the CREATE INDEX statement. If the table already contains rows with duplicate key values, the index is not created.

UNIQUE WHERE NOT NULL

Prevents the table from containing two or more rows with the same value of the index key, where all null values for a column are not considered equal. Multiple null values in a column are allowed. Otherwise, this is identical to UNIQUE.

ENCODED VECTOR

Specifies that the resulting index will be an encoded vector index (EVI).

An encoded vector index cannot be used to ensure an ordering of rows. It is used by the database manager to improve the performance of queries. For more information, see the Database Performance and Query Optimization topic collection.

index-name

Names the index. The name, including the implicit or explicit qualifier, must not be the same as an index, table, view, alias, or file that already exists at the current server.

If SQL names were specified, the index will be created in the schema specified by the implicit or explicit qualifier.

If system names were specified, the index name will be created in the schema that is specified by the qualifier. If not qualified, the index name will be created in the same schema as the table over which the index is created.

If the index name is not a valid system name and the FOR SYSTEM NAME clause is not used, DB2® for i will generate a system name. For information about the rules for generating a name, see Rules for Table Name Generation.

FOR SYSTEM NAME system-object-identifier

Identifies the system-object-identifier of the index. system-object-identifier must not be the same as a table, view, alias, or index that already exists at the current server. The system-object-identifier must be an unqualified system identifier.

When system-object-identifier is specified, index-name must not be a valid system object name.

ON table-name

Identifies the table on which the index is to be created. The table-name must identify a base table (not a view) that exists at the current server.

If the table is a partitioned table, an alias may be specified which identifies a single partition. The created index will then only be created over the specified partition.

key-expression

Identifies a column or expression that will be part of the index key.

The number of keys defined for the index must not exceed 120, and the sum of their byte lengths must not exceed 32766-n, where n is the number of keys specified that allow nulls.

expression

If expression contains only a column-name, it must be an unqualified name that identifies a column of the table. The same column-name cannot be specified more than once if:

• a WHERE clause, INCLUDE clause, or RCDFMT clause is specified,
• an expression is defined as part of an index key, or
• a column is renamed using the AS clause.

If the expression is not a column name, the expression must not reference a column that contains a field procedure.

A column-name must not identify a LOB, XML, or DATALINK column, or a distinct type based on a LOB, XML, or DATALINK column. If the expression is not a column name, any intermediate result expression and the final result expression must not be a DATALINK, LOB, or XML data type. It must not contain any of the following:

• Subqueries
• Aggregate functions
• Variables
• Global variables
• Parameter markers
• Complex expressions that contain LOBs (such as concatenation)
• Special registers
• Sequence references
• OLAP specifications
• REGEXP_LIKE predicate
• ROW CHANGE expressions
• User-defined functions other than functions that were implicitly generated with the creation of a distinct type
• Any function that is not deterministic
• The following built-in scalar functions:

  ATAN2	DLURLCOMPLETE 1	LPAD	REPLACE
  CARDINALITY	DLURLPATH	MAX_CARDINALITY	ROUND_TIMESTAMP
  CONTAINS	DLURLPATHONLY	MONTHNAME	RPAD
  CURDATE	DLURLSCHEME	MONTHS_BETWEEN	SCORE
  CURTIME	DLURLSERVER	NEXT_DAY	SOUNDEX
  DATAPARTITIONNAME	DLVALUE	NOW	TIMESTAMP_FORMAT
  DATAPARTITIONNUM	ENCRYPT_AES	OVERLAY	TIMESTAMPDIFF
  DAYNAME	ENCRYPT_RC2	RAISE_ERROR	TRUNC_TIMESTAMP
  DBPARTITIONNAME	ENCRYPT_TDES	RAND	VARCHAR_FORMAT
  DECRYPT_BINARY	GENERATE_UNIQUE	REGEXP_COUNT	WEEK_ISO
  DECRYPT_BIT	GETHINT	REGEXP_INSTR	XMLPARSE
  DECRYPT_CHAR	IDENTITY_VAL_LOCAL	REGEXP_REPLACE	XMLVALIDATE
  DECRYPT_DB	INSERT	REGEXP_SUBSTR	XSLTRANSFORM
  DIFFERENCE	LOCATE_IN_STRING	REPEAT
  1 For DataLinks with an attribute of FILE LINK CONTROL and READ PERMISSION DB.

column-name

Names a column of the index. Do not use the same name for more than one column of the index or for a system-column-name of the index.

If the expression is not a column name and is not named, a name will be generated for the index key column. The name will be SQLIXxxxxx, where xxxxx is a number that makes the column name unique for the index.

FOR COLUMN system-column-name

Provides an IBM® i name for the column. Do not use the same name for more than one column of the index or for a column-name of the index.

If the system-column-name is not specified, and the column-name is not a valid system-column-name, a system column name is generated. The name will be SQLIXxxxxx, where xxxxx is a number that makes the column name unique for the index.

ASC

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

DESC

Specifies that the index entries are to be kept in descending order of the column values.

Ordering is performed in accordance with the comparison rules described in Assignments and comparisons. The null value is higher than all other values.

WHERE search-condition

Specifies the condition to apply for a row to be included in the index. The search-condition cannot contain a predicate with a subquery. It must not contain any of the items listed as restrictions for key-expression.

WITH integer DISTINCT VALUES

Specifies the estimated number of distinct key values. This clause may be specified for any type of index.

For encoded vector indexes this is used to determine the initial size of the codes assigned to each distinct key value. The default value is 256.

For non-encoded vector indexes, this clause is ignored.

PARTITIONED

Specifies that an index partition should be created for each data partition defined for the table using the specified columns. The table-name must identify a partitioned table. If the index is unique, the columns of the index must be the same or a superset of the columns of the data partition key. PARTITIONED is the default if the index is not unique and the table is partitioned.

NOT PARTITIONED

Specifies that a single index should be created that spans all of the data partitions defined for the table. The table-name must identify a partitioned table. NOT PARTITIONED is the default if the index is unique and the table is partitioned. An index on a table that is not partitioned is also by default not partitioned.

If an encoded vector index is specified, NOT PARTITIONED is not allowed.

PAGESIZE

Specifies the logical page used for the index in kilobytes. Indexes with larger logical page sizes are typically more efficient when scanned during query processing. Indexes with smaller logical page sizes are typically more efficient for simple index probes and individual key look ups.

The default value for PAGESIZE is determined by the length of the key and has a minimum value of 64.

If an encoded vector index is specified, PAGESIZE is not allowed.

INCLUDE

Specifies aggregate function expressions to be included in the index. These aggregates make it possible for the index to be used directly to return aggregate results for a query. INCLUDE is only allowed for an encoded vector index.

aggregate-function-name ( expression )

The aggregate function name must be one of the built-in functions AVG, COUNT, COUNT_BIG, SUM, STDDEV, STDDEV_SAMP, VARIANCE, or VARIANCE_SAMP. The expression argument of the aggregate function must not contain any of the items listed as restrictions for key-expression.

RCDFMT format-name

An unqualified name that designates the IBM i record format name of the index. A format-name is a system identifier.

If a record format name is not specified:

• If no key columns are expressions and no key columns have been explicitly named using the AS clause, the index will share the format of table-name.
• Otherwise, the index will not share the format of table-name and the format-name is the same as the system-object-name of the index.

If the INCLUDE keyword is specified, RCDFMT is not allowed.

ADD ALL COLUMNS

Specifies that all non-hidden columns of table-name will be added to the format for the index. All the columns will be defined in the same order as they appear in the format of table-name and will precede any expressions defined as index keys.

ADD KEYS ONLY

Specifies that only the columns specified as index key columns will be added to the format for the index. Other columns from table-name will not be added.

ADD column-name

Specifies that the listed columns will be added to the format for the index. The index key columns will be first, followed by the added columns.

media-preference

Specifies the preferred storage media for the index.

UNIT ANY

No storage media is preferred. Storage for the index will be allocated from any available storage media.

UNIT SSD

Solid state disk storage media is preferred. Storage for the index may be allocated from solid state disk storage media, if available.

Notes

Effects of the statement: CREATE INDEX creates a description of the index. If the named table already contains data, CREATE INDEX creates the index entries for it. If the table does not yet contain data, the index entries are created when data is inserted into the table.

Collating sequence: Any index created over columns containing SBCS or mixed data is created with the collating sequence in effect at the time the statement is executed. For collating sequences other than *HEX, the key for SBCS data or mixed data is the weighted value of the key based on the collating sequence.

Index attributes: An index is created as a keyed logical file. When an index is created, the file wait time and record wait time attributes are set to the default that is specified on the WAITFILE and WAITRCD keywords of the Create Logical File (CRTLF) command.

An index created over a distributed table is created on all of the servers across which the table is distributed. For more information about distributed tables, see DB2 Multisystem.

Index ownership: If SQL names were specified:

• If a user profile with the same name as the schema into which the index is created exists, the owner of the index is that user profile.
• Otherwise, the owner of the index is the user profile or group user profile of the job executing the statement.

If system names were specified, the owner of the index is the user profile or group user profile of the job executing the statement.

Index authority: If SQL names are used, indexes are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, indexes are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

If the owner of the index is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the index.