Restrictions on indexes over XML data

Indexing over XML data is subject to restrictions, including data type support restrictions, concurrency level restrictions, XML list elements restrictions, and index compression restrictions.

Data type support

Every XML pattern expression that you specify in the CREATE INDEX statement must be associated with a data type. Only four SQL-based data types are supported: DATE, TIMESTAMP, VARCHAR, and DOUBLE.

Concurrency levels

Support for some concurrency levels is restricted during processing of XML columns and associated indexes. The following table specifies what concurrency levels are supported.

Table 1. Supported concurrency levels for a nonpartitioned table with at least one XML column

Command	Concurrency level	Supported
REORG INDEXES ALL FOR TABLE	Concurrent read and write access to the table	Yes.
REORG TABLE	Command clause: ALLOW [READ | NO] ACCESS	Yes. An index over XML data might exist.
REORG TABLE INPLACE (at least one index over XML data exists on the table)	Command clause: ALLOW [READ | WRITE] ACCESS	No.

Table 2. Supported concurrency levels for a partitioned table with nonpartitioned indexes and at least one XML column

Command	Concurrency level	Supported
REORG INDEX (on a nonpartitioned index over XML data)	Concurrent read and write access to the table	Yes.
REORG INDEXES ALL FOR TABLE	Command clause: ALLOW NO ACCESS or ALLOW [READ | WRITE] CLEANUP ONLY	Yes.
REORG INDEXES ALL FOR TABLE	Command clause: ALLOW [READ | WRITE] ACCESS	No.
REORG TABLE	Command clause: ALLOW NO ACCESS (for a partitioned table, the only supported access is ALLOW NO ACCESS)	Yes.
REORG TABLE INPLACE	Command clause: ALLOW [NO | READ | WRITE] ACCESS	No. For a partitioned table, the INPLACE parameter is not supported.

Table 3. Supported concurrency levels for a partitioned table with partitioned indexes and XML columns

Command	Concurrency level	Supported
REORG INDEXES ALL FOR TABLE	Command clause: ALLOW NO ACCESS	Yes.
REORG INDEXES ALL FOR TABLE	Command clause: ALLOW [READ | WRITE] ACCESS CLEANUP ONLY	Yes.
REORG INDEXES ALL FOR TABLE	Command clause: ALLOW [READ | WRITE] ACCESS 1	Yes.
REORG INDEXES ALL FOR TABLE	Command clause: ALLOW [READ | WRITE] ACCESS	No.
REORG TABLE	Command clause: ALLOW NO ACCESS (for a data partitioned table, the only supported access is ALLOW NO ACCESS)	Yes.
REORG TABLE INPLACE	Command clause: ALLOW [NO | READ | WRITE] ACCESS	No. For a data partitioned table, the INPLACE parameter is not supported.

Note:

1. If you specify the ON DATA PARTITION clause, the access clause applies only to the specified partition. You can read from and write to the rest of the table while the indexes on the specified partition are being reorganized.

See the CREATE INDEX statement and REORG INDEX/TABLE command for information about the clauses and options.

For multidimensional clustering (MDC) tables, online index reorganization with ALLOW WRITE ACCESS is not supported.

XML list elements

You cannot index list data type nodes. If a node is qualified by xmlpattern-clause and an XML schema exists that specifies that the node is a list data type, you cannot index the node. Issuing a CREATE INDEX statement on a list data type node returns an error (SQLSTATE 23526, SQLCODE -20306). Issuing INSERT and UPDATE statements also returns an error (SQLSTATE 23525, SQLCODE -20305).

UNIQUE index over XML data

In a partitioned database environment, the following rules apply to a table with one or more XML columns:

• A table with a distribution key cannot have a unique index over XML data.
• A unique index over XML data is supported only on a table without a distribution key in a single-partition database.
• If a unique index over XML data exists on a table, you cannot alter the table cannot to add a distribution key.

For a partitioned table, a unique partitioned index over XML data is not supported. If you try to create such index, you will receive error message SQL20303N (SQLSTATE=42990).

Creating indexes on XML columns is also subject to restrictions placed on the native XML data store overall.