LOBs with DB2 for z/OS: Stronger and Faster

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