The pureXML feature and data organization schemes

Using the pureXML® feature with data organization schemes such as database partitioning environments, table partitioning, and multidimensional clustering, can significantly improve query performance and reduce the overhead of data maintenance operations such as reorganization, insertion, and deletion.

There are three clauses of the CREATE TABLE statement that include an algorithm to indicate how the data should be organized:
  • DISTRIBUTE BY to spread data evenly across database partitions in a partitioned database environment (database partitioning).
  • PARTITION BY to specify the table partitioning scheme for a table (table partitioning).
  • ORGANIZE BY DIMENSIONS to specify a dimension for each column or group of columns used to cluster the table data (multidimensional clustering).

See the related links at the end of this topic for information about table partitioning and data organization schemes and restrictions when using the pureXML feature.

Partitioned database environments

When using tables in a partitioned database environment, tables containing XML columns can be stored in a multi-partition database on multiple machines. The XML data can be managed using the Db2® pureXML feature.

When the XML data is distributed across database partitions, multiple processors on multiple machines can process requests for information. Data retrieval and update requests are decomposed automatically into sub-requests, and executed in parallel among the applicable database partitions.

Partitioned tables

A partitioned table can contain one or more columns with the XML data type and one or more indexes over XML data. The user-created indexes over XML data can be partitioned or nonpartitioned. A partitioned index reduces the overhead of data maintenance operations in environments when you use the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement for the roll-in and rollout of table data.

Multidimensional clustering tables

A multidimensional clustering (MDC) table can contain one or more columns with an XML data type, and one or more indexes over XML data on an XML column can be created. An index over XML data can be used with an MDC block index to improve query performance. Also, index ANDing can be performed using an MDC block index with an index over XML data.