Db2 LOB types overview

LOB types at a glance

String data in Db2 includes character, graphic, and binary strings—and each family has a large object form. LOBs are still “strings” in the type system, but size and storage differ sharply from CHAR/VARCHAR.

Exact limits and defaults come from the SQL reference for your Db2 version; remember the order of magnitude: LOBs are for big values. IBM recommends not defining VARCHAR(n) or CLOB(n) unless n is at least about 18 characters—short codes belong in CHAR/VARCHAR, not LOBs.

CLOB

CLOB (character large object) holds varying-length character data—large text documents, XML-as-text (when not using the XML type), JSON text, comments, or contract language. Character semantics and CCSID considerations apply as with other character types (subtypes such as SBCS/MIXED; BIT is not used for CLOB the way it is for some CHAR/VARCHAR bit-data cases).

1
2
3
4
5
CREATE TABLE POLICY (
    POLICY_ID   INTEGER      NOT NULL,
    POLICY_TEXT CLOB(2M)     NOT NULL,
    PRIMARY KEY (POLICY_ID)
);

Prefer CLOB when size can grow far beyond page-friendly VARCHAR limits. Prefer VARCHAR when values are modest and you want simpler host-variable handling without LOB locators.

Inline LOBs (awareness)

Db2 can store a portion of a LOB inline in the base table row for smaller values, with the rest in LOB storage—details are advanced. Conceptually: small LOBs can be cheaper to touch; huge LOBs stay out of the way of ordinary row pages.

BLOB

BLOB (binary large object) holds varying-length binary bytes. Use BLOB for images, PDFs, encrypted blobs, compressed archives, or any payload where character conversion would destroy meaning.

1
2
3
4
5
CREATE TABLE ATTACHMENT (
    ATTACH_ID   INTEGER      NOT NULL,
    FILE_BYTES  BLOB(10M)    NOT NULL,
    PRIMARY KEY (ATTACH_ID)
);

Do not store binary files in CLOB and hope CCSID conversion is a no-op. Binary stays in BLOB (or BINARY/VARBINARY for smaller fixed/variable binary strings).

DBCLOB

DBCLOB is the large-object form of graphic / double-byte character data. Where GRAPHIC/VARGRAPHIC hold DBCS strings of moderate size, DBCLOB scales to very large double-byte text—important for some East Asian text workloads and graphic encodings.

1
2
3
4
5
CREATE TABLE DOC_DBCS (
    DOC_ID    INTEGER       NOT NULL,
    DOC_BODY  DBCLOB(5M)    NOT NULL,
    PRIMARY KEY (DOC_ID)
);

If all characters are DBCS, graphic types (including DBCLOB for large data) are appropriate. For mixed SBCS/DBCS at smaller sizes, MIXED character types may apply instead—see character and graphic deep-dives.

Storage and programming mental model

• Base table — holds keys and a ROWID used to find LOB data
• LOB table space / auxiliary table — holds the large bytes/characters
• Locators — host programs often manipulate LOBs via locators instead of fetching entire objects into working storage
• Utilities — LOB spaces participate in backup/recovery designs separately from ordinary table data planning

Creating a table with LOB columns implies more objects than a simple VARCHAR column. Budget DBA design time for LOB table spaces, buffer pools, and application APIs—not only the column type keyword.

Choosing quickly

• Text, huge → CLOB
• Bytes, huge → BLOB
• DBCS text, huge → DBCLOB
• Text or bytes, small/medium → VARCHAR / VARBINARY / VARGRAPHIC first

Explain It Like I'm Five

A normal pocket notebook is VARCHAR—short notes. A CLOB is a whole library shelf of words. A BLOB is a sealed box of Lego bricks (bytes) you should not shake into letters. A DBCLOB is a huge notebook written in a double-wide alphabet. The notebook cover stays on your desk (base row); the big shelf lives in a special storeroom (LOB space).

Exercises

1. Pick CLOB, BLOB, or DBCLOB for: (a) JPEG photo, (b) 50-page English contract, (c) large DBCS manuscript.
2. Why is CLOB(10) usually a poor design for a status code?
3. Name one programming reason LOB locators exist.
4. How does a ROWID relate to LOB columns at a high level?
5. When would VARBINARY be enough instead of BLOB?