Question & Answer
Question
Why does DB2 reorganize LOB data even though you are not including LONGLOBDATA clause in the 'REORG' command ?
Cause
DB2 usually reorganizes LOB data only when LONGLOBDATA clause is specified in the REORG command. However if LOB column is being dropped from a table that contains another one or more LOB columns, DB2 will reorganize LOB data even though LONGLOBDATA clause is not specified in the REORG command.
To recreate and check/confirm the working you can use the following steps/commands :
db2sampl
db2 connect to sample
db2 "select count(*) from EMP_PHOTO where PICTURE is not null"
-- Add another LOB column.
db2 "alter table EMP_PHOTO add column PICTURE1 BLOB(102400)"
-- Drop original (one) LOB column
db2 "alter table EMP_PHOTO drop column PICTURE"
-- Execute REORG on table without specifying LONGLOBDATA clause in this command :
db2 reorg table EMP_PHOTO
db2 get snapshot for tables on sample
Last command shows:
Table Name = EMP_PHOTO
Table Type = User
Data Object Pages = 1
Index Object Pages = 4
LOB Object pages = 1
Rows Read = Not Collected
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Reorg Information:
Reorg Type =
Reclaiming
Table Reorg
Allow Read Access
Reorg Long Field LOB Data
This is expected behavior from DB2.
In following conditions, DB2 will force reorganization of LOB data in a table even though LONGLOBDATA clause is not mentioned in REORG command :
- missing user defaults for lobs
- converting to use inline lob data
- dropping lob column
- first REORG after converting the table to extended row size.
Answer
In above scenario, the space used by the dropped column cannot be reclaimed by the tablespace without reorganizing the LOB data as the drop does not delete the LOB data. When the table is reorganized, only data referenced by the current version of the table will be reorganized.
This behavior is not observed when the LOB column being dropped is the last LOB column in that table. When you drop the last LOB column, it becomes table without any LOB column in it. Hence DB2 will not reorganize LOB data as it is not there in current version of table and hence only reorganize normal data during REORG.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21661779