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

Technote (FAQ)


Question

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

Cause

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 :

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

Answer

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.

Related information

DB2 Reorg command syntax (V95)

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
DB2 Storage - Reorg

Software version:

7.2, 8.2, 9.1, 9.5, 9.7, 9.8, 10.1, 10.5

Operating system(s):

AIX, HP-UX, Linux, Linux iSeries, Linux pSeries, Linux zSeries, Solaris, Windows

Software edition:

Advanced Enterprise Server, Enterprise Server, Express, Express-C, Personal, Workgroup Server

Reference #:

1661779

Modified date:

2014-01-23

Translate my page

Machine Translation

Content navigation