Why does DB2 reorganize LOB data even though I am not including LONGLOBDATA clause in the 'REORG' command ?
DB2 usually reorganises 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 reorganise 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 :
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 =
Allow Read Access
Reorg Long Field LOB Data
This is expected behaviour from DB2.
In following conditions, DB2 will force reorganisation 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.
In above scenario, the space used by the dropped column cannot be reclaimed by the tablespace without reorganising the LOB data as the drop does not delete the LOB data. When the table is reorganised, only data referenced by the current version of the table will be reorganised.
This behaviour 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 reorganise LOB data as it is not there in current version of table and hence only reorganise normal data during REORG.