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.

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 Command clause: ALLOW [NO | READ | WRITE] ACCESS 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.
REORG TABLE RECLAIM EXTENTS Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes.
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) Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes.
REORG INDEXES ALL FOR TABLE CLEANUP|RECLAIM EXTENTS Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes.
REORG INDEXES ALL FOR TABLE REBUILD1 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.
REORG TABLE RECLAIM EXTENTS Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes.
Note:
  1. The default behavior rebuilds the index. If you do not specify the REBUILD clause, the index is rebuilt.
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 CLEANUP|RECLAIM EXTENTS Command clause: ALLOW [READ | WRITE] ACCESS Yes.
REORG INDEXES ALL FOR TABLE REBUILD ON DATA PARTITION1 Command clause: ALLOW [READ | WRITE] ACCESS Yes.
REORG INDEXES ALL FOR TABLE REBUILD1 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.
REORG TABLE RECLAIM EXTENTS Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes.
Note:
  1. The default behavior rebuilds the index. If you do not specify the REBUILD clause, the index is rebuilt.

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

For multidimensional clustering (MDC) and insert time clustering (ITC) tables, online index reorganization (rebuild) 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.