DB2 LOBs: BLOB, CLOB, DBCLOB, and how they are stored

BLOB, CLOB, and DBCLOB

A LOB is a varying-length value that can be far larger than VARCHAR or VARGRAPHIC. Maximum length is about 2 GB. You declare a length so Db2 and the application know the ceiling; you do not pay 2 GB per row.

1
2
3
4
5
6
7
CREATE TABLE HR.EMP_DOCS (
    EMPNO   CHAR(6)  NOT NULL,
    RESUME  CLOB(10M),
    PHOTO   BLOB(5M),
    NOTES   DBCLOB(1M),
    PRIMARY KEY (EMPNO)
) IN APPDB.EMPDOC_TS;

A table may have many LOB columns. Each table that has at least one LOB also needs a ROWID (explicit or implicitly generated). ROWID is how Db2 finds the matching auxiliary row. Do not confuse ROWID with an identity column; it is a separate type for row identity and LOB linkage.

Compared with VARCHAR, use a LOB when the value can grow past ordinary page-size string limits or when you want locator/file-reference access. A 40-byte status code is still CHAR or VARCHAR.

LOB storage: table spaces, auxiliary tables, and LOB indexes

One page of a LOB table space never holds more than one LOB; one LOB may span many pages. An auxiliary table stores one LOB column of the base table. If the base table space is partitioned and you have two LOB columns and ten partitions, you are looking at twenty LOB table spaces, twenty auxiliary tables, and twenty auxiliary indexes. That arithmetic is why shops care about implicit creation and about not adding LOB columns casually to a 1000-partition table.

Implicit versus explicit creation

On modern universal table spaces, defining the LOB column is often enough: Db2 implicitly creates the LOB table space, auxiliary table, and auxiliary index in the same database as the base table. You CREATE AUXILIARY TABLE and CREATE LOB TABLESPACE explicitly when the layout requires it (classic partitioned cases). The auxiliary CREATE does not list user columns the way CREATE TABLE does; you name the base table and LOB column and Db2 builds the structure. The auxiliary index is the same: you name the auxiliary table; Db2 generates the key.

Inline LOBs do not remove this requirement. Even if every value fits in the base row, the auxiliary objects must exist or the table definition is incomplete and INSERT fails.

Inline LOB

INLINE LENGTH stores a prefix of the LOB in the base table space with the other columns.

• If the LOB length is less than or equal to the inline length, the whole value can live in the base table space. Access need not touch the LOB table space or auxiliary index.
• If the LOB is longer, the prefix is inline and the remainder is auxiliary. Any process that needs the full value reads both spaces.

Valid INLINE LENGTH: 0–32680 bytes for BLOB and CLOB, 0–16340 characters for DBCLOB. Subsystem parameter LOB_INLINE_LENGTH (0–32680, default 0) sets the default for new LOB columns in universal table spaces. You can also specify INLINE LENGTH on CREATE TABLE or ALTER TABLE ADD.

1
2
3
4
CREATE TABLE HR.EMP_DOCS (
    EMPNO  CHAR(6) NOT NULL,
    RESUME CLOB(10M) INLINE LENGTH 200
) IN APPDB.EMPDOC_TS;

A resume whose first 200 bytes are what the list screen shows is a classic inline win: the list query stays on the base pages; the interview program fetches the rest.

LOB locators versus actual LOB values

An actual LOB value is the bytes (or characters) themselves, in a host variable, a file, or the table. A locator is a small token that refers to a LOB value the Db2 server is currently holding. The program does not own the 10 MB string; it owns a 4-byte handle.

1
2
3
4
5
6
7
8
9
10
11
       EXEC SQL
            SELECT RESUME INTO :RESUME-LOC
            FROM HR.EMP_DOCS
            WHERE EMPNO = :EMPNO
       END-EXEC.
       EXEC SQL
            SET :RESUME-LEN = LENGTH(:RESUME-LOC)
       END-EXEC.
       EXEC SQL
            FREE LOCATOR :RESUME-LOC
       END-EXEC.

In COBOL, declare locators with USAGE IS SQL TYPE IS CLOB-LOCATOR, BLOB-LOCATOR, or DBCLOB-LOCATOR (PL/I uses CLOB_LOCATOR and friends). FETCH or SELECT INTO the locator assigns the token. You can pass the locator into SUBSTR, POSSTR, LENGTH, INSERT, or UPDATE so the server copies or slices without shipping the whole object to the address space.

Locators belong to the transaction. COMMIT or ROLLBACK drops them unless you issued HOLD LOCATOR. FREE LOCATOR releases one or more locators early — do that in loops so the server does not accumulate held LOBs until commit. HOLD is for the case where you COMMIT but must keep talking about the same LOB in the next unit of work.

LOB host variables and file references

Three application representations:

• LOB host variable — SQL TYPE IS CLOB(n) / BLOB(n) / DBCLOB(n). The program allocates a buffer. Fine for modest sizes; dangerous for “up to 2 GB.”
• LOB locator — token only. Best for server-side operations and streaming slices.
• LOB file reference — SQL TYPE IS CLOB-FILE / BLOB-FILE / DBCLOB-FILE. The host variable describes a file (name, name length, file options). Db2 reads or writes the file directly. Options include read, create, and overwrite. The file reference represents the file the way a locator represents a LOB; it does not contain the bytes in the program.

