CREATE INDEX statement

Invocation for CREATE INDEX

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for CREATE INDEX

The privilege set that is defined below must include at least one of the following:

• The INDEX privilege on the table
• Ownership of the table
• DBADM authority for the database that contains the table
• SYSADM or SYSCTRL authority
• System DBADM
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

If the index is created using an expression, the EXECUTE privilege is required on any user-defined function that is invoked in the index expression.

Additional privileges might be required, as explained in the description of the BUFFERPOOL and USING STOGROUP clauses.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the specified index name includes a qualifier that is not the same as this owner, the privilege set must include SYSADM or SYSCTRL authority, or DBADM or DBCTRL authority for the database.

If ROLE AS OBJECT OWNER is in effect, the schema qualifier must be the same as the role, unless the role has the CREATEIN privilege on the schema, SYSADM authority, or SYSCTRL authority.

If ROLE AS OBJECT OWNER is not in effect, one of the following rules applies:

• If the privilege set lacks the CREATEIN privilege on the schema, SYSADM authority, or SYSCTRL authority, the schema qualifier (implicit or explicit) must be the same as one of the authorization ids of the process.
• If the privilege set includes SYSADM authority or SYSCTRL authority, the schema qualifier can be any valid schema name.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process unless the process is within a trusted context and the ROLE AS OBJECT OWNER clause is specified. In that case, the privilege set is the set of privileges that are held by the role that is associated with the primary authorization ID of the process. However, if the specified index name includes a qualifier that is not the same as this authorization ID, the following rules apply:

• If the privilege set includes SYSADM or SYSCTRL authority (or DBADM authority for the database, or DBCTRL authority for the database when creating a table), the schema qualifier can be any valid schema name.
• If the privilege set lacks SYSADM or SYSCTRL authority (or DBADM authority for the database, or DBCTRL authority for the database when creating a table), the schema qualifier is valid only if it is the same as one of the authorization IDs of the process and the privilege set that are held by that authorization ID includes all privileges needed to create the index. This is an exception to the rule that the privilege set is the privileges that are held by the SQL authorization ID of the process.

Description for CREATE INDEX

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 are allowed. Otherwise, this is identical to UNIQUE.

INDEX index-name

Names the index. The name must not identify an index that exists at the current server, or is listed in the SYSIBM.SYSPENDINGOBJECTS catalog table, or is in an accelerator-only table.

The associated index space also has a name. That name appears as a qualifier in the names of data sets defined for the index. If the data sets are managed by the user, the name is the same as the second (or only) part of index-name. If this identifier consists of more than eight characters, only the first eight are used. The name of the index space must be unique among the names of the index spaces and table spaces of the database for the identified table. If the data sets are defined by Db2, Db2 derives a unique name.

If the index is an index on a declared temporary table, the qualifier, if explicitly specified, must be SESSION. If the index name is unqualified, Db2 uses SESSION as the implicit qualifier.

For more information, see Index names and guidelines.

ON table-name or aux-table-name

Identifies the table on which the index is created. The name can identify a base table, a materialized query table, a declared temporary table, or an auxiliary table.

table-name

Identifies the base table, materialized query table, or declared temporary table on which the index is created. The name must identify a table that exists at the current server. (The name of a declared temporary table must be qualified with SESSION.)

The name must not identify a clone table. The name must not identify a created temporary table or a table that is implicitly created for an XML column. If the index that is being created is for XML values, the table can contain an XML column, otherwise, the table must not contain an XML column. The name must not identify a catalog table or declared temporary table if the index is created using expressions. The name must not identify an accelerator-only table or a directory table.

If the table has enforced row or column access controls, the row permissions and column masks are not applied during key generation.

column-name,…

Specifies the columns of the index key.

Each column-name must identify a column of the table. Do not specify more than 64 columns or the same column more than one time. Do not qualify column-name.

Do not specify a column for column-name that is defined as follows:

• a LOB column (or a column with a distinct type that is based on a LOB data type)
• a BINARY or VARBINARY column (or a column with a distinct type that is based on a BINARY or VARBINARY data type) when the PARTITION BY RANGE clause is also specified
• a VARBINARY column (or a column with a distinct type that is based on a VARBINARY data type) when the PADDED clause is also specified
• a row change timestamp column when the PARTITION BY RANGE or PARTITIONED clause is also specified.
• a timestamp with time zone column (or a column with a distinct type that is based on the timestamp with time zone data type) when the PARTITION or PARTITION BY RANGE clause is also specified.

A column with an XML type can only be specified if the XMLPATTERN clause is also specified. If the XMLPATTERN clause is specified, only one column can be identified and the column must be an XML type. The resulting index is an XML index.

If the table is an EBCDIC table with Unicode columns, character and graphic columns that are specified for the index key must be all EBCDIC or all Unicode.

The sum of the length attributes of the columns must not be greater than the following limits, where n is the number of columns that can contain null values, m is the number of varying-length columns, and d is the number of DECFLOAT columns in the key:

• 2000 - n for a padded, nonpartitioning index
• 2000 - n - 2m - 3d for a nonpadded, nonpartitioning index
• 255 - n for a partitioning index (padded or nonpadded)
• 255 - n - 2m- 3d for a nonpadded, partitioning index

key-expression

Specifies an expression that returns a scalar value. An index with a key that includes one or more expressions consisting of more than just a column name is an expression-based index. key-expression cannot be specified with the GENERATE KEY USING clause or the INCLUDE clause. key-expression has the following restrictions:

• Each key-expression must contain as least one reference to a column of table-name.

  All references to columns of table-name must be unqualified. Referenced columns cannot include any FIELDPROCs or a SECURITY LABEL. Referenced columns cannot be implicitly hidden (that is, defined with the IMPLICITLY HIDDEN attribute).

• key-expression must not include any of the following:
  • A subquery
  • An aggregate function
  • A function that is not deterministic function
  • A function that has an external action
  • A user-defined function
  • The VERIFY_GROUP_FOR_USER or VERIFY_ROLE_FOR_USER functions
  • A sequence reference
  • A host variable
  • A parameter marker
  • A global variable
  • A special register
  • An expression for which implicit time zone value apply (or example, cast a timestamp to a timestamp with time zone)
  • A CASE expression
  • An OLAP specification
• If key-expression invokes a cast function, the privilege set must implicitly include EXECUTE authority on the generated cast functions for the distinct type.
• If key-expression invokes the LOWER or UPPER functions, the input string-expression cannot be FOR BIT DATA, and the function invocation must contain the locale-name argument.
• If key-expression invokes the TRANSLATE function, the function invocation must contain the to-string argument.
• key-expression must not invoke a built-in function with an argument that references a LOB column, unless the function is SUBSTR or JSON_VAL.
• If key-expression invokes the SUBSTR function, an argument to the function that references a LOB column can reference only the inline portion of the LOB column.
• If key-expression invokes the JSON_VAL function and the first argument is a LOB column, the column must be defined as an inline LOB.
• If key-expression invokes the JSON_VAL function, the function invocation must meet the following conditions:
  • The invocation of the JSON_VAL function must be the outermost expression for key-expression.
  • If the first argument is a column, that column must be contained in a table in a partition-by-growth table space.
  • The third argument must end with the string ':na', to indicate that the first argument does not contain a JSON array.
  
