How to determine when REORG with LONGLOBDATA is required (DB2)

Question

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.

Answer 1

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.

Answer 2

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.