File references are how you INSERT a document from USS or a data set without a giant working storage field, and how you SELECT a CLOB out to a file. LOAD and UNLOAD also use file reference variables in SYSREC when each LOB value lives in its own file (PDS/PDSE member or HFS). Db2 10 added SPANNED / RECFM=VBS so LOBs can ride in the SYSREC data set itself instead of sidecar files.

Inserting, retrieving, updating, and streaming

INSERT supplies a host variable, a locator, a file reference, or a LOB expression (SUBSTR of another LOB, a string cast to CLOB, and so on). UPDATE replaces the whole LOB or a piece via OVERLAY / SET col = SUBSTR(…) CONCAT new-piece CONCAT …. There is no “byte 5000 of this CLOB is now X” row-store operation independent of those expressions; you build a new value.

Retrieval: SELECT the column into a host variable (materialize), into a locator (handle), or into a file reference (write a file). Streaming means: take a locator, loop SUBSTR with a sliding offset, process a chunk, FREE when done. That pattern keeps virtual storage flat and is the usual advice for COBOL and stored procedures that must not allocate 2 GB.

Never SELECT a LOB column in a list screen “just in case.” Each fetch may drag auxiliary pages. Project the LOB only on the detail path, or project LENGTH and an inline prefix.

LOB functions

These functions accept locators. LENGTH(:locator) is the standard way to learn size before you decide whether to materialize. CAST between CLOB and VARCHAR is possible within limits; do not CAST a 10 MB CLOB to VARCHAR(32704) and hope.

LOB logging, compression, and performance

A LOB table space can be defined with LOG YES or LOG NO. LOG YES logs LOB data changes (still expensive at 2 GB). LOG NO skips logging the LOB payload (system pages are another matter): recovery then depends on image copies, and some operations leave the space in a recover-pending type of state if you do not copy. Match LOG on the LOB space to your recovery contract. Inline bytes live in the base table space and follow the base space’s logging.

Compression: LOB table spaces can use compression (including zEDC-related options on supported levels). Compressing huge text CLOBs often pays; compressing already-compressed JPEG BLOBs often does not. COMPRESS on the base table space does not compress the auxiliary LOB pages.

Performance checklist:

• Inline the prefix the workload actually reads
• Separate buffer pools for LOB table spaces when they are large and sequential
• Locators and chunked SUBSTR instead of full materialize
• Do not index-scan a table and fetch every CLOB
• WATCH COPY/REORG elapsed time: auxiliary spaces dominate
• Avoid NOT LOGGED unless you have a copy strategy and understand AUXW/CHKP

LOB recovery and utilities

Treat the base table space and its LOB table spaces as one recovery set. COPY both. RECOVER both to the same point. A point-in-time recover of only the base leaves auxiliary data from a different time — CHECK-pending and broken documents.

• COPY / RECOVER — include every LOB table space; SHRLEVEL CHANGE COPY is common for large LOB spaces
• REORG — reorganize LOB table spaces as well as the base; REORG of the base does not reclaim auxiliary space by itself
• CHECK LOB — finds structural defects and invalid LOB values. Run it before CHECK DATA on a table with LOB columns; after conditional restart or PIT recovery; on AUXW (invalid LOBs) or CHKP (structural). A clean CHECK LOB can reset those states. SHRLEVEL CHANGE CHECK LOB reports problems without setting CHKP/AUXW
• CHECK DATA — after CHECK LOB, verifies base-to-auxiliary relationships
• LOAD / UNLOAD — LOBs go to sidecar files (file reference variables in SYSREC) or to a SPANNED SYSREC. Plan PDS/PDSE, HFS, or VBS space before the first unload of a 2 GB column
• REPAIR — last-resort fix of auxiliary pages; prefer CHECK + COPY/RECOVER

RUNSTATS on base plus LOB spaces keeps the optimizer honest about auxiliary I/O. Skipping LOB stats is a common reason a “simple” SELECT of a CLOB looks cheap in your head and expensive on the accounting report.

Explain It Like I'm Five

A normal column is a sticky note on the page of a notebook. A LOB is a poster that will not fit on the page, so Db2 puts a ticket stub (ROWID) on the page and hangs the poster in a warehouse (LOB table space). Inline LOB means taping the first few inches of the poster onto the notebook page so you can read the title without walking to the warehouse. A locator is not the poster; it is a coat-check token. FREE LOCATOR gives the token back. HOLD LOCATOR lets you keep the token even after you take a break (COMMIT). CHECK LOB is walking the warehouse to see whether any posters are torn.

Exercises

1. Choose BLOB, CLOB, or DBCLOB for (a) a JPEG, (b) an EBCDIC policy document, (c) a large graphic name list.
2. A PBR table has 20 partitions and 2 LOB columns. How many auxiliary tables do you expect?
3. Write a CLOB column with INLINE LENGTH 500 and explain when Db2 still reads the LOB table space.
4. In one sentence each, contrast a CLOB host variable, a CLOB locator, and a CLOB-FILE reference.
5. Why must COPY include LOB table spaces when the base table space is copied for recovery?
6. When would you run CHECK LOB before CHECK DATA?