• If key-expression invokes the JSON_VAL built-in function, the CREATE INDEX statement must not reference any LOB columns other than the LOB column that is the argument to the JSON_VAL function. Such a CREATE INDEX statement can refer only to a single LOB column.
• The same expression cannot be used more than one time in the same index.
• The data type of the result of the expression cannot be a LOB, XML, DECFLOAT, or array value. However, the data type of an intermediate result can be a LOB or DECFLOAT value (or a distinct type that is based on one of these data types), but not an XML value. For an index with a DECFLOAT intermediate result, the rounding mode that was in effect when the index was created should also be in effect when the index is used.
• If a Unicode column in an EBCDIC table is referenced in a key-expression, the encoding scheme of the index keys must either be all Unicode or all EBCDIC. Otherwise, the encoding scheme of the result of a key-expression must be the same encoding scheme as the table.

The maximum length of the text string of each key-expression is 4000 bytes after conversion to UTF-8. The maximum number of key-expression in an extended index is 64.

ASC

Puts the index entries in ascending order by the column. ASC cannot be specified with the GENERATE KEY USING clause.

ASC is the default.

DESC

Puts the index entries in descending order by the column. DESC cannot be specified with the GENERATE KEY USING clause or if the ON clause contains key-expression.

RANDOM

Index entries are put in a random order by the column. RANDOM cannot be specified in the following cases:

• A varying length column is part of the index key and the index is defined with the NOT PADDED option
• A column of the index key is defined as TIMESTAMP WITH TIME ZONE or DECFLOAT
• The index is an XML index. An XML index is defined with the GENERATE KEY USING clause
• The index is part of the partitioning key
• The index is an expression-based index

BUSINESS_TIME

Specifies that the columns of the BUSINESS_TIME period are automatically added to the end of the index key in the following order:

• The end column of the BUSINESS_TIME period in ascending order
• The start column of the BUSINESS_TIME period in ascending order

BUSINESS_TIME can be specified as the last item in the list. The list must also include at least one column-name or key-expression. When BUSINESS_TIME is specified, the columns of the BUSINESS_TIME period must not be specified as column-name or a key-expression, or as columns in the partitioning key.

WITH OR WITHOUT OVERLAPS

Indicates whether multiple rows may exist with the same values for the non-period columns and expressions of the index key for a row, with overlapping time periods.

WITH OVERLAPS

Indicates that multiple rows may exist with the same values for the non-period columns and expressions of the index key for a row, with overlapping time periods. The BUSINESS_TIME WITH OVERLAPS clause is intended for use in defining an index for the foreign key of a temporal referential constraint.

BUSINESS_TIME WITH OVERLAPS must not be specified when UNIQUE is specified for the index definition.

BUSINESS_TIME WITHOUT OVERLAPS must not be specified if the table is defined with a partitioning key that includes any columns of the BUSINESS_TIME period.

WITHOUT OVERLAPS

Indicates that the values for the non-period columns and expressions of the index key for a row must be unique with respect to the time represented by the BUSINESS_TIME period for the row. Db2 enforces that multiple rows do not exist with the same key values for the columns or expressions of the index, with overlapping time periods. The BUSINESS_TIME WITHOUT OVERLAPS clause is intended for use in defining a unique index to enforce a primary key or unique constraint.

BUSINESS_TIME WITHOUT OVERLAPS can only be specified for an index that is defined as UNIQUE.

aux-table-name

Identifies the auxiliary table on which the index is created. The name must identify an auxiliary table that exists at the current server. If the auxiliary table already has an index, do not create another one. An auxiliary table can only have one index.

Do not specify any columns for the index key. The key value is implicitly defined as a unique 19 byte value that is system generated.

If qualified, table-name or aux-table-name can be a two-part or three-part name. If a three-part name is used, the first part must match the value of the field Db2 LOCATION NAME of installation panel DSNTIPR at the current server. (If the current server is not the local Db2, this name is not necessarily the name in the CURRENT SERVER special register.) Whether the name is two-part or three-part, the authorization ID that qualifies the name is the owner of the index.

The table space that contains the named table must be available to Db2 so that its data sets can be opened. If the table space is EA-enabled, the data sets for the index must be defined to belong to a DFSMS data class that has the extended format and addressability attributes.

GENERATE KEY USING

Along with XMLPATTERN, GENERATE KEY USING is required to generate an XML index.

XMLPATTERN

When an XML column is indexed, only parts of the documents will be indexed. To identify those parts, a path expression that follows the XMLPATTERN clause is specified. Only values of those element, attribute, or text nodes which match the specified pattern are indexed. An XML pattern can be specified using an optional namespace declaration where namespace prefixes are mapped to namespace URIs and by providing a path expression. The path expression is similar to a path expression in XQuery except that the paths that are specified for the XML index can support child axis, self-or-descendant axis, wildcard expressions, or attribute only. The maximum length of an XML pattern text is 4000 bytes after being converted to UTF-8. For more information about XQuery, see Overview of pureXML.

prolog

To use qualified names in the pattern-expression, namespace prefixes need to be declared. A default namespace can also be declared for use with unqualified names.

declare namespace NCName=StringLiteral

The namespace prefix, NCName, is mapped to a namespace URI that is identified in StringLiteral. Multiple namespaces can be declared, but each namespace prefix must be unique within the list of namespace declarations. NCName is an XML name as defined by the XML 1.0 standard. NCName cannot include a colon character. The namespace URI cannot be http://www.w3.org/XML/1998/namespace or http://w3.org/2000/xmlns/.

declare default element namespace StringLiteral

Specifies the default namespace URI for unqualified names of elements and types. StringLiteral is a namespace URI. If no default element namespace is declared, unqualified names of element and types are in no namespace. Only one default namespace can be declared.

pattern-expression

Pattern-expression is used to identify those nodes in an XML document that are indexed. Pattern-expression cannot be an empty or invalid string, and the XQuery expression cannot be nested more than 50 levels. pattern-expression cannot be an XQuery updating expression.

/ (forward slash)

Separates path expression steps.

// (double forward slash)

Abbreviated syntax for /descendant-or-self::node()/

. (dot)

Abbreviated syntax for /self::node()/

child::

Specifies children of the context node. child:: is the default if no forward axis is specified.

descendant::

Specifies the descendants of the context node.

self::

Specifies the current context node.

descendant-or-self::

Specifies the context node and the descendents of the context node.

element-name

Identifies an element in an XML document. element-name is an XML QName that can have one of the following forms:

nsprefix:NCName

nsprefix explicitly specifies a namespace prefix that must be declared.

NCName

An unqualified XML name that uses the default namespace.

* (an asterisk)

Indicates any element name. If * is prefixed by attribute:: or @, * indicates any attribute name.

nsprefix:*

Indicates any NCName within the specified namespace.

*:NCName

Indicates a specific XML name in any of the currently declared namespaces.

attribute:: or @

Specifies attributes of the context node.

attribute-name

Identifies an attribute in an XML document. attribute-name is an XML QName that can have one of the following forms:

nsprefix:NCName

nsprefix explicitly specifies a namespace prefix that must be declared.

NCName

An unqualified XML name that uses the default namespace.

text( )

Matches any text node.

fn:upper-case(.)

