You are looking to reclaim unused space within the database. For traditional data you can clearly see how much data is possible to be reclaimed. This can be done in a number of ways:
1. DBACockpit
You can check the statistical data available in the DBACockpit -> Space -> Single Table Analysis:
2. NPAGES/FPAGES in syscat.tables:
select npages, fpages from syscat.tables where tabname = 'BALDAT' NPAGES FPAGES -------------------- -------------------- 13257 13261 1 record(s) selected. NPAGES: Indicates the total number of pages on which rows of the table exist
FPAGES: Indicates the total number of pages allocated for the object
For tables with LOB data the above query will still only show the number of pages physically stored in the table. This is due to the LOBs data being stored at filesystem level, only their references are stored in tables row.
Note that you can use this data to help calculate the total size of the table with: FPAGES * PAGESIZE. PAGESIZE can be found with the following:
select pagesize from syscat.tables where tbspace = '3. ADMIN_GET_TAB_INFO
select DATA_OBJECT_L_SIZE, DATA_OBJECT_P_SIZE from TABLE(sysproc.admin_get_tab_info('SAPDB6','BALDAT')) as t DATA_OBJECT_L_SIZE DATA_OBJECT_P_SIZE -------------------- -------------------- 212416 212416 1 record(s) selected. The data is returned in KiloBytes ( KB )
With this data we can see clear values for space allocated versus used. This can be then utilized to establish whether the object could benefit from reorg.
The same process cannot be used alone for tables containing LOB data.
What are LOBs ?
LOB is an acronym of Large OBjects and refer to the BLOB, CLOB, or DBCLOB data types. On a basic level, LOBs are stored on disk level with a reference to this location contained at row level.
It is for this reason that the techniques above cannot be used for space calculations. In tables containing LOB data only these references/pointers make up the values we see above for NPAGES, FPAGES and DATA.
Identifying whether a table has BLOB data types
This can be check in a number of ways:
1. DBACOCKPIT
-> Space -> Single Table Analysis -> Table Columns
db2 describe table
3. DB2LOOK
You can also use the db2look tool to gather the DDL for the table. This is explained in further details with the following note:
102200 – DB6: db2look – DDL and statistics information
db2look -d Using ADMIN_GET_TAB_INFO to get more precise table data
We can use the function ADMIN_GET_TAB_INFO to capture further data relating to the BLOB space.
select LOB_OBJECT_L_SIZE, LOB_OBJECT_P_SIZE from TABLE(sysproc.admin_get_tab_info('