Recovering from a problem with invalid LOBs

If a LOB table space is defined with LOG NO and you need to recover that table space, you can recover the LOB data to the point at which you made your last image copy of the table space.

About this task

Unless your LOBs are fairly small, specifying LOG NO for LOB objects is recommended for the best performance. However, to maximize recoverability, specifying LOG YES is recommended. The performance cost of logging exceeds the benefits that you can receive from logging such large amounts of data. If no changes are made to LOB data, the logging cost is not an issue. However, you should make image copies of the LOB table space to prepare for failures. The frequency with which you make image copies is based on how often you update LOB data.

Procedure

To recover LOB data from a LOB table space that is defined with LOG NO:

Run the RECOVER utility as you do for other table spaces:
```
RECOVER TABLESPACE dbname.lobts
```
If changes were made after the image copy, Db2 puts the table space in auxiliary warning status, which indicates that some of your LOBs are invalid. Applications that try to retrieve the values of those LOBs receive SQLCODE -904. Applications can still access other LOBs in the LOB table space.
Get a report of the invalid LOBs by running CHECK LOB on the LOB table space:
```
CHECK LOB TABLESPACE dbname.lobts
```
Db2 generates the following messages:
```
LOB WITH ROWID = 'xxxxxxx' VERSION = n IS INVALID
```
Fix the invalid LOBs, by updating the LOBs or setting them to the null value.
For example, suppose that you determine from the CHECK LOB utility that the row of the EMP_PHOTO_RESUME table with ROWID X'C1BDC4652940D40A81C201AA0A28' has an invalid value for column RESUME. If host variable hvlob contains the correct value for RESUME, you can use this statement to correct the value:
```
UPDATE DSN8C10. EMP_PHOTO_RESUME
  SET RESUME = :hvlob
  WHERE EMP_ROWID = ROWID(X'C1BDC4652940D40A81C201AA0A28');  
```