Specifies an element node or an attribute node that identifies the key value for the index for each node that is specified by the context step (the part of pattern-expression that is specified prior to fn:upper-case).

The context step of fn:upper-case() must specify an element node or an attribute node. The argument of fn:upper-case() must be a self step. The key values of an XML value index must be specified as the SQL data type VARCHAR. The length of the VARCHAR value can be any value that is allowed in Db2.

fn:exists()

Specifies an element node that identifies the key value for the index for each node that is specified by the context step (the part of pattern-expression that is specified prior to fn:exists).

The context step of fn:exists() must specify an element node. The argument of fn:exists() must be either a single step of a child element node or an attribute node. The name test part can be a wildcard character for either the namespace prefix or NCName. The key values of an XML value index for an XPath expression that ends with fn:exists() must be specified as the SQL data type VARCHAR(1). The key value will be "T" or "F". "T" implies that fn:exists() evaluates to true and "F" implies that fn:exists() evaluates to false.

AS SQL data-type

Specifies that indexed values are stored as an instance of the specified SQL data type. Casting to the specified data type can result in a loss of precision of the values. For example, a loss of precision can occur when an XML integer value is cast to the SQL data type DECFLOAT. If the cast causes a loss of precision, the result will be rounded to the approximate value when it is stored in the index. The cast result cannot be outside of the range that is supported by the SQL data type. If the value cannot be cast to the specified data type, the document is still inserted into the table, but the index entry for that value is not created. No error or warning code is returned.

If the index is unique, the uniqueness is enforced on the value after it is cast to the specified type. Because rounding can occur during the cast to the SQL data type, if a value is cast to the same key value as a document that the table already contains, Db2 will return duplicate key errors at insert time, or fail to create the index.

VARCHAR (integer)

The length integer is a value in the range 1–1000 bytes. If VARCHAR is specified with a length, the specified length is treated as a constraint. If documents are inserted into a table (or exist in the table at create index time) that have nodes with values that are longer than the specified length, the insert or index creation will fail.

DECFLOAT

DECFLOAT can be specified to index numeric values. For the cast to succeed, the string must be a valid XML numeric type. Otherwise the value will be ignored and no insert to the index will occur. The result of the cast cannot be outside of the range that DECFLOAT can represent. Because the XML Schema data type for numeric values allows greater precision than the SQL data type, the result might be rounded to fit into the SQL data type. The DECFLOAT values that are stored in the index are the normalized numeric values.

DATE

The SQL DATE data type values will be normalized to UTC (Coordinated Universal Time) before being stored in the index. For invalid xs:date values, the value will be ignored without being inserted into the index. The XML schema data type for DATE allows for greater precision than the SQL data type. If an out-of-range value is encountered, an error is returned.

TIMESTAMP (12)

The SQL TIMESTAMP data type values will be normalized to UTC (Coordinated Universal Time) before being stored in the index. If the value that is specified in the document does not specify the time zone, Db2 will use the implicit time zone to normalize the value to UTC. For invalid xs:dateTime values, the value will be ignored without being inserted into the index. The XML schema data type for timestamps allows for greater precision than the SQL data type. If an out-of range value is encountered, an error is returned. Only a precision of 12 fractional digits is allowed for an SQL TIMESTAMP index key.

INCLUDE (column-name)

Specifies additional columns to append to the set of index key columns of a unique index. Any column that is specified using INCLUDE column-name is not used to enforce uniqueness. The included columns might improve performance for some queries using index only access.

The UNIQUE clause must be specified when INCLUDE is specified. Columns that are specified in the INCLUDE clause count towards the limits for the number of columns and the limits on the sum of the length attributes of the columns that are specified in the index. The total number of columns for the index cannot exceed 64.

column-name must be distinct from the columns that are used to enforce uniqueness and from other columns specified in the INCLUDE clause. column-name must be unqualified, must identify a column of the specified table, and must not be one of the existing columns of the index. column-name must not identify a LOB or DECFLOAT column (or a distinct type that is based on one of those types).

The INCLUDE clause cannot be specified for the following types of indexes:

• A non-unique index
• A partitioning index when index-controlled partitioning is used
• An auxiliary index
• An XML index
• An extended index
• An expression-based index

Columns in the INCLUDE list that are defined as character or graphic string data types must be defined with the same encoding scheme as other key columns with character or graphic string data types.

CLUSTER or NOT CLUSTER

Specifies whether the index is the clustering index for the table. This clause must not be specified for an index on an auxiliary table, or on a table that is defined to use hash organization.

CLUSTER

The index is to be used as the clustering index of the table. CLUSTER cannot be specified if XMLPATTERN or key-expression is specified.

NOT CLUSTER

The index is not to be used as the clustering index of the table.

PARTITIONED

Specifies that the index is data partitioned (that is, partitioned according to the partitioning scheme of the underlying data). A partitioned index can be created only on a partitioned table space, not on a partition-by-growth table space. PARTITIONED cannot be specified if XMLPATTERN is specified. The types of partitioned indexes are partitioning and secondary.

An index is considered a partitioning index if the specified index key columns match or comprise a superset of the columns specified in the partitioning key, are in the same order, and have the same ascending or descending attributes.

If PARTITION BY was not specified when the table was created, the CREATE INDEX statement must have the ENDING AT clause specified to define a partitioning index and use index-controlled partitioning. This index is created as a partitioned index even if the PARTITIONED keyword is not specified. When a partitioning index is created, if both the PARTITIONED and ENDING AT keywords are omitted, the index will be non-partitioned. If PARTITIONED is specified, the USING specification with PRIQTY and SECQTY specifications are optional. If these space parameters are not specified, default values are used.

A secondary index is any index defined on a partitioned table space that does not meet the definition of the partitioning index. For partitioned secondary indexes (data-partitioned secondary indexes), the ENDING AT clause is not allowed because the partitioning scheme of the index is predetermined by that of the underlying data. UNIQUE and UNIQUE WHERE NOT NULL are allowed only if the columns in the index are a superset of the partitioning columns. All of the index columns must be specified in a table-name(column-name) clause, and not in an INCLUDE clause. If a partitioned secondary index is created on a table that uses index-controlled partitioning, the table is converted to use table-controlled partitioning.

Index-controlled partitioning cannot be used if the PREVENT_NEW_IXCTRL_PART subsystem parameter is set to YES.

For more information, see PREVENT_NEW_IXCTRL_PART in macro DSN6SPRM .

NOT PADDED or PADDED

Specifies how varying-length string columns are to be stored in the index. If the index contains no varying-length columns, this option is ignored, and a warning message is returned. Indexes that do not have varying-length string columns are always created as physically padded indexes.

NOT PADDED

Specifies that varying-length string columns are not to be padded to their maximum length in the index. The length information for a varying-length column is stored with the key.

NOT PADDED is ignored and has no effect if the index is being created on an auxiliary table. Indexes on auxiliary tables are always padded.

PADDED

Specifies that varying-length string columns within the index are always padded with the default pad character to their maximum length. PADDED cannot be specified if XMLPATTERN is specified. PADDED cannot be specified for indexes that are defined on VARBINARY columns.

When the index contains at least one varying-length column, the default for the option depends on the value of field PAD INDEXES BY DEFAULT on installation panel DSNTIPE:

• When the value of this field is NO, new indexes are not padded unless PADDED is specified.
• When the value of this field is YES, new indexes are padded unless NOT PADDED is specified.

