LOBs with DB2 for z/OS: Stronger and Faster

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.