232 LOBs with DB2 for z/OS: Stronger and Faster
Example 7-37 Use of REPAIR to delete orphan LOBs
REPAIR OBJECT
LOCATE TABLESPACE NORMEN03.NORMLOB
ROWID X'05A455C204CFD1062104015C5630' VERSION X'0001' DELETE
REPAIR OBJECT
LOCATE TABLESPACE NORMEN03.NORMLOB
ROWID X'2D1165C204CFD30A2104015C5630' VERSION X'0001' DELETE
REPAIR OBJECT
LOCATE TABLESPACE NORMEN03.NORMLOB
ROWID X'3E8FB7D204CFDA0F2104015C5630' VERSION X'0001' DELETE
REPAIR OBJECT
LOCATE TABLESPACE NORMEN03.NORMLOB
ROWID X'50C8D45204CFD00D2104015C5630' VERSION X'0001' DELETE
REPAIR OBJECT
LOCATE TABLESPACE NORMEN03.NORMLOB
ROWID X'6B8F05C204CFDE392104015C5630' VERSION X'0001' DELETE
REPAIR OBJECT
LOCATE TABLESPACE NORMEN03.NORMLOB
ROWID X'9B4177D204CFD50B2104015C5630' VERSION X'0001' DELETE
The REPAIR deletes the six orphan LOBs, and the base table space and the LOB table space
are in sync again.
7.2.2 LOGGED base table space with NOT LOGGED LOB table space
In the second scenario, we create the base table space as LOGGED and the LOB table
space as NOT LOGGED. We then redo the following steps as in the first scenario:
1. LOAD the data.
2. Create a common recoverable point of consistency using the COPY utility with LISTDEF
and SHRLEVEL REFERENCE as shown in Example 7-11 on page 221. In
SYSIBM.SYSCOPY, we now get a common START_RBA = X'00008927CDE6’.
3. Delete six LOBs using SPUFI as shown in Example 7-13 on page 222 to create some
update activity. As a result, the LOB table space is put in the informational copy (ICOPY)
status, because the changes to the LOB table spaces have not been logged.
4. Stop and start all spaces of database NORMEN03 and delete the VSAM clusters using
ISPF 3.4 as shown in Example 7-14 on page 222.
5. Recover the VSAM clusters back to the current point with the same statements as shown
in Example 7-15 on page 222. Because of the logged system pages, the recovery of the
LOB table space succeeds without problems. DB2 is able to delete the six LOBS again
from the full image copy of the LOB table space, even when the LOB table space is
created as NOT LOGGED.
6. We then reinsert the six rows with SPUFI as shown in Example 7-38.
Example 7-38 SPUFI Insert
INSERT INTO ##T.NORMEN03 (DOC_ID,PAGE_NUMBER,FORMAT,IMAGE)
SELECT DOC_ID,PAGE_NUMBER,FORMAT,IMAGE FROM ##T.NORMEN00
WHERE DOC_ID LIKE '%E%' ;
---------+---------+---------+---------+---------+---------+-
DSNE615I NUMBER OF ROWS AFFECTED IS 6
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-
Chapter 7. Data administration with LOBs 233
---------+---------+---------+---------+---------+---------+-
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
7. Now it is interesting to see what happens if we stop, start, and delete all VSAM clusters
again and try to recover them back to the current point with the same statements as in
Example 7-15 on page 222. Because the inserts of the LOBs were not logged, the LOB
table space is put in AUXW and the six LOBs are invalidated in the LOB table space by the
RECOVER utility as shown in Example 7-39.
Example 7-39 LOB table space in AUXW
DSNT360I -DB9B ***********************************
DSNT361I -DB9B * DISPLAY DATABASE SUMMARY
* GLOBAL
DSNT360I -DB9B ***********************************
DSNT362I -DB9B DATABASE = NORMEN03 STATUS = RW
DBD LENGTH = 4028
DSNT397I -DB9B
NAME TYPE PART STATUS PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ----- ----------------- -------- -------- -------- -----
NORMEN03 TS RW
NORMLOB LS RW,ICOPY,AUXW
IRNO1BNY IX RW
IRNORMEN IX RW
******* DISPLAY OF DATABASE NORMEN03 ENDED **********************
DSN9022I -DB9B DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
***
The invalid LOBS can be shown by running a CHECK LOB utility on the LOB table space. The
job output is shown in Example 7-40.
Example 7-40 CHECK LOB output
.......
DSNU743I 222 17:03:36.39 DSNUKLBD - LOB IS INVALID.
ROWID X'00E0C2EC03CFD4062104015C5630' VERSION X'0001'
DSNU743I 222 17:03:36.40 DSNUKLBD - LOB IS INVALID.
ROWID X'554542EC03CFD1372104015C5630' VERSION X'0001'
DSNU743I 222 17:03:36.41 DSNUKLBD - LOB IS INVALID.
ROWID X'94EF82EC03CFD5092104015C5630' VERSION X'0001'
DSNU743I 222 17:03:36.41 DSNUKLBD - LOB IS INVALID.
ROWID X'D3FD82EC03CFD4272104015C5630' VERSION X'0001'
DSNU743I 222 17:03:36.41 DSNUKLBD - LOB IS INVALID.
ROWID X'D578C2EC03CFD4222104015C5630' VERSION X'0001'
DSNU743I 222 17:03:36.41 DSNUKLBD - LOB IS INVALID.
ROWID X'F20AC2EC03CFD41F2104015C5630' VERSION X'0001'
DSNU796I 222 17:03:36.42 DSNUKLBD - REPRTLOB PHASE COMPLETE, ELAPSED TIME=00:00:00
DSNU568I -DB9B 222 17:03:36.62 DSNUGSRX - TABLESPACE NORMEN03.NORMLOB IS IN INFORMATIONAL COPY PENDING STATE
DSNU816I -DB9B 222 17:03:36.62 DSNUGSRX - TABLESPACE NORMEN03.NORMLOB IS IN AUX WARNING STATE
DSNU010I 222 17:03:36.63 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4
These LOBs can now be populated using SQL update. CHECK DATA on the base table space
does not report or invalidate these LOBs in the base table space. We now use SPUFI to
update these invalid LOBs as shown in Example 7-41 on page 234.

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.