DB2 Version 9.7 for Linux, UNIX, and Windows

Logical and physical indexes over XML data

When you create an index over XML data, two B-tree indexes are created, a logical index and a physical index.

The logical index contains the XML pattern information specified by the CREATE INDEX statement. The physical index has DB2® generated key columns to support the logical index and contains the indexed document values, converted to the data type specified in the xmltype-clause of the CREATE INDEX statement.

You work with an index over XML data at the logical level (with the CREATE INDEX and DROP INDEX statements, for example). Processing of the underlying physical index by DB2 is transparent to you. Note that the physical index is not recognized by any application programming interface that returns index metadata.

In the SYSCAT.INDEXES catalog view, the logical index has the index name you specified in the CREATE INDEX statement and the index type XVIL. The physical index has a system generated name and the index type XVIP. The logical index is always created and assigned an index ID (IID) first. The physical index is created immediately afterwards and is assigned the next consecutive index ID.

The relationship between logical and physical indexes is demonstrated in the following example: Consider two indexes over XML data, EMPINDEX and IDINDEX. For EMPINDEX, the logical index has the name EMPINDEX, the index ID 3, and the index type XVIL. The corresponding physical index has the system generated name SQL060414134408390, the index ID 4, and the index type XVIP.

Table 1. The relationship between logical and physical indexes
Index name (INDNAME)	Index ID (IID)	Table name (TABNAME)	Index type (INDEXTYPE)
SQL060414133259940	1	COMPANY	XRGN
SQL060414133300150	2	COMPANY	XPTH
EMPINDEX	3	COMPANY	XVIL
SQL060414134408390	4	COMPANY	XVIP
IDINDEX	5	COMPANY	XVIL
SQL060414134408620	6	COMPANY	XVIP

Catalog views

Please refer to the Related reference section for more information on each of these catalog views.

SYSCAT.INDEXES: Each row represents an index, including logical and physical indexes over XML data.
SYSCAT.INDEXXMLPATTERNS: Each row represents a pattern clause in an index over XML data.

Indexes in a database partitioned environment

You can issue CREATE INDEX and ALTER INDEX statements from any database partition.

If you create an index on a table that is partitioned across multiple data partitions, the index is also partitioned across the data partitions.

Auditing

Indexes on XML columns use the existing index object type for audits. Only the logical index is audited, and not the physical index.