DB2 Version 10.1 for Linux, UNIX, and Windows

Restrictions on the pureXML feature

The pureXML® feature is subject to certain restrictions including restrictions on XML column definitions, restrictions when attaching a table to a partitioned table, and restrictions in a partitioned database environment.

Restrictions on XML column definitions

XML columns are subject to the following restrictions:

When creating an index on an XML column, the GENERATE KEY USING XMLPATTERN clause must be used, and the index cannot be part of a composite index. Multiple indexes can be created on an XML column.
XML columns can be referenced in CHECK constraints only in conjunction with a VALIDATED predicate. See the related links at the end of this topic for information about CHECK constraints on XML columns.
XML columns cannot have a default value specified by the WITH DEFAULT clause; if the column is nullable, the default for the column is NULL
XML columns cannot be used in a range-clustered table (RCT).
XML columns cannot be included as columns of keys, including primary, foreign, and unique keys, dimension keys (in the ORGANIZE BY DIMENSIONS clause) of multidimensional clustering (MDC) tables, sequence keys of range-clustered tables, table-partitioning keys of partitioned tables, and distribution keys of a table in a partitioned database environment.
A partitioned table with XML columns must have at least one non-XML column with a data type that is supported for use as a table-partitioning key column.
XML columns cannot be included in typed tables and typed views.
XML columns cannot be referenced in generated columns.
XML columns cannot be specified in the select-list of scrollable cursors.
XML columns cause cursor blocking to be disabled when retrieving XML data.
When dropping XML columns using the ALTER TABLE statement, you must drop all of the XML columns in the table in a single ALTER TABLE statement.
For DB2® V9.7 Fix Pack 1 and later releases, distribution statistics can be collected on indexes over XML data defined on an XML column. The following restrictions apply to the collection of distribution statistics on an XML column:
- Distribution statistics are collected for each index over XML data specified on the XML column. The data type specified for the index must be VARCHAR, DOUBLE, INTEGER, DECIMAL, TIMESTAMP, or DATE. Distribution statistics are not collected for indexes over XML data of type VARCHAR HASHED.
- Distribution statistics for each index over XML data uses a maximum of 250 quantiles as the default. The default can be changed by specifying a value with the NUM_QUANTILES parameter in the ON COLUMNS or the DEFAULT clause when issuing the RUNSTATS command. The num_quantiles database configuration parameter is ignored while collecting XML distribution statistics.
- XML distribution statistics are not created when loading data with the STATISTICS option.
- XML distribution statistics are not collected for partitioned indexes over XML data defined on a partitioned table.

Restriction on triggers

In a trigger body of a BEFORE or AFTER trigger, transition variables that reference columns of type XML in affected rows can be used only for validation with the XMLVALIDATE function, to set XML column values to NULL, or to leave XML columns values unchanged.

Restrictions when attaching partitions to partitioned tables

When using ALTER ATTACH to attach a partition to a partitioned table with XML columns, the INLINE LENGTH of each XML column on the table to attach (the source table) must match with the INLINE LENGTH of the corresponding XML columns on the table attaching to (the target table).

If a table contains XML columns that use the Version 9.5 or earlier XML record format, attaching the table to a partitioned table that contains XML columns that use the Version 9.7 or later record format is not supported. Before attaching the table, you must update the XML record format of the table to match the record format of the target partitioned table. Either of the following two methods updates the XML record format of a table:

Perform an online table move on table using the ADMIN_MOVE_TABLE procedure.
Perform the following steps:
1. Use the EXPORT command to create a copy of the table data.
2. Use the TRUNCATE statement to delete all the rows from the table and release the storage allocated to the table.
3. Use the LOAD command to add the data into the table.

After the XML record format of the table has been updated, attach the table to the target partitioned table.

Restrictions in a partitioned database environment

When using the pureXML feature in a partitioned database environment, the following rules apply:

An XML column cannot be used as the distribution key. As a result, the following restrictions apply:
- A table with only XML columns cannot be distributed.
- A table with a distribution key cannot have primary key, unique constraint, or unique index defined on an XML column.
- A table with a distribution key with XML columns must have at least one non-XML column with a data type that is supported for use as distribution key.
If you are loading XML data from XML data files into partitioned tables in parallel, then the XML data files must be read-accessible to all the partitions where loading in parallel is taking place.
When using a CURSOR file type with the LOAD command to load XML data into a multipartition database, the PARTITION_ONLY and LOAD_ONLY modes are not supported.
When using the REDISTRIBUTE DATABASE PARTITION GROUP command with the NOT ROLLFORWARD RECOVERABLE option, the redistribute operation uses the INDEXING MODE DEFERRED option for tables that contain XML columns. If a table does not contain an XML column, the redistribute operation uses the indexing mode specified when issuing the command.
Tables that contains XML columns that use the Version 9.5 or earlier XML record format cannot be redistributed. Use the ADMIN_MOVE_TABLE stored procedure to convert the table to the new format.

Restriction on data row compression

The COMPRESS YES option of the ALTER TABLE or CREATE TABLE statement enables data row compression for a table. Compression of data in the XML storage object of a table is not supported if the table contains XML columns that use the Version 9.5 or earlier XML record format. If you enable such a table for data row compression, only the table row data in the table object is compressed.

If a table is enabled for data row compression but the XML storage object of the table cannot be compressed during an insert, load, or reorg operation, a message is written to a db2diag log file.

To make the data in the XML storage object of the table eligible for compression, use the ADMIN_MOVE_TABLE stored procedure to convert the table to the new format and then enable data row compression for the migrated table.

Additional restrictions

Using serialized XML data: Although there is no architectural limit on the size of an XML value stored in the database, serialized XML data that is exchanged with the database is effectively limited to 2 GB.

Using the RUNSTATS command: If an ALLOW READ ACCESS load operation with XML data is completed and leaves a table in set integrity pending state, it is possible to issue a RUNSTATS command against this table. In this scenario, the RUNSTATS operation cannot see the invisible XML index keys from the previous load operation and returns an error. The workaround is to run the SET INTEGRITY statement before the RUNSTATS command.

Using the LOAD command: When using the LOAD command to load XML data, using the FOR EXCEPTION clause to specify a load exception table is not supported in the following cases:

When using label-based access control (LBAC).
When loading data into a partitioned table.

Creating indexes on XML columns: Additional restrictions exist for creating indexes on XML columns and for transforming with XSLT style sheets. See the related reference topic for more information.See "Restrictions on indexes over XML data" and "Important considerations for transforming XML documents" for more information.

Using maxOccurs attribute values greater than 5000: In DB2 Version 9.7 Fix Pack 1 and later, if an XML schema that is registered in the DB2 XSR uses the maxOccurs attribute where the value is greater than 5000, the maxOccurs attribute value is treated as if you specified "unbounded". Because document elements that have a maxOccurs attribute value that is greater than 5000 are processed as if you specified "unbounded", an XML document might pass validation when you use the XMLVALIDATE function even if the number of occurrences of an element exceeds the maximum according to the XML schema that you used to validate the document. For additional information and a suggested workaround, review the XMLVALIDATE function information.

Using the RESTORE DATABASE command: You cannot use the TRANSPORT option to transport table spaces and SQL schemas if any tables within the schema contains an XML column.