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');