USING (for non-partitioned indexes)

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. If Db2 definition is specified, the clause also gives space allocation parameters (PRIQTY and SECQTY) and an erase rule (ERASE).

If you omit USING, the data sets Db2 manages on volumes listed in the default storage group of the database that is associated with the table. The default storage group for the database must exist. With no USING clause, PRIQTY, SECQTY, and ERASE assume their default values.

VCAT catalog-name

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.

The data sets are VSAM linear data sets cataloged in the integrated catalog facility catalog that catalog-name identifies. For more information about catalog-name values, see Naming conventions in SQL.

More than one Db2 subsystem can share the integrated catalog facility catalogs with the current server. To avoid the chance of those subsystems attempting to assign the same name to different data sets, specify a catalog-name value that is not used by the other Db2 subsystems.

Do not specify VCAT in any of the following circumstances:

• For an index on a declared temporary table.
• If the table space is partition-by-growth, and the table space is not part of the Db2 catalog.

STOGROUP stogroup-name

Specifies that Db2 will define and manage the data sets for the index. Each data set will be defined on a volume listed in the identified storage group. The values specified (or the defaults) for PRIQTY and SECQTY determine the primary and secondary allocations for the data set. If PRIQTY+118×SECQTY is 2 gigabytes or greater, more than one data set could eventually be used, but only the first is defined during execution of this statement.

To use USING STOGROUP, the privilege set must include one of the following, except when creating an index on a declare global temporary table if stogroup-name matches the default storage group of the work file database:

• SYSADM authority
• SYSCTRL authority
• The USE privilege for that storage group

Moreover, stogroup-name must identify a storage group that exists at the current server and includes in its description at least one volume serial number. The description can indicate that the choice of volumes will be left to Storage Management Subsystem (SMS). Each volume specified in the storage group must be accessible to z/OS® for dynamic allocation of the data set, and all these volumes must be of the same device type.

The integrated catalog facility catalog used for the storage group must not contain an entry for the first data set of the index. If the catalog is password protected, the description of the storage group must include a valid password.

The storage group supplies the data set name. The first level qualifier is also the name of, or an alias for, the integrated catalog facility catalog on which the data set is to be cataloged. The naming convention for the data set is the same as if the data set is managed by the user.

PRIQTY integer

Specifies the minimum primary space allocation for a Db2-managed data set. integer must be a positive integer, or -1. When you specify PRIQTY with a positive integer value, the primary space allocation is at least n kilobytes, where n is:

  12

If integer is greater than 0 and less than 12.

  integer

If integer is in the range 12–4194304.

  2097152

If both of the following conditions are true:

• integer is greater than 2097152.
• The index is a non-partitioned index on a table space that is not defined with the LARGE or DSSIZE attribute.

  4194304

If integer is greater than 4194304.

If you do not specify PRIQTY, or you specify a PRIQTY value of -1, Db2 uses a default value for the primary space allocation. For information on how Db2 determines the default value, see Rules for primary and secondary space allocation.

If you specify PRIQTY, and do not specify a value of -1, Db2 specifies the primary space allocation to access method services using the smallest multiple of 4KB not less than n. The allocated space can be greater than the amount of space requested by Db2. For example, it could be the smallest number of tracks that will accommodate the space requested. To more closely estimate the actual amount of storage, see DEFINE CLUSTER command.

When determining a suitable value for PRIQTY, be aware that two of the pages of the primary space could be used by Db2 for purposes other than storing index entries.

SECQTY integer

Specifies the minimum secondary space allocation for a Db2-managed data set. integer must be a positive integer, 0, or -1. If you do not specify SECQTY, or specify a SECQTY value of -1, Db2 uses a formula to determine a value. For information on the actual value that is used for secondary space allocation, whether you specify a value or not, see Rules for primary and secondary space allocation.

If you specify SECQTY, and do not specify a value of -1, Db2 specifies the secondary space allocation to access method services using the smallest multiple of 4KB not less than integer. The allocated space can be greater than the amount of space requested by Db2. For example, it could be the smallest number of tracks that will accommodate the space requested. To more closely estimate the actual amount of storage, see DEFINE CLUSTER command.

ERASE

Indicates whether the Db2-managed data sets are to be erased when they are deleted during the execution of a utility or an SQL statement that drops the index.

NO

Does not erase the data sets. Operations involving data set deletion will perform better than ERASE YES. However, the data is still accessible, though not through Db2. This is the default.

YES

Erases the data sets. As a security measure, Db2 overwrites all data in the data sets with zeros before they are deleted.

USING (partitioned indexes)

If the index is partitioned, there is a PARTITION clause for each partition. Within a PARTITION clause, a USING clause is optional. If a USING clause is present, it applies to that partition in the same way that a USING clause for a secondary index applies to the entire index.

When a USING specification is absent from a PARTITION clause, the USING clause parameters for the partition depend on whether a USING clause is specified before the PARTITION clauses.

• If the USING clause is specified, it applies to every PARTITION clause that does not include a USING clause.
• If the USING clause is not specified, the following defaults apply to the partition:
  • Data sets are managed by Db2.
  • The default storage group for the database is used. If the USING clause for the index space is omitted, the default storage group for database must exist.
  • Default values of -1 are used for both PRIQTY and SECQTY.
  • A value of NO is used for ERASE.

VCAT catalog-name

Specifies a user-managed data set with a name that starts with the specified catalog name. The identified integrated catalog facility catalog must already contain an entry for the nth data set of the index, where n is the partition number.

The data sets are VSAM linear data sets cataloged in the integrated catalog facility catalog that catalog-name identifies. For more information about catalog-name values, see Naming conventions in SQL.

More than one Db2 subsystem can share the integrated catalog facility catalogs with the current server. To avoid the chance of those subsystems attempting to assign the same name to different data sets, specify a catalog-name value that is not used by the other Db2 subsystems.

Db2 assumes one and only one data set for each partition.

STOGROUP stogroup-name

If USING STOGROUP is used, explicitly or by default, for a partition n, Db2 defines the data set for the partition during the execution of the CREATE INDEX statement, using space from the named storage group. The privilege set must include SYSADM authority, SYSCTRL authority, or the USE privilege for that storage group. The integrated catalog facility catalog used for the storage group must NOT contain an entry for the nth data set of the index.

stogroup-name must identify a storage group that exists at the current server and the privilege set must include one of the following privileges or authorities, except when creating an index on a declare global temporary table if stogroup-name matches the default storage group of the work file database:

• SYSADM authority
• SYSCTRL authority
• USE privilege for the storage group

If you omit PRIQTY, SECQTY, or ERASE from a USING STOGROUP clause for some partition, their values are given by the next USING STOGROUP clause that governs that partition: either a USING clause that is not in any PARTITION clause, or a default USING clause. Db2 assumes one and only one data set for each partition.

FREEPAGE integer

Specifies how often to leave a page of free space when index entries are created as the result of executing a Db2 utility or when creating an index for a table with existing rows. One free page is left for every integer pages. The value of integer can range 0–255. The default is 0, leaving no free pages.

Do not specify FREEPAGE for an index on a declared temporary table.

PCTFREE integer

