This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
No explicit privilege is required to create an index on a declared temporary table.
>>-CREATE--+--------+--INDEX--index-name------------------------> '-UNIQUE-' .-,---------------------. (1) V .-ASC--. | >--ON--+-table-name-----+--(----column-name--+------+-+--)------> | (2) | '-DESC-' '-nickname-------' >--+-----------------+--●--+---------------------+--●-----------> +-PARTITIONED-----+ '-IN--tablespace-name-' '-NOT PARTITIONED-' >--+--------------------+--●------------------------------------> '-SPECIFICATION ONLY-' >--+------------------------------------+--●--------------------> | .-,-----------. | | (3) V | | '-INCLUDE------(----column-name-+--)-' >--+-----------------------------------------------------------------------+--●--> | (4) | +-| xml-index-specification |-------------------------------------------+ +-CLUSTER---------------------------------------------------------------+ '-EXTEND USING--index-extension-name--+-------------------------------+-' | .-,-------------------. | | V | | '-(----constant-expression-+--)-' .-PCTFREE 10-------. >--+------------------+--●--+-------------------------+--●------> '-PCTFREE--integer-' '-LEVEL2 PCTFREE--integer-' .-ALLOW REVERSE SCANS----. >--+---------------------+--●--+------------------------+--●----> '-MINPCTUSED--integer-' '-DISALLOW REVERSE SCANS-' .-PAGE SPLIT SYMMETRIC-. >--+----------------------+--●----------------------------------> '-PAGE SPLIT--+-HIGH-+-' '-LOW--' >--+------------------------------------------------+--●--------> '-COLLECT--+-----------------------+--STATISTICS-' '-+---------+--DETAILED-' '-SAMPLED-' >--+-------------------+--------------------------------------->< '-COMPRESS--+-NO--+-' '-YES-'
xml-index-specification (1) |--GENERATE KEY USING XMLPATTERN------| xmlpattern-clause |-----> >--| xmltype-clause |-------------------------------------------|
xmlpattern-clause |--'--+---------------------------+--| pattern-expression |--'--| '-| namespace-declaration |-'
namespace-declaration .----------------------------------------------------------. V | |----+-DECLARE NAMESPACE--namespace-prefix=namespace-uri-+--;-+--| '-DECLARE DEFAULT ELEMENT NAMESPACE--namespace-uri--'
pattern-expression .----------------------------------------------. V | |----+-/--+-| forward-axis |-+-| xmlname-test |-+-+-------------| '-//-' '-| xmlkind-test |-'
forward-axis .-child::--------------. |--+----------------------+-------------------------------------| +-@--------------------+ +-attribute::----------+ +-descendant::---------+ +-self::---------------+ '-descendant-or-self::-'
xmlname-test |--+-xml-qname--------+-----------------------------------------| '-| xml-wildcard |-'
xml-wildcard |--+-*--------------+-------------------------------------------| +-xml-nsprefix:*-+ '-*:xml-ncname---'
xmlkind-test |--+-node()-------------------+---------------------------------| +-text()-------------------+ +-comment()----------------+ '-processing instruction()-'
xmltype-clause .-IGNORE INVALID VALUES-. |--AS--| data-type |--+-----------------------+-----------------| '-REJECT INVALID VALUES-'
data-type |--| sql-data-type |--------------------------------------------|
sql-data-type |--SQL--+-VARCHAR--+-(--integer--)-+-+--------------------------| | '-HASHED--------' | +-DOUBLE---------------------+ +-DATE-----------------------+ '-TIMESTAMP------------------'
The uniqueness 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.
If the index is on an XML column (the index is an index over XML data), the uniqueness applies to values with the specified pattern-expression for all rows of the table. Uniqueness is enforced on each value after the value has been converted to the specified sql-data-type. Because converting to the specified sql-data-type might result in a loss of precision or range, or different values might be hashed to the same key value, multiple values that appear to be unique in the XML document might result in duplicate key errors. The uniqueness of character strings depends on XQuery semantics where trailing blanks are significant. Therefore, values that would be duplicates in SQL but differ in trailing blanks are considered unique values in an index over XML data.
When UNIQUE is used, null values are treated as any other values. For example, if the key is a single column that may contain null values, that column may contain no more than one null value.
If the UNIQUE option is specified, and the table has a distribution key, the columns in the index key must be a superset of the distribution key. That is, the columns specified for a unique index key must include all the columns of the distribution key (SQLSTATE 42997).
Primary or unique keys cannot be subsets of dimensions (SQLSTATE 429BE).
If ON nickname is specified, UNIQUE should be specified only if the data for the index key contains unique values for every row of the data source table. The uniqueness will not be checked.
For an index over XML data, UNIQUE can be specified only if the specified pattern-expression specifies a single complete path and does not contain a descendant or descendant-or-self axis, "//", an xml-wildcard, node(), or processing-instruction() (SQLSTATE 429BS).
The implicit or explicit qualifier for indexes on declared global temporary tables must be SESSION (SQLSTATE 428EK).
nickname is the nickname on which an index specification is to be created. The nickname references either a data source table whose index is described by the index specification, or a data source view that is based on such a table. The nickname must be listed in the catalog.
Each column-name must be an unqualified name that identifies a column of the table. Up to 64 columns can be specified. If table-name is a typed table, up to 63 columns can be specified. If table-name is a subtable, at least one column-name must be introduced in the subtable; that is, not inherited from a supertable (SQLSTATE 428DS). No column-name can be repeated (SQLSTATE 42711).
The sum of the stored lengths of the specified columns must not be greater than the index key length limit for the page size. For key length limits, see "SQL limits". If table-name is a typed table, the index key length limit is further reduced by 4 bytes. Note that this length limit can be reduced even more by system overhead, which varies according to the data type of the column and whether or not the column is nullable. For more information on overhead affecting this limit, see "Byte Counts" in "CREATE TABLE".
Note that this length can be reduced by system overhead, which varies according to the data type of the column and whether it is nullable. For more information on overhead affecting this limit, see "Byte Counts" in "CREATE TABLE".
No LOB column or distinct type column based on a LOB can be used as part of an index, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008). A structured type column can only be specified if the EXTEND USING clause is also specified (SQLSTATE 42962). If the EXTEND USING clause is specified, only one column can be specified, and the type of the column must be a structured type or a distinct type that is not based on a LOB (SQLSTATE 42997).
If an index has only one column, and that column has the XML data type, and the GENERATE KEY USING XMLPATTERN clause is also specified, the index is an index over XML data. A column with the XML data type can be specified only if the GENERATE KEY USING XMLPATTERN clause is also specified (SQLSTATE 42962). If the GENERATE KEY USING XMLPATTERN clause is specified, only one column can be specified, and the type of the column must be XML.
A partitioned index with a definition that duplicates the definition of a nonpartitioned index is not considered to be a duplicate index. For more details, see the Rules section in this topic.
A partitioned index cannot be created on a partitioned table that has detached dependent tables, for example, MQTs (SQLSTATE 55019).
A partitioned index cannot be created on a partitioned table that has detached partitions.
A nonpartitioned index with a definition that duplicates the definition of a partitioned index is not considered to be a duplicate index. For more details, see the Rules section in this topic.
Specifies the table space in which the index is to be created. This clause is not supported for indexes on a created temporary table or a declared temporary table (SQLSTATE 42601). You can specify this clause even if the INDEX IN clause was specified when the table was created. This will override that clause.
The table space specified by tablespace-name must be in the same database partition group as the data table spaces for the table and manage space in the same way as the other table spaces of the partitioned table (SQLSTATE 42838); it must be a table space on which the authorization ID of the statement holds the USE privilege.
If the IN clause is not specified, the index is created in the table space that was specified by the INDEX IN clause on the CREATE TABLE statement. If no INDEX IN clause was specified, the table space of the first visible or attached data partition of the table is used. This is the first partition in the list of data partitions that are sorted on the basis of range specifications. If the IN clause is not specified, the authorization ID of the statement is not required to have the USE privilege on the default table space.
If the index specification applies to an index that is unique, DB2 does not verify that the column values in the remote table are unique. If the remote column values are not unique, queries against the nickname that include the index column might return incorrect data or errors.
This clause cannot be used when creating an index on a created temporary table or declared temporary table (SQLSTATE 42995).
This clause cannot be used with created temporary tables or declared temporary tables (SQLSTATE 42995).
INCLUDE cannot be specified for indexes that are defined with EXTEND USING, if nickname is specified, or if the index is an XML values index (SQLSTATE 42601).
This clause only applies to XML columns (SQLSTATE 429BS).
You cannot use xml-wildcard if you also specify UNIQUE.
Index XML data type | Index SQL data type |
---|---|
xs:string | VARCHAR(integer), VARCHAR HASHED |
xs:double | DOUBLE |
xs:date | DATE |
xs:dateTime | TIMESTAMP |
For VARCHAR(integer) and VARCHAR HASHED, the value is converted to an xs:string value using the XQuery function fn:string. The length attribute of VARCHAR(integer) is applied as a constraint to the resulting xs:string value. An index SQL data type of VARCHAR HASHED applies a hash algorithm to the resulting xs:string value to generate a hash code that is inserted into the index.
For indexes using the data types DOUBLE, DATE, and TIMESTAMP, the value is converted to the index XML data type using the XQuery cast expression.
If the index is unique, the uniqueness of the value is enforced after the value is converted to the indexed type.
Page size | Maximum length of document node (bytes) |
---|---|
4KB | 817 |
8KB | 1841 |
16KB | 3889 |
32KB | 7985 |
XQuery semantics are used for string comparisons, where trailing blanks are significant. This differs from SQL semantics, where trailing blanks are insignificant during comparisons.
The index can ignore only invalid XML pattern values for the index XML data type. Valid values must conform to the DB2 representation of the value for the index XML data type, or an error is returned. An XML pattern value associated with the index XML data type xs:string is always valid. However, the additional length constraint of the associated index SQL data type VARCHAR(integer) data type can still raise an error, if the maximum length is exceeded. If an error is returned, XML data is not inserted or updated in the table if the index already exists (SQLSTATE 23525). If the index does not exist, the index is not created (SQLSTATE 23526).
CLUSTER is disallowed if nickname is specified, or if the index is an index over XML data (SQLSTATE 42601). This clause cannot be used with created temporary tables or declared temporary tables (SQLSTATE 42995) or range-clustered tables (SQLSTATE 429BG).
This clause cannot be used with created temporary tables or declared temporary tables (SQLSTATE 42995).
PCTFREE is disallowed if nickname is specified (SQLSTATE 42601). This clause cannot be used with created temporary tables or declared temporary tables (SQLSTATE 42995).
LEVEL2 PCTFREE is disallowed if nickname is specified (SQLSTATE 42601). This clause cannot be used with created temporary tables or declared temporary tables (SQLSTATE 42995).
MINPCTUSED is disallowed if nickname is specified (SQLSTATE 42601). This clause cannot be used with created temporary tables or declared temporary tables (SQLSTATE 42995).
DISALLOW REVERSE SCANS cannot be specified together with nickname (SQLSTATE 42601).
ALLOW REVERSE SCANS cannot be specified together with nickname (SQLSTATE 42601).
(1,1),(1,2),(1,3), ... (1,n),
(2,1),(2,2),(2,3), ... (2,n),
...
(m,1),(m,2),(m,3), ... (m,n)
The next key to be inserted
would have the value (x,y) where 1 <=
x <= m and y > n.In such cases, use the PAGE SPLIT HIGH clause so that page splits do not result in many pages that are 50 percent empty.
To circumvent this default behavior, use the LOCK TABLE statement to explicitly lock the table before issuing a CREATE INDEX statement. (The table can be locked in either SHARE or EXCLUSIVE mode, depending on whether read access is to be allowed.)
CREATE UNIQUE INDEX UNIQUE_NAM
ON PROJECT(PROJNAME)
CREATE INDEX JOB_BY_DPT
ON EMPLOYEE (WORKDEPT, JOB)
CREATE UNIQUE INDEX JOB_BY_DEPT
ON EMPLOYEE (WORKDEPT, JOB)
SPECIFICATION ONLY
CREATE INDEX SPATIAL_INDEX ON CUSTOMER (LOCATION)
EXTEND USING (GRID_EXTENSION (x'000100100010001000400010'))
CREATE INDEX IDX1 ON TAB1 (col1) COLLECT STATISTICS
CREATE INDEX IDX2 ON TAB1 (col2) COLLECT DETAILED STATISTICS
CREATE INDEX IDX3 ON TAB1 (col3) COLLECT SAMPLED DETAILED STATISTICS
CREATE UNIQUE INDEX A_IDX ON MYNUMBERDATA (A) IN IDX_TBSP
CREATE INDEX B_IDX ON MYNUMBERDATA (B)
NOT PARTITIONED IN IDX_TBSP
Example 10: Create an index over XML data on a table named COMPANYINFO, which contains an XML column named COMPANYDOCS. The XML column COMPANYDOCS contains a large number of XML documents similar to the one below:
<company name="Company1">
<emp id="31201" salary="60000" gender="Female">
<name>
<first>Laura</first>
<last>Brown</last>
</name>
<dept id="M25">
Finance
</dept>
</emp>
</company>
CREATE INDEX EMPINDEX ON COMPANYINFO(COMPANYDOCS)
GENERATE KEY USING XMLPATTERN '/company/emp/@id'
AS SQL DOUBLE
CREATE INDEX EMPINDEX ON COMPANYINFO(COMPANYDOCS)
GENERATE KEY USING XMLPATTERN '/child::company/child::emp/attribute::id'
AS SQL DOUBLE
CREATE UNIQUE INDEX MYDOCSIDX ON MYDOCS(DOC)
GENERATE KEY USING XMLPATTERN '/book/title'
AS SQL VARCHAR(100)
CREATE INDEX MYDOCSIDX ON MYDOCS(DOC)
GENERATE KEY USING XMLPATTERN
'declare namespace b="http://www.foobar.com/book/";
declare namespace c="http://acme.org/chapters";
/b:book/c:chapter/@number'
AS SQL DOUBLE
CREATE UNIQUE INDEX IDXPROJEST ON PROJECT (PROJNO) INCLUDE (PRSTAFF)