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 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, 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:
- Use the EXPORT command to create a copy of the table data.
- Use the TRUNCATE statement to delete all the rows from the table
and release the storage allocated to the table.
- 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 migrate 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 migrate the table 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.