Determines the percentage of free space to leave in each nonleaf page and leaf page when entries are added to the index or index partition as the result of executing a Db2 utility or when creating an index for a table with existing rows. The first entry in a page is loaded without restriction. When additional entries are placed in a nonleaf or leaf page, the percentage of free space is at least as great as integer.

The value of integer can range from 0 to 99, however, if a value greater than 10 is specified, only 10 percent of free space will be left in nonleaf pages. The default is 10.

Do not specify PCTFREE for an index on a declared temporary table.

If the index is partitioned , the values of FREEPAGE and PCTFREE for a particular partition are given by the first of these choices that applies:

• The values of FREEPAGE and PCTFREE given in the PARTITION clause for that partition. Do not use more than one free-specification in any PARTITION clause.
• The values given in a free-specification that is not in any PARTITION clause.
• The default values FREEPAGE 0 and PCTFREE 10.

GBPCACHE

In a data sharing environment, specifies what index pages are written to the group buffer pool. In a non-data-sharing environment, the option is ignored unless the index is on a declared temporary table. Do not specify GBPCACHE for an index on a declared temporary table in either environment (data sharing or non-data-sharing).

CHANGED

Specifies that updated pages are written to the group buffer pool, when there is inter-Db2 R/W interest on the index or partition. When there is no inter-Db2 R/W interest, the group buffer pool is not used. Inter-Db2 R/W interest exists when more than one member in the data sharing group has the index or partition open, and at least one member has it open for update. GBPCACHE CHANGED is the default.

If the index is in a group buffer pool that is defined as GBPCACHE(NO), CHANGED is ignored and no pages are written to the group buffer pool.

ALL

Indicates that pages are written to the group buffer pool as they are read in from DASD.

Exception: In the case of a single updating Db2 subsystem when no other Db2 subsystems have any interest in the page set, no pages are written to the group buffer pool.

If the index is in a group buffer pool that is defined as GBPCACHE(NO), ALL is ignored and no pages are written to the group buffer pool.

NONE

Indicates that no pages are written to the group buffer pool. Db2 uses the group buffer pool only for cross-invalidation.

If the index is partitioned, the value of GBPCACHE for a particular partition is given by the first of these choices that applies:

1. The value of GBPCACHE given in the PARTITION clause for that partition. Do not use more than one gbpcache-specification in any PARTITION clause.
2. The value given in a gbpcache-specification that is not in any PARTITION clause.
3. GBPCACHE CHANGED is the default value.

DEFINE

Specifies when the underlying data sets for the index are physically created. The SPACE column in catalog table SYSINDEXPART is used to record the status of the data sets (undefined or allocated). If the DEFINE keyword is not specified, the define attribute is inherited from the current state of the base table space.

YES

The data sets are created when the index is created (the CREATE INDEX statement is executed).

NO

The data sets are not created until data is inserted into the index.

DEFINE NO is applicable only for Db2-managed data sets (USING STOGROUP is specified). Use DEFINE NO especially when performance of the CREATE INDEX statement is important or DASD resource is constrained.

Do not use DEFINE NO on an index if you use a program outside of Db2 to propagate data into a table on which that index is defined. If you use DEFINE NO on an index of a table and data is then propagated into the table from a program that is outside of Db2, the index space data sets are allocated, but the Db2 catalog will not reflect this fact. As a result, Db2 treats the data sets for the index space as if they have not yet been allocated. The resulting inconsistency causes Db2 to deny application programs access to the data until the inconsistency is resolved.

DEFINE NO is ignored for user-managed data sets (USING VCAT is specified). DEFINE NO is also ignored if the index is being created on a table that is not empty.

Do not specify DEFINE NO if the index is created on a base table that is involved in a clone relationship.

Do not specify DEFINE NO for an index on a declared temporary table.

COMPRESS NO or COMPRESS YES

Specifies whether compression for index data will be used. If the index is partitioned, the clause will apply to all partitions.

COMPRESS NO

Specifies that no index compression will be used.

COMPRESS NO is the default.

COMPRESS YES

Specifies that index compression will be used. The buffer pool that is used to create the index must be 8K, 16K, or 32K in size. The physical page size on disk will be 4K. The index compression will take place immediately.

Index compression is recommended for applications that do sequential insert operations with few or no delete operations. Random inserts and deletes can adversely effect compression. Index compress is also recommended for applications where the indexes are created primarily for scan operations.

INCLUDE NULL KEYS or EXCLUDE NULL KEYS

Specifies whether an index entry will be created when every key column contains the NULL value.

INCLUDE NULL KEYS

Specifies that an index entry will be created when every key column contains the NULL value.

INCLUDE NULL KEYS is the default.

EXCLUDE NULL KEYS

Specifies that no index entry will be created when every key column contains the NULL value. If any key column is not null the index entry will be created.

EXCLUDE NULL KEYS must not be specified with the following:

• UNIQUE
• BUSINESS_TIME WITHOUT OVERLAPS
• XML-index-specification
• key-expression
• INCLUDE (column-name)

EXCLUDE NULL KEYS must also not be specified if any of the columns that are identified by column-name are defined as NOT NULL, or if the index is defined as a partitioning index for use with index-controlled partitioning.

PARTITION BY RANGE

Specifies the partitioning index for the table, which determines the partitioning scheme for the data in the table.

PARTITION BY RANGE should only be specified if the table space is partitioned and the partitioning schema has not already been established.

PARTITION BY RANGE must not be specified if the index is an extended index, is defined with the BUSINESS_TIME WITHOUT OVERLAPS, or if the table is in a universal table space (ranged-partitioned or partition-by-growth table space).

partition-element

Specifies the range for each partition.

PARTITION integer

A PARTITION clause specifies the highest value of the index key in one partition of a partitioning index. In this context, highest means highest in the sorting sequences of the index columns. In a column defined as ascending (ASC), highest and lowest have their usual meanings. In a column defined as descending (DESC), the lowest actual value is highest in the sorting sequence.

If you use CLUSTER, and the table is contained in a partitioned table space, you must use exactly one PARTITION clause for each partition (defined with NUMPARTS on CREATE TABLESPACE). If there are p partitions, the value of integer must range from 1 through p.

The length of the highest value of a partition (also called the limit key) is the same as the length of the partitioning index.

ENDING AT(constant, MAXVALUE, or MINVALUE...)

Specifies that this is the partitioning index and indicates how the data will be partitioned. The table space is marked complete after this partitioning index is created. You must use at least one value (constant, MAXVALUE, or MINVALUE) after ENDING AT in each PARTITION clause. You can use as many as there are columns in the key. The concatenation of all the values is the highest value of the key in the corresponding partition of the index unless the VALUES statement was already specified when the table or previous index was created.

constant

Specifies a constant value with a data type that must conform to the rules for assigning that value to the column. If a string constant is longer or shorter than required by the length attribute of its column, the constant is either truncated or padded on the right to the required length. If the column is ascending, the padding character is X'FF'. If the column is descending, the padding character is X'00'. The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column. A hexadecimal string constant (GX) cannot be specified.

MAXVALUE

