Chapter 7. Data administration with LOBs 219
Example 7-7 Select from SYSIBM.SYSTABLESPACE
NAME DBNAME BPOOL LOCKRULE IMPLICIT SPACEF LOCKMAX TYPE LOG
-------- -------- ----- -------- -------- --------- ------- ---- ---
NORMEN00 NORMEN00 BP1 P N 1.58E+03 0 Y
NORMLOB NORMEN00 BP1 L N 1.92E+05 0 O N
Here it is also interesting to see what happens if we let DB2 create all underlying objects
automatically by not specifying a table space for the base table. See Example 7-8 (DB2 9
only).
Example 7-8 DB2 9, automatic creation of objects
CREATE TABLE ##T.NORMEN01
(DOC_ID VARCHAR(30) FOR SBCS DATA NOT NULL
,PAGE_NUMBER SMALLINT NOT NULL
,IMPORTER CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT USER
,IMPORT_TIME TIMESTAMP NOT NULL
WITH DEFAULT
,FORMAT CHAR(8) FOR SBCS DATA NOT NULL
,IMAGE BLOB(2097152)
WITH DEFAULT NULL) ;
In this case, DB2 creates a base table space and LOB table space in a new database as
shown in SYSIBM.SYSTABLESPACE in Example 7-9.
Example 7-9 Select from SYSIBM.SYSTABLESPACE
NAME DBNAME BPOOL LOCKRULE IMPLICIT SPACEF LOCKMAX TYPE LOG
-------- -------- ----- -------- -------- --------- ------- ---- ---
NORMEN01 DSN00030 BP0 R Y 7.20E+02 0 G Y
L96UX60E DSN00030 BP0 A Y 1.89E+05 -1 O Y
Both base and LOB table spaces are created with default settings for buffer pool, locksize,
lockmax, and logging (and others not shown here).
7.1.2 LOBs defined in DB2 catalog
Even if you have no application using DB2 large objects in your environment, you already
have LOBs in the DB2 catalog in DBSNDB06. If you run a SELECT on
SYSIBM.SYSAUXRELS using WHERE clause AUXTBOWNER = ‘SYSIBM’, you see the
auxiliary tables listed in Example 7-10.
Example 7-10 DB2 catalog query
SELECT TBOWNER,TBNAME,COLNAME,AUXTBOWNER,AUXTBNAME,RELCREATED
FROM SYSIBM.SYSAUXRELS WHERE TBOWNER = 'SYSIBM' ;
AUX REL
TBOWNER TBNAME COLNAME TBOWNER AUXTBNAME CREATED
------- ------------------- ------------ ------- ------------------ -------
SYSIBM SYSJARCONTENTS CLASS_SOURCE SYSIBM SYSJARCLASS_SOURCE
SYSIBM SYSJAROBJECTS JAR_DATA SYSIBM SYSJARDATA
SYSIBM SYSROUTINES TEXT SYSIBM SYSROUTINESTEXT
SYSIBM XSROBJECTS GRAMMAR SYSIBM XSROBJECTGRAMMAR M
SYSIBM XSROBJECTS PROPERTIES SYSIBM XSROBJECTPROPERTY M

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.