DB2 10.5 for Linux, UNIX, and Windows

Other database objects associated with XML columns

There are two indexes associated with XML columns that are internal and system generated. The indexes are represented in the SYSCAT.INDEXES catalog view. In addition, index partitions for data partitioned tables with XML data are represented in the SYSCAT.INDEXPARTITIONS catalog view.

XML path index and XML regions index

Whenever you create an XML column, an XML path index is automatically created by DB2® on the XML column. DB2 also creates a single XML regions index for all XML columns within a table.

The XML path index records all unique paths which exist within XML documents stored within an XML column.

The XML regions index captures how an XML document is divided up internally into regions, which are sets of nodes within a page. Since regions are sets of nodes within a page, the number of regions index entries can be reduced, and performance may be improved, if a larger page size that can store more nodes within a page is used.

For a data partitioned table, the XML regions index is always a partitioned index, and the XML column path indexes are always nonpartitioned indexes.

The table space id and object id of the XML regions index in SYSCAT.INDEXES are logical values, because the regions index is always partitioned and the single entry in SYSCAT.INDEXES is a logical representative. The table space id and object id equal those of the partitioned table with which they are associated.

Every index partition associated with a data partition in a partitioned table has an entry in the SYSIBM.SYSINDEXPARTITIONS catalog table, containing information and statistics about that index partition. This information is accessed through the SYSCAT.INDEXPARTITIONS catalog view. Nonpartitioned indexes do not have any entries in this catalog table.

Both the XML path and the XML regions indexes are recorded in SYSCAT.INDEXES. Note that these indexes are not recognized by any application programming interface that returns index metadata.

These internal indexes associated with XML columns are distinct from user-created indexes over XML data. For indexing XML data as stored in XML columns, you work only with the logical indexes on XML columns, using statements such as CREATE INDEX and DROP INDEX.

Catalog views

Refer to the Related reference section for more information on these catalog views.

SYSCAT.INDEXES: Each row represents an index, including XML path and XML regions indexes. The XML path index is shown as XPTH in SYSCAT.INDEXES.INDEXTYPE, and the XML regions index is shown as XRGN in SYSCAT.INDEXES.INDEXTYPE.
SYSCAT.INDEXPARTITIONS: Each row represents an index partition associated with a data partition in a partitioned table.