LOBs with DB2 for z/OS: Stronger and Faster

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.