32 LOBs with DB2 for z/OS: Stronger and Faster
3.1.3 Manual creation of objects
DB2 still allows you to create all of the necessary objects for a LOB environment on your own.
Creating the base table
The first step is creating the base table in a table space already existing in the same database
where the LOB table spaces are stored. There are no special suggestions for a base table
space: it is just a normal table space. But we recommend that you have only one base table in
a base table space. This simplifies your recovery procedures by dealing independently with
each LOB table space.
The base table for our example is created with the DDL statement reported in Example 3-3.
Example 3-3 DDL for a base table for manual object creation
CREATE TABLE BOOK_BASE_TABLE
( BOOK_NO CHAR(10) NOT NULL WITH DEFAULT
, DESCRIPTION CHAR(32) NOT NULL WITH DEFAULT
, BOOK_TEXT CLOB(500M) NOT NULL
, BOOK_COVER BLOB(1M) NOT NULL )
IN LOBDB.BASETS;
Note that a ROWID column is omitted and created as a hidden column by DB2.
A few more details about the base table
DB2 currently requires that a ROWID column is included in tables which have one or more
LOB columns. The additional column containing the data type ROWID simply contains unique
values related to the auxiliary tables in the LOB table spaces. DB2 generates a hidden
ROWID column if a ROWID column is not explicitly specified, as shown in Example 3-3. This
column is not included in the result set of a SELECT * from a base table, but by selecting the
column explicitly by name, you can retrieve its content.
If DB2 detects a LOB column in your CREATE TABLE statement, the definition of a ROWID
column is added implicitly as shown in Example 3-4 on page 32. This is called ROWID
transparency and was introduced in DB2 V8.
Example 3-4 Catalog description for a hidden ROWID for LOBs
---------+---------+---------+---------+---------+---------+---------+---------+
NAME COLTYPE HIDDEN LENGTH UPDATES DEFAULT
---------+---------+---------+---------+---------+---------+---------+---------+
DOCUMENT_NR CHAR N 10 Y Y
DESCRIPTION CHAR N 32 Y Y
DOCUMENT BLOB N 4 Y N
DB2_GENERATED_ROWID_FOR_LOBS ROWID P 17 N A
DB2 creates the column with a name of DB2_GENERATED_ROWID_FOR_LOBSnn. DB2
appends nn only if the column name already exists in the table, replacing nn with 00 and
incrementing by 1 until the name is unique within the row. The implicitly added column is
appended to the end of the row after all of the other explicitly defined columns.
For the DB2-generated column containing the ROWID, the value of ’P’ for the attribute
’HIDDEN’ indicates that the ROWID column is not visible in SQL statements except for explicit
reference by column name. Updates for that specific column are not allowed, and
it is also created with the GENERATED ALWAYS clause.

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

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.