LOB locators in DB2 application programs

What a LOB locator is

A LOB locator is a host variable whose value represents a LOB value (or a LOB expression) that currently exists on the database server. Conceptually it is an old idea: use a small, easy-to-copy token to refer to something huge. The locator is not a disk address, not a ROWID, and not a column you CREATE TABLE with. It is never stored in the table. It cannot appear in a view definition or a check constraint as a persistent type. It exists for the application’s conversation with Db2 during a unit of work.

After you SELECT a LOB into a locator, later changes to the original row do not rewrite the value the locator already represents. The locator is tied to a value or expression, not to “whatever is in that row now.” That snapshot-like association is why locators are safe to pass around inside the same transaction even if another statement updates the source table.

Three ways to fetch a LOB, from heaviest to lightest:

• Full LOB host variable — the entire object is copied into program storage. Fine when the value is small and you truly need all of it
• LOB file reference variable — Db2 reads or writes a file rather than a giant memory buffer
• LOB locator — only the token moves to the client; pieces move later when you SUBSTR or assign into a modest host variable

If the value always fits in a few kilobytes and you always process it whole, a locator is optional. If resumes, images, or XML-sized CLOBs are involved, locators (or file references) are the normal design.

The three locator types

Match families. A CLOB column goes to a CLOB locator. A BLOB column goes to a BLOB locator. A DBCLOB column goes to a DBCLOB locator. Character types are only partially compatible with CLOB locators (you can assign a CHAR/VARCHAR to a CLOB locator in specific statements, and the reverse into CHAR/VARCHAR columns). Graphic types have the same partial story with DBCLOB locators. Binary types have it with BLOB locators. Do not treat a BLOB locator as a character string.

CLOB locators

Use a CLOB locator when the large object is character data: contracts, comments, JSON text stored as CLOB, emails, reports. Operations that make sense on character strings—POSSTR, SUBSTR, LIKE, CONCAT, LENGTH—are the usual toolkit. CCSID still applies: the CLOB has an encoding; substrings you fetch into a CHAR/VARCHAR host variable follow conversion rules. Searching for EBCDIC literals inside a Unicode CLOB is a conversion problem, not a locator problem.

1
2
3
4
5
6
7
8
9
-- Assign the CLOB to a locator, then search and slice
SELECT RESUME
  INTO :HV-CLOB-LOC
  FROM HR.EMP_RESUME
 WHERE EMPNO = '000130';
 
-- POSSTR / SUBSTR use the locator as if it were the CLOB
VALUES POSSTR(:HV-CLOB-LOC, 'Education')
  INTO :HV-START;

BLOB locators

Use a BLOB locator for bytes: images, PDF, encrypted payloads, packed files. Character functions that assume CCSID semantics are the wrong tool. You still useSUBSTR and LENGTH in the binary sense (bytes, not “characters”), and you assign pieces into BINARY/VARBINARY or BLOB host variables. Mixing a BLOB locator with a CLOB locator is a type error.

1
2
3
4
5
6
7
8
       01  TKN-VIDEO        USAGE IS SQL TYPE IS BLOB-LOCATOR.
       01  TKN-VIDEO-IND    PIC S9(4) COMP-5.
           EXEC SQL
                SELECT VIDEO_BLOB
                  INTO :TKN-VIDEO:TKN-VIDEO-IND
                  FROM MEDIA.CLIP
                 WHERE CLIP_ID = :HV-CLIP-ID
           END-EXEC.

DBCLOB locators

Use a DBCLOB locator for double-byte / graphic large objects: large GRAPHIC-oriented documents, some Unicode graphic payloads, and any DBCLOB column. Length and SUBSTR are in double-byte characters, not bytes. PL/I declarations typically use DBCLOB_LOCATOR (underscore). COBOL uses DBCLOB-LOCATOR (hyphen). The precompiler is picky about the spelling that belongs to that host language.

Declaring locators in host languages

Host languages do not have a native “locator” type. You declare them with SQL TYPE IS inside the SQL declare section. The precompiler rewrites the declaration into a 4-byte binary integer the compiler understands. In COBOL that is typically PIC S9(9) COMP-5. You must still use the name you declared with SQL TYPE IS inside EXEC SQL; in COBOL procedural statements you use the generated name if it differs.

