DB2 10.5 for Linux, UNIX, and Windows

XML base table row storage

You can optionally store smaller and medium-size XML documents in the row of the base table instead of storing them in the default XML storage object. Row storage of XML documents is similar to how a structured type instance can be stored inline in the row of a table.

Before you enable base table row storage, you need to decide how much row space you want to dedicate to row storage for each XML column. How much space you can dedicate depends on the maximum row size available, which in turn depends on the page size of the table space in which the table is created, and on the other columns you specify as part of the table. To calculate the row space available to you, see Row Size and Byte Counts for XML columns with the INLINE LENGTH specified in "CREATE TABLE statement".

Enabling base table row storage

You can specify that XML documents should be stored in base table rows instead of the default XML storage object when you create a table with an XML column, or when you alter an existing table with an XML column. To enable base table row storage, you need to include the INLINE LENGTH keywords with your CREATE TABLE or ALTER TABLE statement for each XML column that should use row storage, followed by the maximum size of XML documents in bytes to store in the base table row.

Note that altering the XML column of an existing table will not move the XML documents already stored in that column into base table rows automatically. To move the XML documents, you must update all XML documents with an UPDATE statement.

Restrictions

Base table row storage is available only for XML documents with an internal representation of 32 KB or less (smaller, if your row size is less), minus the required byte count overhead for an XML column with the INLINE LENGTH option specified. The 32 KB size assumes the table space page size is 32 KB. When you store XML documents that exceed the specified inline length, the oversize documents are stored automatically in the default XML storage object.

Once you have specified an inline length for an XML column, you can only increase the inline length size to use for row storage of XML documents, but not reduce it.

Examples

The following example enables base table row storage of XML documents for the XML column DESCRIPTION of the PRODUCT table in the SAMPLE database. This example sets the maximum inline length of XML documents to be stored in the base table row to 32000 bytes, which leaves room for the additional space required by overhead. Using the value 32000 bytes assumes the table space page size is 32 KB. After the XML column has been altered, an UPDATE statement moves the XML documents into the base table row.

ALTER TABLE PRODUCT
   ALTER COLUMN DESCRIPTION 
   SET INLINE LENGTH 32000

UPDATE PRODUCT SET DESCRIPTION = DESCRIPTION

The following example creates a table MYCUSTOMER similar to the CUSTOMER table of the SAMPLE database, except that base table row storage is specified for the XML column Info. Documents that have an internal representation of 2000 bytes or less will be stored in the base table row when inserted into the Info column.

CREATE TABLE MYCUSTOMER (Cid BIGINT NOT NULL,
   Info XML INLINE LENGTH 2000,
   History XML,
   CONSTRAINT PK_CUSTOMER PRIMARY KEY (Cid)) in IBMDB2SAMPLEXML