Specifies a value greater than the maximum value for the limit key of a partition boundary (that is, all X'FF' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are ascending, a constant or the MINVALUE clause cannot be specified following MAXVALUE. After MAXVALUE is specified, all subsequent columns must be MAXVALUE.

MINVALUE

Specifies a value that is smaller than the minimum value for the limit key of a partition boundary (that is, all X'00' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are descending, a constant or the MAXVALUE clause cannot be specified following MAXVALUE. After MINVALUE is specified, all subsequent columns must be MINVALUE.

The key values are subject to the following rules:

• The first value corresponds to the first column of the key, the second value to the second column, and so on. Using fewer values than there are columns in the key has the same effect as using the highest or lowest values for the omitted columns, depending on whether they are ascending or descending.
• If a key includes a ROWID column or a column with a distinct type that is based on a ROWID data type, 17 bytes of the constant that is specified for the corresponding ROWID column are considered.
• The highest value of the key in any partition must be lower than the highest value of the key in the next partition.
• If the concatenation of all the values exceeds 255 bytes, only the first 255 bytes are considered.
• The highest value of the key in the last partition depends on how the table space is defined. For table spaces that are created without the LARGE or DSSIZE options, the values that you specify after VALUES are not enforced. The highest value of the key that can be placed in the table is the highest possible value of the key.

  For large partitioned table space, the values you specify are enforced. The value specified for the last partition is the highest value of the key that can be placed in the table. Any key values greater than the value that is specified for the last partition are out of range.

ENDING AT can be specified only if the ENDING AT clause was not specified on a previous CREATE or ALTER TABLE statement for the underlying table.

INCLUSIVE

Specifies that the specified range values are included in the data partition.

BUFFERPOOL bpname

Identifies the buffer pool that is to be used for the index. The privilege set must include SYSADM or SYSCTRL authority or the USE privilege for the buffer pool, except when creating an index on a declare global temporary table and bpname matches the default index buffer pool of the work file database.

The bpname must identify an activated 4KB, 8KB, 16KB, or 32KB buffer pool.

A buffer pool with a smaller size should be chosen for indexes with random insert patterns. A buffer pool with a larger size should be chosen for indexes with sequential insert patterns.

For more details about bpname, see Naming conventions in SQL. For a description of active and inactive buffer pools, see Controlling Db2 databases and buffer pools.

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 unless the index is on a declared temporary table.

NO

Not eligible for closing.

If the limit on the number of open data sets is reached and there are no page sets that specify CLOSE YES to close, page sets that specify CLOSE NO will be closed.

For an index on a declared temporary table, Db2 uses CLOSE NO regardless of the value specified.

DEFER

Indicates whether the index is built during the execution of the CREATE INDEX statement. Regardless of the option specified, the description of the index and its index space is added to the catalog. If the table is determined to be empty and DEFER YES is specified, the index is neither built nor placed in a rebuild-pending status. For more information about using DEFER, see Index names and guidelines. Do not specify DEFER for an index on a declared temporary table or an auxiliary table.

NO

The index is built. This is the default.

YES

The index is not built. If the table is populated, the index is placed in a rebuild-pending status and a warning message is issued; the index must be rebuilt by the REBUILD INDEX utility.

DSSIZE integer G

FL 500Specifies the maximum size for each partition of a partitioned index. Any integer 1–1024 can be specified (for example, 1 G or 1024 G). This keyword is not valid on nonpartitioned secondary indexes. You can only specify DSSIZE on CREATE INDEX if the index is on a table space with relative page numbers.

To specify a value greater than 4G, the data sets for the table space must be associated with a DFSMS data class that has been specified with extended format and extended addressability.

If the index is a partitioned index using relative page numbering, the value of DSSIZE for a particular partition is given by the first of these choices that applies:

• The value of DSSIZE given in the PARTITION clause for that partition.
• The value given by a DSSIZE keyword that is not in any PARTITION clause.
• The default value is inherited from the base table space.

integer can be separated from G by 0 or more spaces.

PIECESIZE integer

Specifies the maximum addressability of each data set for a non-partitioned index. The subsequent keyword K, M, or G, indicates the units of the value that is specified in integer.

K

Indicates that the integer value is to be multiplied by 1024 to specify the maximum data set size in bytes. integer must be a power of two in the range 1–268435456.

M

Indicates that the integer value is to be multiplied by 1048576 to specify the maximum data set size in bytes. integer must be a power of two in the range 1–262144.

G

Indicates that the integer value is to be multiplied by 1073741824 to specify the maximum data set size in bytes. integer must be a power of two in the range 1–256.

integer can be separated from K, M, or G by 0 or more spaces.integer

The following table shows the valid values for the data set size, which depend on the size of the table space.

Table 1. Valid values of PIECESIZE clause

K units	M units	G units	Size attribute of table space
256K
512 K
1024 K	1 M
2048 K	2 M
4096 K	4 M
8192 K	8 M
16384 K	16 M
32768 K	32 M
65536 K	64 M
131072 K	128 M
262144 K	256 M
524288 K	512 M
1048576 K	1024 M	1 G
2097152 K	2048 M	2 G
4194304 K	4096 M	4 G	LARGE, DSSIZE 4 G (or greater)
8388608 K	8192 M	8 G	DSSIZE 8 G (or greater)
16777216 K	16384 M	16 G	DSSIZE 16 G (or greater)
33554432 K	32768 M	32 G	DSSIZE 32 G (or greater)
67108864 K	65536 M	64 G	DSSIZE 64 G (or greater)
134217728 K	131072 M	128 G	DSSIZE 128 G (or greater)
268435456 K	262144 M	256 G	DSSIZE 256 G

PIECESIZE has no effect on primary and secondary space allocation as it is only a specification of the maximum amount of data that a data set can hold and not the actual allocation of storage.

If you change the PIECESIZE value with the ALTER INDEX statement, the index is put into REBUILD-pending status.

See the following for additional information:

• Number of pieces and maximum piece size for non-partitioned indexes and data-partitioned secondary indexes
• Choosing a value for PIECESIZE

COPY

Indicates whether the COPY utility is allowed for the index. Do not specify COPY for an index on a declared temporary table.

NO

Does not allow full image or concurrent copies or the use of the RECOVER utility on the index. NO is the default.

YES

Allows full image or concurrent copies and the use of the RECOVER utility on the index.

Notes for CREATE INDEX

Owner privileges:

The owner of the table has all table privileges (see GRANT statement (table or view privileges)) with the ability to grant these privileges to others. For more information about ownership of the object, see Authorization, privileges, permissions, masks, and object ownership.

Effects of the DEFER clause:

If DEFER NO is implicitly or explicitly specified, the CREATE INDEX statement cannot be executed while a Db2 utility has control of the table space that contains the identified table.

If the identified table already contains data and if the index build is not deferred, CREATE INDEX creates the index entries for it. If the table does not yet contain data, CREATE INDEX creates a description of the index; the index entries are created when data is inserted into the table.

Errors evaluating the expressions for an index:

Errors that occur during the evaluation of an expression for an index are returned when the expression is evaluated. This can occur on an SQL data change statement, SELECT from an SQL data change statement, or the REBUILD INDEX utility. For example, the evaluation of the expression 10 / column_1 returns an error if the value in column_1 is 0. The error is returned during CREATE INDEX processing if the table is not empty and contains a row with a value of zero in column_1, otherwise the error is returned during the processing of the insert or update operation when a row with a value of zero in column_1 is inserted or updated.

Result length of expressions that return a string type:

If the result data type of key-expression is a string type and the result length cannot be calculated at bind time, the length is set to the maximum allowable length of that data type or the largest length that Db2 can estimate. In this case, the CREATE INDEX statement can fail because the total key length might exceed the limit of an index key.

For example, the result length of the expression REPEAT('A', CEIL(1.1)) is VARCHAR(32767) and the result length of the expression SUBSTR(DESCRIPTION,1,INTEGER(1.2)) is the length of the DESCRIPTION column. Therefore, a CREATE INDEX statement that uses any of these expressions as a key-expression might not be created because the total key length might exceed the limit of an index key.

Use of ASC or DESC on key columns:

There are no restrictions on the use of ASC or DESC for the columns of a parent key or foreign key. An index on a foreign key does not have to have the same ascending and descending attributes as the index of the corresponding parent key.

EBCDIC, ASCII, and UNICODE encoding schemes for an index:

In general, an index has the same encoding scheme as its associated table. However, if an index on an EBCDIC table consists of only Unicode columns, the encoding scheme of the index is Unicode.

Maximum partition size of a partitioned index

The size of a partitioned index depends on whether the corresponding partitioned table space is created with or without the LARGE or DSSIZE keywords, and on the number of partitions.

The following table provides information about partitioned indexes on table spaces that are created without the LARGE or DSSIZE keywords and with 64 or fewer partitions.

Table 2. Maximum number of pieces and the default size of a partitioned index on a partitioned table space that is created without the LARGE or DSSIZE clauses and with a NUMPARTS value of less than or equal to 64

Definition of partitioned table space (non-large)	Maximum number of pieces for a partitioned index	Default size of a partitioned index, per data set
NUMPARTS <= 16	16	4G
NUMPARTS >= 17 but NUMPARTS <= 32	32	2G
NUMPARTS >= 33	64	1G

The following table shows information about partitioned indexes on table spaces that are created with the LARGE or DSSIZE keywords and with more than 64 partitions.

Table 3. Maximum number of pieces and the default partitioned index size for a partitioned table space that is created with the LARGE or DSSIZE clauses or with a NUMPARTS value of greater than 64

Definition of partitioned table space (large)	Maximum number of pieces for a partitioned index	Default index piece size for a partitioned index
One or more of the following conditions are true: LARGE clause - specified NUMPARTS greater than 64 but less than 256 and DSSIZE clause not specified	Maximum number of partitions in the partitioned table space	4G
One or more of the following conditions are true: DSSIZE clause - specified NUMPARTS greater than or equal to 256	Maximum number of partitions in the partitioned table space	MIN(table space DSSIZE, 2^32/ (Maximum number of partitions in the table space) * index page size)

To calculate the maximum data set size for a partitioned index, you need to first calculate the maximum number of partitions in the table space by using the following formula:

MIN(4096, 2^32/ (table space DSSIZE / table space page size))


After you calculate the maximum number of partitions in the table space, you can calculate the maximum data set size for a partitioned index with the following formula, using the number of partitions that you calculated above:

MIN(table space DSSIZE, 2^32/
(Maximum number of partitions in the table space) * index page size)

For an index that is defined with COMPRESS YES, index page size is always 4096 (4KB).

For example, suppose that a table space and an index on that table space have the following characteristics:

• DSSIZE: 64 GB
• Page size: 32 KB
• Index page size: 4 KB
• Maximum number of partitions: 2048

Given those characteristics, you can begin by calculating the maximum number of partitions in the table space:

MIN(4096, 2^32/ (64GB / 32KB)) = 2048


You can then use the value of 2048 to calculate the maximum data set size for the partitioned index:

MIN(64 GB, 2^32/ 2048 * 4KB)
= MIN(64GB, 8GB)
= 8GB


Number of pieces and maximum piece size for non-partitioned indexes

The largest amount of data that an index can hold is the maximum number of pieces for the index times the maximum amount of data that a piece can hold.

For a non-partitioned index, the maximum amount of data that an index can hold is defined by using the PIECESIZE parameter.

The default piece size for an index is as follows:

• 2 GB (PIECESIZE 2 G) for indexes of table spaces created without the LARGE or DSSIZE option
• 4 GB (PIECESIZE 4 G) for indexes of table spaces created with the LARGE or DSSIZE option
• 4 GB (PIECESIZE 4 G) for auxiliary indexes

The following tables list the maximum number of pieces and the default index piece size for various table spaces.

Table 4. Maximum number of pieces and the default index piece size for a partitioned table space that is created without the LARGE or DSSIZE clauses and has a NUMPART value of less than or equal to 64

Definition of partitioned table space (non-large), NUMPART value	Maximum number of pieces in a non-partitioned index	Default index piece size for a non-partitioned index
NUMPARTS <= 16	32	2G
NUMPARTS >= 17 but NUMPARTS <= 32	32	2G
NUMPARTS >= 33	32	2G

Table 5. Maximum number of pieces and the default index piece size for a partitioned table space that is created with the LARGE or DSSIZE clauses or has a NUMPARTS value of greater than or equal to 65

Definition of partitioned table space (large)	Maximum number of pieces for a non-partitioned index	Default index piece size for a non-partitioned index
LARGE clause - specified DSSIZE clause - not specified	MIN(4096, 2^32/ (x/y)) - see 1	4G
LARGE clause - not specified DSSIZE clause - not specified NUMPARTS clause - greater than 64 but less than 256	MIN(4096, 2^32/ (x/y)) - see 1	4G
LARGE clause - not specified DSSIZE clause - specified or NUMPARTS clause - greater than or equal to 256	MIN(4096, 2^32/ (x/y)) - see 1	4G

Note:

1. For a non-partitioned index, the formula MIN(4096, 2^32 / (x / y)), determines the maximum number of pieces for the non-partitioned index, where x and y have the following values:
   • x is the piece size of the index (stored in the PIECESIZE column of the SYSIBM.SYSINDEXES catalog table)
   • y is the page size of the index (stored in the PGSIZE column of the SYSIBM.SYSINDEXES catalog table)

Table 6. Maximum number of pieces and the default index piece size for a non-partitioned table space

Type of non-partitioned table space	Maximum number of pieces	Default index piece size
non-segmented table space	32	2G
segmented table space	32	2G
LOB, auxiliary, or XML table space	32	4G

Choosing a value for PIECESIZE:

To choose a value for PIECESIZE, divide the size of the non-partitioned index by the number of data sets that you want. For example, to ensure that you have five data sets for the non-partitioned index, and your index is 10MB (and not likely to grow much), specify PIECESIZE 2 M. If your non-partitioned index is likely to grow, choose a larger value.

Remember that 32 data sets is the limit if the underlying table space is not defined as LARGE or with a DSSIZE parameter and that the limit is 4096 for objects with greater than 254 parts. For a non-partitioned index on a table space that is defined as LARGE or with a DSSIZE parameter, the maximum is MIN(4096, 2³² / (index piece size/index page size)).

Keep the PIECESIZE value in mind when you are choosing values for primary and secondary quantities. Ideally, the value of your primary quantity plus the secondary quantities should be evenly divisible into PIECESIZE.

Dropping an index:

Partitioning indexes can be dropped. If the table space is using index-controlled partitioning, the table space is converted to table-controlled partitioning. Secondary indexes that are not indexes on auxiliary tables can be dropped simply by dropping the indexes. An empty index on an auxiliary table can be explicitly dropped; a populated index can be dropped only by dropping other objects. For details, see Dropping an index on an auxiliary table and an auxiliary table"Dropping an index on a base table and auxiliary table" in DROP statement.

If the index is a unique index that enforces a primary key, unique key, or referential constraint, the constraint must be dropped before the index is dropped. See DROP statement.

Unique indexes and enforcement of UNIQUE or PRIMARY KEY specifications for a table:

A table requires a unique index (that is not defined as UNIQUE WHERE NOT NULL) if you use the UNIQUE or PRIMARY KEY clause in the CREATE or ALTER TABLE statements, or if there is a ROWID column that is defined as GENERATED BY DEFAULT. Db2 implicitly creates those unique indexes if the table space is explicitly created and the CREATE or ALTER TABLE statement is processed by the schema processor or if the table space is implicitly created; otherwise, you must explicitly create them. If any of the unique indexes that must be explicitly defined do not exist, the definition of the table is incomplete, and the following rules apply:

• Let K denote a key for which a required unique index does not exist and let n denote the number of unique indexes that remain to be created before the definition of the table is complete. (For a new table that has no indexes, K is its primary key or any of the keys defined in the CREATE or ALTER TABLE statement as UNIQUE and n is the number of such keys. After the definition of a table is complete, an index cannot be dropped if it is enforcing a primary key or unique key.)
• The creation of the unique index reduces n by one if the index key is identical to K. The keys are identical only if they have the same columns in the same order.
• If n is now zero, the creation of the index completes the definition of the table.
• If K is a primary key, the description of the index indicates that it is a primary index. If K is not a primary key, the description of the index indicates that it enforces the uniqueness of a key defined as UNIQUE in the CREATE or ALTER TABLE statement.

A unique index cannot be created on a materialized query table.

Unique indexes and XML columns:

If the index is an XML index on a unique XML column, the uniqueness applies to values of the specified pattern across all documents of that column, and the uniqueness is enforced on the value after the value is cast to the specified SQL data type. Because the data type conversion might result in a loss of precision and normalization, multiple values that appear unique in the XML document might still result in duplicate errors. If the index is defined using an expression, the uniqueness is enforced against the values that are stored in the index, not against the original values of the columns. The WHERE NOT NULL specification is ignored with a warning if XMLPATTERN is also specified, and the index is treated as if UNIQUE had been specified.

Defining an XML index using an XPath pattern-expression that includes functions:

An XPath pattern-expression that includes functions (including fn:exists() or fn:upper-case()) will have two parts. The first part is referred to as the context step and specifies the XPath of the element node or attribute node for which an index entry will be created (the element or attributes NodeID will be included in the index). The context step follows the same syntax as the XPath pattern-expression for an XML index, except that for fn:exists() it has to specify an element node, and for fn:upper-case() it has to specify an element node or an attribute node.

The second part is referred to as the function expression step and specifies the fn:exists() or fn:upper-case() XPath function. The function expression step is the right-most part of an XPath pattern-expression. For each node specified by the context step, the function expression step specifies the key value for the index. For example, in the XPath pattern-expression /purchaseOrder/items/item/fn:exists(shipDate), the context step is /purchaseOrder/items/item, and the function expression step is fn:exists(shipDate).

Use of PARTITIONED keyword:

When a partitioned index is created and no additional keywords are specified, the index is non-partitioned. If the keyword PARTITIONED is specified, the index is partitioned. If PARTITION BY RANGE is specified, the index is both data-partitioned and key-partitioned because it is defined on the partitioning columns of the table. Any index on a partitioned table space that does not meet the definition of a partitioning index is a secondary index. When a secondary index is created and no additional keywords are specified, the secondary index is non-partitioned (NPSI). If the keyword PARTITIONED is specified, the index is a data-partitioned secondary index (DPSI).

Creating a partitioning index for a table created without partition boundaries:

When a table is created without specifying partition boundaries using the ENDING AT clause, the table is incomplete until a partitioning index is created. The first index that is created for a table must specify both the PARTITION and the ENDING AT clauses.

When the PARTITION clause is specified while creating an index, either the PARTITIONED clause, or the ENDING AT clause must also be specified.

Considerations for tables that are involved in a clone relationship:

If an index is created on a base table that is involved in a clone relationship, an index with the same name is also created on the clone table. The index on the clone table will be placed in rebuild-pending status unless the clone table is empty when the index is created.

Considerations for tables that contain a row change timestamp column:

To create an index that refers to a row change timestamp column in the table, values must already exist in the column for all rows. Values are stored in row change timestamp columns whenever a row is inserted or updated in the table. If the row change timestamp column is added to an existing table that contains rows, the values for the row change timestamp column is not materialized and stored at the time of the ALTER TABLE statement. Values are materialized for these rows when they are updated, or when a REORG or a LOAD REPLACE utility is run on the table or table space.

Restriction on table spaces when there are pending changes to the definition:

A CREATE INDEX statement is not allowed if there are pending changes to the definition of the table space or to any objects in the table space. In addition, an index that references an expression cannot be created on a table where the inline length of a LOB column has been changed and the table space has not been reorganized.

Effects of DEFINE NO and INCLUDE NULL KEYS or EXCLUDE NULL KEYS:

When INCLUDE NULL KEYS is specified (implicitly or explicitly) with DEFINE NO and the table that is being indexed is populated, a warning is returned, the index is created, and the data set is defined. When EXCLUDE NULL KEYS is specified, it is possible that the data set will not be defined if the all rows for the table that is being indexed contain the NULL value for all key columns. The index will be empty after the CREATE INDEX statement. However, if DEFINE NO is specified with EXCLUDE NULL KEYS a warning is returned.

Creating indexes on Db2 catalog tables:

For details on creating indexes on catalog tables, see SQL statements allowed on the catalog.

EA-enabled index data sets:

If an index is created for an EA-enabled table space, the data sets for the index must be set up to belong to a DFSMS data class that has the extended format and extended addressability attributes.

Alternative syntax and synonyms:

To provide compatibility with previous releases of Db2 or other products in the Db2 family, Db2 supports the following keywords when creating a partitioned index:

• PART integer VALUES as an alternative syntax for PARTITION integer ENDING. The PARTITION BY RANGE keyword that precedes the partition-element clause is optional.

Although these keywords are supported as alternatives, they are not the preferred syntax.

User-defined indexes on catalog tables:

If you issue CREATE INDEX for an index on a catalog table, and you specify the USING clause, Db2 ignores that clause. Instead, Db2 defines and manages the index data sets. The data sets are defined in the same SMS environment that is used for the catalog data sets with default space attributes.

Temporal referential constraints:

An index is required for the foreign key of a temporal referential constraint. The index must be defined in one of the following ways:

• Specify the BUSINESS_TIME WITH OVERLAPS clause after the columns and key expressions.
• Specify the end column of the BUSINESS_TIME period, followed by the begin column of the BUSINESS_TIME period as the last two keys of the index. ASC must be used for each of these columns.

When a temporal referential constraint is defined for a table, the first index that is created that meets the criteria for an index on the foreign key, is recorded as a dependency for the constraint. An index used for the foreign key of a temporal referential constraint cannot be dropped. A column cannot be added to an index used for a temporal referential constraint.