1
2
3
4
5
      *    COBOL locator declarations (Area A / Area B)
       01  LIFE-STORY-LOCATOR USAGE IS SQL TYPE IS CLOB-LOCATOR.
       01  TKN-VIDEO          USAGE IS SQL TYPE IS BLOB-LOCATOR.
       01  WIDE-DOC-LOC       USAGE IS SQL TYPE IS DBCLOB-LOCATOR.
       01  LIFE-STORY-IND     PIC S9(4) COMP-5.

PL/I uses DECLARE ... SQL TYPE IS CLOB_LOCATOR (and BLOB_LOCATOR, DBCLOB_LOCATOR). C programs use SQL TYPE IS similarly in the declare section. REXX maps LOBs toward strings; locator-style processing is less typical in REXX than in COBOL/C/PL/I. Java applications usually use java.sql.Clob / Blob locators via JDBC rather than SQL TYPE IS.

Initialization of locator declarations is not permitted in the SQL TYPE IS statement. Do not VALUE clause a locator. Assign it with SQL.

Assigning, using, holding, and freeing

Typical life cycle:

• Assign — SELECT INTO or FETCH a LOB column (or a LOB expression) into the locator host variable
• Operate — use the locator in VALUES, SET, UPDATE, or scalar functions. Concatenating two locators can produce a third locator that represents the concatenated expression without building the full string in the program
• Materialise a piece — SUBSTR into a VARCHAR/CLOB host variable when you finally need bytes in working storage
• FREE LOCATOR — drop the association early if you are done and want the server to release resources before commit
• HOLD LOCATOR — keep the association across COMMIT when the next unit of work still needs the same server-side value

1
2
3
           EXEC SQL
                FREE LOCATOR :TKN-VIDEO, :LIFE-STORY-LOCATOR
           END-EXEC.

FREE LOCATOR is embeddable only (not dynamically prepared). If you list several locators, Db2 frees those it can even if another name in the list is invalid. Referencing a locator that was never assigned or was already freed raises an error (commonly SQLSTATE 0F001).

Without HOLD LOCATOR, every locator acquired in the unit of work is released at commit or rollback. That surprise shows up as “it worked until I committed inside the loop.” If you must commit and then keep slicing the same document, HOLD LOCATOR before the commit, or select the LOB into a locator again after the commit.

Null indicators

A locator can have an associated indicator variable, declared like any other (PIC S9(4) COMP-5 in COBOL). When the LOB column is null, Db2 sets the indicator and does not change the locator host variable. A locator never represents a null value. Always test the indicator after SELECT INTO / FETCH before using the locator. Using a leftover locator from a previous row after a null fetch is a classic bug.

Locators versus file reference variables

BLOB-FILE, CLOB-FILE, and DBCLOB-FILE (and XML file reference forms) tell Db2 to move the object to or from a file. Choose file references when the natural home of the data is a data set or HFS/zFS file. Choose locators when the program will inspect or rewrite pieces through SQL functions. You can combine patterns: locator for search, then SUBSTR a section into a file reference or a modest buffer.

Result-set locators and table locators are different tokens for different jobs (cursors returned from procedures, and transition tables). Do not confuse them with LOB locators even though the COBOL syntax also uses SQL TYPE IS.

Explain It Like I'm Five

Imagine a giant picture book that lives in the library. A locator is a tiny claim ticket the librarian gives you. You do not carry the book home. You say “read page 12 of ticket 47” and the librarian reads that page to you. CLOB tickets are for story books, BLOB tickets are for boxes of LEGO bricks (raw bits), and DBCLOB tickets are for books written with extra-wide letters. When the visit (transaction) ends, the tickets stop working unless you asked the librarian to hold them.

Exercises

1. Declare a COBOL CLOB locator and indicator, then SELECT a CLOB column into them.
2. Write FREE LOCATOR for two locator host variables.
3. Explain why a locator is valid after UPDATE of the source row in the same unit of work, but not after COMMIT (unless held).
4. Decide CLOB-LOCATOR vs BLOB-LOCATOR vs DBCLOB-LOCATOR for a JPEG image column.
5. Describe how you would extract characters 1–200 of a resume CLOB without fetching the whole document into working storage.

Frequently asked questions