38 LOBs with DB2 for z/OS: Stronger and Faster
DB2 V7 uses the index on the auxiliary table to locate a LOB value for a particular row
containing a LOB within the base table.
An index defined on an auxiliary table is automatically defined as a unique index, fed by a
ROWID from the base table. The buffer pool that you might want to assign to this index does
not need any special considerations, because the index only contains two relatively small
columns.
Displaying LOB objects
For our base table containing one BLOB column and one CLOB column, the display database
looks like Example 3-11.
Example 3-11 Displaying a database for LOBs
DSNT360I -DB9B ***********************************
DSNT361I -DB9B * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -DB9B ***********************************
DSNT362I -DB9B DATABASE = LOBDB STATUS = RW
DBD LENGTH = 4028
DSNT397I -DB9B
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ---- ------------------ -------- -------- -------- -----
BASETS TS RW
BLOBATS1 LS RW
CLOBATS1 LS RW
BLOBAIX1 IX RW
CLOBAIX1 IX RW
******* DISPLAY OF DATABASE LOBDB ENDED **********************
3.1.4 Adding a LOB column to an existing table
Another possible situation is the need for adding a particular LOB column to an already
existing table; this table becomes the base table for your LOB columns.
Before a LOB column can be added, we have to be sure that a column of data type ROWID is
already in the table. If it is not, we have to add it using the statement reported in
Example 3-12.
Example 3-12 Adding a ROWID column
ALTER TABLE NEW_LOB_TABLE
ADD ROW_ID ROWID NOT NULL GENERATED ALWAYS;
A table can only have one ROWID column, and you cannot add a ROWID column to a created
temporary table.
Note: You cannot define an index on a LOB column.
Important: If you add a LOB column prior to a ROWID column, DB2 generates a hidden
ROWID column.

Get LOBs with DB2 for z/OS: Stronger and Faster now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.