DB2 XML columns and storage (pureXML)

The XML data type and XML columns

The XML data type defines columns that hold XML values. Most SQL statements accept the type: CREATE TABLE, ALTER TABLE ADD, CREATE INDEX over XML, triggers, INSERT, UPDATE, and DELETE. You can replace a whole document or, with XML functions covered on later pages, update pieces of it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE SALES.ORDER_XML (
    ORDER_ID   BIGINT NOT NULL,
    ORDER_DOC  XML,
    PRIMARY KEY (ORDER_ID)
);
 
INSERT INTO SALES.ORDER_XML (ORDER_ID, ORDER_DOC)
VALUES (
  1001,
  XMLPARSE(DOCUMENT CAST(
    'WIDGET'
    AS CLOB)
  )
);
 
SELECT ORDER_ID,
       XMLSERIALIZE(ORDER_DOC AS CLOB) AS ORDER_TEXT
  FROM SALES.ORDER_XML
 WHERE ORDER_ID = 1001;

XML values are not a string type. They are not compared to VARCHAR with ordinary =. Character data in the internal form uses UTF-8. To cross into text, use XMLSERIALIZE or fetch into a string/binary/XML host variable. To cross in from text, use XMLPARSE or insert from an XML application type.

Serialized XML stored in or retrieved from an XML column is limited to 2 GB. IBM describes internal size as not having the same architectural cap — plan the serialized path your programs actually use.

A table may have several XML columns plus ordinary relational columns. You can also extract nodes into relational tables with XMLTABLE and INSERT…SELECT if you need joins and foreign keys on values that started life inside the document.

XML storage structure

IBM’s storage model for XML is similar to LOB storage: the base table that contains the XML column lives in a different table space from the table that contains the XML data. The difference is that you do not CREATE AUXILIARY TABLE for XML. When you create or ALTER ADD an XML column, Db2 implicitly creates the XML objects. Applications never name the XML table space on INSERT.

• One XML table space per XML column — two XML columns means two XML table spaces.
• Same database as the base table.
• Unicode UTF-8 encoding for the XML table space, even if the base table is EBCDIC.
• No limit keys on the XML table space. For a partitioned base, XML data sits in the partition number that matches the base row.

If the base table space supports XML versions, each XML table has extra START_TS and END_TS columns (BINARY(8) or BINARY(10) depending on 6-byte vs 10-byte page format). They hold the RBA/LRSN of logical creation and deletion of an XML record so several versions of a document can coexist. The node ID index key gains the same columns.

If an edit procedure is defined on the base table, the XML table inherits it. Implicit XML table space attributes are copied from the base (and from the first logical partition) so DSSIZE, SEGSIZE, compression, and locking behavior stay in family:

Document ID index and node ID index

These indexes are not optional decorations. The document ID index is a unique index that maps document ID to the base table RID. If the base is partitioned, it is a non-partitioned secondary index (NPSI). The node ID index is an extended non-partitioning index on the XML table. Db2 uses it to keep document order and to map logical node IDs to physical record IDs.

Do not DROP them to “save space.” You would break XML access. RUNSTATS and COPY should include their index spaces along with the XML table space.

XML indexes (XMLPATTERN)

User-defined XML indexes are how you make “find orders where /order/status = 'OPEN'” cheap. You specify an XML pattern — a limited XPath — and Db2 generates keys from matching nodes.

1
2
3
4
CREATE INDEX SALES.ORDXML_STATUS
  ON SALES.ORDER_XML (ORDER_DOC)
  GENERATE KEY USING XMLPATTERN '/order/status'
  AS SQL VARCHAR(20);

Design notes:

• Index only nodes the workload predicates on. Each extra XML index is maintained on every document INSERT/UPDATE/DELETE.
• The AS SQL type (VARCHAR, DECFLOAT, TIMESTAMP, and so on) must match how you compare in XMLQUERY / XMLEXISTS. A VARCHAR index will not help a numeric comparison.
• XMLPATTERN is not a substitute for shredding. If you join this document to CUSTOMER on cust-id all day, a relational CUST_ID column (filled by trigger or XMLTABLE) may still be simpler than an XML index.
• EXPLAIN still matters. An XML index is a matching tool, not a guarantee.

