Db2 ROWID

What ROWID is

You use the ROWID data type to uniquely identify rows in a Db2 subsystem. Unlike a business primary key (customer number), a ROWID is a system row identifier. Db2 generates values when you define the column as GENERATED ALWAYS or GENERATED BY DEFAULT.

ROWID columns do not store nulls—Db2 always provides a value. Explicitly coding NOT NULL is good practice; if you omit it, Db2 still treats ROWID as not nullable (the opposite of ordinary columns’ nullable default). Defaults for ROWID are always Db2-generated.

1
2
3
4
5
6
CREATE TABLE DOC (
    DOC_ID    INTEGER      NOT NULL,
    DOC_RID   ROWID        NOT NULL GENERATED ALWAYS,
    DOC_BODY  CLOB(2M)     NOT NULL,
    PRIMARY KEY (DOC_ID)
);

GENERATED ALWAYS vs BY DEFAULT

For normal application inserts into LOB tables, GENERATED ALWAYS is the usual choice—let Db2 invent the identifier. GENERATED BY DEFAULT matters when you must preserve ROWID values across unload/load or copy scenarios (still using values that Db2 originally produced—not homemade bytes).

Insert rules are strict: if the column is GENERATED ALWAYS, you cannot insert your own ROWID data. Special clauses such as OVERRIDING USER VALUE appear in advanced copy patterns—learn them when you write cross-table LOB migrations, not on day one SELECT practice.

ROWID and LOBs

A table that contains a LOB column must include a ROWID column. The ROWID is stored in the base table and is used to look up the actual LOB data in the LOB table space / auxiliary structures. That is why LOB designs always drag ROWID into the conversation even when the business key is DOC_ID or POLICY_NO.

• Business key → humans and foreign keys
• ROWID → Db2’s locator for LOB pieces and optional direct access

Do not invent ROWID values by hand for LOAD when the column is GENERATED ALWAYS. Unload values that Db2 created if you must reload them under BY DEFAULT rules.

Direct-row access

If an application selects a row from a table that contains a ROWID column, that value implicitly contains location information for the row. Using that ROWID in a later search condition can let Db2 navigate directly to the row—useful for some high-performance patterns.

1
2
3
4
5
6
7
8
9
-- Pseudocode flow
SELECT DOC_RID, DOC_ID INTO :hv-rid, :hv-id
  FROM DOC
 WHERE DOC_ID = 100;
 
-- Later, same unit of work, before COMMIT:
SELECT DOC_BODY
  FROM DOC
 WHERE DOC_RID = :hv-rid;

Requirement: use the retrieved ROWID before you commit. Commit releases the application’s claim on the table space. Afterward, REORG can change physical row locations, and a stale ROWID may not point where you expect. Direct-row access is a sharp tool— understand commit boundaries before relying on it.

IMPLICITLY HIDDEN ROWID

You can define a ROWID column with the IMPLICITLY HIDDEN attribute. Then SELECT * does not return the column; only an explicit select-list name includes it. That keeps LOB mechanics out of casual queries and application SELECT * habits while still allowing intentional access.

ROWID vs primary key

Keep roles clear. Primary keys encode business identity and relationships. ROWID encodes Db2’s row identity for storage features. You can have both. Foreign keys still reference business keys (or surrogate integers)—not ROWID—unless you have a very unusual design.

Explain It Like I'm Five

Every library book can have a secret sticker only the librarian understands (ROWID). Your book title is still the name you care about (primary key). Huge posters stored in the basement (LOBs) are found using that secret sticker. If you write the sticker down, use it before closing time (COMMIT)—overnight the shelves might be rearranged (REORG).

Exercises

1. Write a CREATE TABLE sketch with an INTEGER business key, a GENERATED ALWAYS ROWID, and a CLOB.
2. Why is GENERATED ALWAYS usually preferred for application inserts?
3. Explain why using a ROWID after COMMIT can be unsafe for direct-row access.
4. What does IMPLICITLY HIDDEN change about SELECT *?
5. Name one difference between ROWID and a surrogate INTEGER primary key.