IBM Support

DB2 reorganizes LOB data even though LONGLOBDATA clause is not specified in the 'REORG' command.

Question & Answer


Why does DB2 reorganize LOB data even though you are not including LONGLOBDATA clause in the 'REORG' command ?


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 :

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        =
        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.


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.

[{"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Business Unit":{"code":"BU001","label":"Analytics Private Cloud"},"Component":"Data Movement - Reorg\/Rebalance","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5","Edition":"Advanced Enterprise Server;Enterprise Server;Express;Express-C;Personal;Workgroup Server"}]

Document Information

Modified date:
16 June 2018