Db2 LOB and XML storage overview

Large values, logical columns

From an application view, you declare a column as BLOB, CLOB, DBCLOB, or XML, then INSERT and SELECT it. From a storage view, Db2 usually keeps the bulky payload in separate table spaces tied to the base table. That split protects base-row density, improves how utilities and buffer pools can be tuned, and still presents one logical table to SQL.

Inline LOB options and version-specific limits exist—small LOBs can sometimes live closer to the base row—but the mental model to learn first is: base table for control and regular columns, auxiliary/XML spaces for the heavy data. When something goes wrong with space or COPY, DBAs look at those related objects, not only the base table space name you use in everyday talk.

LOB storage: auxiliary tables and LOB table spaces

A table may contain a LOB column, yet the actual LOB data is usually stored in another table called an auxiliary table. That auxiliary table lives in a LOB table space. One auxiliary table must exist for each LOB column (and partitioned designs multiply the pattern per partition rules). The base table includes a ROWID column that Db2 uses to locate data in the auxiliary table. The auxiliary table must have exactly one index—the auxiliary index—typically keyed for that ROWID linkage.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE HR.EMP_DOCS (
    EMPNO      CHAR(6) NOT NULL,
    DOC_ID     INTEGER NOT NULL,
    RESUME     CLOB(10M),
    PHOTO      BLOB(5M),
    PRIMARY KEY (EMPNO, DOC_ID)
) IN APPDB.EMPDOC_TS;
 
-- Db2 often creates LOB table spaces, auxiliary tables,
-- and auxiliary indexes implicitly for RESUME and PHOTO.
-- In some cases you CREATE LOB TABLESPACE / AUXILIARY TABLE
-- / INDEX explicitly in the same database as the base table.

Implicit versus explicit creation

For many modern table space designs, defining a LOB column is enough: Db2 implicitly creates the LOB table space, auxiliary table, and auxiliary index. Explicit creation is required in certain situations—classic examples involve specific partitioned layouts where you must create one LOB table space and auxiliary table per partition for each LOB column. When explicit, you use CREATE LOB TABLESPACE, CREATE AUXILIARY TABLE, and CREATE INDEX, keeping LOB spaces in the same database as the base table.

What applications still do

Application programmers usually do not name auxiliary tables in SELECT lists. They host-variable a CLOB or use LOB locators so they can work with pieces of a large value without always materializing everything in program storage. Host-language details belong in later data-type pages; the storage takeaway is that SQL names the base column, while Db2 navigates ROWID to auxiliary pages behind the scenes.

XML storage: separate XML table spaces

pureXML is Db2 for z/OS support for storing and querying XML. The XML column type holds documents. To manage XML efficiently alongside traditional SQL types, Db2 stores XML data in separate table spaces from the base table that contains the XML column. The underlying mechanism is transparent to applications: you do not pick the XML table space name on every INSERT.

When you create an XML column, Db2 implicitly creates an XML table space, an XML table to store the data, and related identifiers such as a node ID used internally. Each XML column gets its own table space. For partitioned bases, XML data is associated with the corresponding base partition. XML table spaces also have their own index spaces when indexes are defined—IBM notes that utility implications resemble the LOB story because large data again lives outside the base space.

1
2
3
4
5
6
7
8
CREATE TABLE SALES.ORDER_XML (
    ORDER_ID   BIGINT NOT NULL,
    ORDER_DOC  XML,
    PRIMARY KEY (ORDER_ID)
) IN SALESDB.ORDXML_TS;
 
-- Applications insert XML into ORDER_DOC.
-- Db2 maintains XML table space objects behind the scenes.

XML versus LOB: same idea, different objects

• Similar — bulky data outside base pages; implicit spaces; utility awareness
• Different — LOBs use auxiliary tables/LOB table spaces; XML uses XML table spaces and XML indexes/functions
• Query model — XML enables XML-specific predicates and publishing functions; LOBs are large typed strings/bytes

Do not store XML “only as a CLOB” if you need pureXML features—and do not assume a BLOB is queryable as structured XML. Choose the type that matches how you will use the data.

Planning, utilities, and performance

LOB and XML columns change operational cost even when SQL looks simple. Image copies must cover related spaces. REORG and CHECK utilities need correct scope. Recovering a base table space without its LOB/XML companions leaves logical wreckage. Buffer pool sizing may isolate large object pages from high-churn transactional pages so a flood of document fetches does not evict hot index leaf pages.

On the application side, SELECT * that pulls multi-megabyte CLOBs in an online transaction is a self-inflicted outage pattern. Project only the columns you need; use locators or progressive chunking patterns when working with huge values; and avoid logging surprises by understanding whether a LOB table space is logged and how your utility strategy handles it.

1
2
3
4
5
6
7
8
9
10
11
12
13
Logical table HR.EMP_DOCS
  |
  +-- base table space pages: EMPNO, DOC_ID, ROWID, indicators
  |
  +-- LOB table space(s): auxiliary table for RESUME (CLOB)
  |
  +-- LOB table space(s): auxiliary table for PHOTO (BLOB)
 
Logical table SALES.ORDER_XML
  |
  +-- base table space pages: ORDER_ID + XML column control
  |
  +-- XML table space: document storage for ORDER_DOC

Explain It Like I'm Five

Imagine each employee row is a folder on a shelf. The folder holds a small card with the employee number (ordinary columns). The thick photo album and the long life-story book do not fit in the folder, so they go in labeled crates in the back room (LOB or XML spaces). A sticker on the folder (ROWID / linkage) tells Db2 which crate belongs to which card. You still ask for “Sam’s photo” by name, but the librarian walks to the back room to fetch the heavy book.

Exercises

1. Draw the base table, LOB table space, auxiliary table, and auxiliary index for one CLOB column.
2. In one paragraph, contrast LOB auxiliary storage with XML table space storage.
3. Why might SELECT * be dangerous on a table with two 50 MB BLOB columns in an online CICS transaction?
4. Name two utility or recovery concerns that appear once LOB/XML spaces exist.
5. When would you store a document as CLOB instead of XML, and vice versa?