XML performance

Fetching XMLSERIALIZE(doc AS CLOB) for every row in a 2-million-row table is the XML equivalent of SELECT * on a CLOB. Project ORDER_ID and a status column (relational or XMLQUERY) for lists. Open the document on the detail transaction.

Parsing on INSERT (XMLPARSE, implicit parse from a string, XMLVALIDATE) is CPU. If the same document is stored many times, validate once at the gateway. If documents vary wildly in size, watch XML table space DSSIZE and the number of partitions on the base: XML volume follows base partitions but can dwarf them.

Binary XML (XDB) client formats exist so JDBC/ODBC can avoid extra parse/serialize on the wire. That is an application driver setting, not a column attribute, but it belongs in a performance review next to XML indexes.

XML backup and recovery

Utility support for XML is modeled on LOB support. XML data is not in the base table space. XML table spaces have their own index spaces. Implications:

• COPY the base table space and every XML table space (and the document ID / node ID / XMLPATTERN index spaces your recovery procedure includes). A base-only copy cannot restore documents.
• RECOVER those spaces to the same RBA/LRSN or the same image-copy set. Recovering the base to Tuesday and XML to Monday yields rows whose documents do not match.
• REORG XML table spaces when they grow or fragment; REORG of the base does not reclaim XML pages.
• RUNSTATS on XML table spaces and XML indexes so access path choice sees document volume.
• LOAD / UNLOAD support XML. Spanned records and file reference patterns resemble LOB handling — size the output before the first unload of a 2 GB serialized document.
• After point-in-time recovery or conditional restart, verify base and XML synchronization the way you run CHECK LOB / CHECK DATA for LOBs. XML-specific check utilities and advisory statuses depend on version; the operational idea is the same: do not assume the pair is consistent until you check.

Disaster-restart runbooks that list table spaces by name must be regenerated after the first XML column is added. Implicit names are easy to miss in a handwritten COPY list. LISTDEF with a pattern on the database, or a catalog query of SYSTABLESPACE for the XML spaces, is safer than memory.

XML column versus CLOB versus shredding

• XML type — you need structure, XML indexes, validation, or XMLQUERY / XMLTABLE. Storage cost is the XML table space family.
• CLOB of XML text — archive or pass-through. No XML intelligence, simpler objects (LOB auxiliary instead of XML node indexes).
• Shred to relational columns — best for values you constrain, join, and aggregate. The original document can still sit in XML or CLOB beside the shredded row.

Schema validation and the XML schema repository (XSR) are covered on the XML schema pages. Storage takeaway: validation does not change the fact that the document lives in the XML table space; it only changes whether INSERT is allowed.

Explain It Like I'm Five

An XML document is a set of labeled boxes inside boxes. The XML column is a special shelf for those boxes. Db2 does not cram the boxes onto the same notebook page as the order number; it builds a second warehouse (XML table space) and keeps two card catalogs: one that finds the notebook row from the document (document ID index) and one that finds each box inside the warehouse (node ID index). If you often ask “which orders are OPEN?”, you can add a sticky index on the status box (XMLPATTERN). Backup means photographing both the notebook and the warehouse on the same day, or the story and the boxes will not match.

Exercises

1. Write CREATE TABLE with BIGINT ORDER_ID and XML ORDER_DOC. List the implicit objects Db2 will add.
2. Why is the XML table space UTF-8 even when the base table is EBCDIC?
3. Write an XMLPATTERN index on /order/status as VARCHAR(20).
4. Give one reason a list screen should not SELECT XMLSERIALIZE(ORDER_DOC AS CLOB) for every row.
5. Your COPY job lists only the base table space. What goes wrong at RECOVER time?
6. When would you store the same business message as XML and also as shredded relational columns?