REORGCHK only identifies when a reorg is required for space reclamation on normal (non-LOB) data. Is there a way of determining how many pages are wasted due to LONG and LOB data that has been logically deleted and thus indicate that a reorg with LONGLOBDATA would be beneficial?

The admin table ADMINTABINFO accurately identifies the number of pages containing LOBDATA but gives no indication of how much of that data is "real" data, and how much has been logically deleted.

eg. Before REORG with LONGLOBDATA column LOB_OBJECT_P_SIZE/32 showed 35328 pages. After the reorg it showed 2176 pages. A substantial saving. But I had no way of knowing that I would save this much until actually running the reorg.


There doesn't seem to be a specific metric that you can use, but indirectly the high ratio of FPAGES to NPAGES and, if you have indexes, INDCARD to NDEL will tell you that there is a significant number of logically deleted rows. Unfortunately, this does not work for tables where rows are UPDATEd but not DELETEd, so I guess you will just want to run regular reorgs on tables where LOB columns are regularly modified.

  • 1
    And consider storing your LOBs in the long tablespace, rather than interleaved with table data in the main tablespace. – Ian Bjorhovde Nov 13 '14 at 18:07

Determine space that can be reclaimed with LONGLOBDATA Reorg command option.

This relatively simple analysis is performed by doing the following:

a) Determine the LONG/LOB columns in the table

db2 describe table <table_schema>.<table_name>

Look for Columns with LONG and LOB data types. This could also be accomplished by querying the SYSCAT.COLUMNS table.

b) Determine the current disk requirement for the LONG/LOB columns in the current active rows in the table

db2 select sum(decimal(length(<column_name>))) from <table_schema>.<table_name>

The result will be in bytes. If there is more than one column that is LONG/LOB, you will need to modify this select to SUM each column and add up the values to get the LOB disk requirements for the entire table:

db2 select sum(decimal(length(<column_name_1>)) + decimal(length(<column_name_2>))) from <table_schema>.<table_name>

c) Determine the current disk usage for the LONG/LOB data in the table

db2pd -d <dbname> -tcbstat|grep -p LobSize|grep -e LobSize -e <table_name>  

then look for the LobSize (in pages) and multiply time the PAGESIZE for the tablespace the table lives in.

The difference between c) and b) represents the space that can be reclaimed by REORGing the Table with the LONGLOBDATA option.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.