Space: Calculating size of unused LOB space
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 = '<TBSP_NAME>'
select pagesize from syscat.tables where TBSPACEID = '<TBSP_ID>'
db2pd -db <SID> -tablespaces <TBSP_ID>
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
2. DESCRIBE
You can check the structure of a table with the describe command as follows:
db2 describe table <SCHEMA>.DYNPSOURCE
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PROGNAME SYSIBM VARCHAR 120 0 No
DYNPNUMBER SYSIBM VARCHAR 12 0 No
R3STATE SYSIBM VARCHAR 3 0 No
FIELDINFO SYSIBM BLOB 1073741824 0 Yes
LOGICINFO SYSIBM BLOB 1073741824 0 Yes
EXTENSIONS SYSIBM BLOB 1073741824 0 Yes
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 <SID> -z <TABSCHEMA> -t <TABNAME> -e -o <OUTPUTFILE>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('<SCHEMA>','<TABLE>')) as t
select DATA_OBJECT_L_SIZE, DATA_OBJECT_P_SIZE from TABLE(sysproc.admin_get_tab_info('<SCHEMA>','<TABLE>')) as t
select LONG_OBJECT_L_SIZE, LONG_OBJECT_P_SIZE from TABLE(sysproc.admin_get_tab_info('<SCHEMA>','<TABLE>')) as t
DATA_OBJECT_L_SIZE: Amount of disk space logically allocated for the table
DATA_OBJECT_P_SIZE: Data object physical size
LONG_OBJECT_L_SIZE: Amount of disk space logically allocated for long field data in a table
LONG_OBJECT_P_SIZE: Amount of disk space physically allocated for long field data in a table
LOB_OBJECT_L_SIZE: Amount of disk space logically allocated for LOB data in a table
LOB_OBJECT_P_SIZE: Amount of disk space physically allocated for LOB data in a table
The data is returned in KiloBytes ( KB )
Output would look as follows:
select LOB_OBJECT_L_SIZE, LOB_OBJECT_P_SIZE from TABLE(sysproc.admin_get_tab_info('<SCHEMA>','DYNPSOURCE')) as t
LOB_OBJECT_L_SIZE LOB_OBJECT_P_SIZE
-------------------- --------------------
2448848 2448864
1 record(s) selected.
select DATA_OBJECT_L_SIZE, DATA_OBJECT_P_SIZE from TABLE(sysproc.admin_get_tab_info('<SCHEMA>','DYNPSOURCE')) as t
DATA_OBJECT_L_SIZE DATA_OBJECT_P_SIZE
-------------------- --------------------
9152 9152
1 record(s) selected.
select LONG_OBJECT_L_SIZE, LONG_OBJECT_P_SIZE from TABLE(sysproc.admin_get_tab_info('<SCHEMA>','DYNPSOURCE')) as t
LONG_OBJECT_L_SIZE LONG_OBJECT_P_SIZE
-------------------- --------------------
0 0
Calculating the used LOB space
To calculate the space that is currently allocated to used LOB data you can use LENGTH SQL function on each of the LOB columns
select sum(cast(length(FIELDINFO) as bigint)) from <SCHEMA>.dynpsource
1
--------------------
1392148219
1 record(s) selected.
select sum(cast(length(LOGICINFO) as bigint)) from <SCHEMA>.dynpsource
1
--------------------
175746044
1 record(s) selected.
select sum(cast(length(EXTENSIONS) as bigint)) from <SCHEMA>.dynpsource
1
--------------------
77995448
1 record(s) selected.
The data here is returned in bytes. A sum of all of these values will provide a value for total LOB space currently in use.
How much data can we reclaim ?
Using all the data captured we can now calculate the unused LOB space that is allocated to the object.
Via ADMIN_GET_TAB_INFO we can see that object has the following disk space allocated for LOB data:
2448864 KB
Via the length SQL function we can determine that the LOB space currently in use is:
FIELDINFO: 1392148219 bytes
LOGICINFO: 175746044 bytes
EXTENSIONS: 77995448 bytes
We add all these values to get a total LOB space in use:
1645889711 bytes ( 1607314 KB )To obtain an estimate space saving that could be accomplished via DB6CONV/REORG we simply subtract the used LOB space from the allocated space. In this case:
2448864 KB - 1607314 KB = 841550 KBIn this case there is potentially 822 MB that could be reclaimed.
It is important to note that this is only an estimate. This does not take into account any space saving already in place via any of the following:
- Table Compression
- Inline LOBs
- COMPACT LOBs
You can determine if any of these features are enabled as follows:
1. Table compression
select compression from syscat.tables where tabname = 'DYNPSOURCE';
COMPRESSION
-----------
B
1 record(s) selected.
B = Both value and row compression are enabled
N = No compression is enabled; a row format that does not support compression is used
R = Row compression is enabled; a row format that supports compression might be used
V = Value compression is enabled; a row format that supports compression is used
2. Inline LOBs
You can check the percentage of LOB data that is inlined as follows:
select substr(name,1,30) as name, coltype, pctinlined from sysibm.syscolumns where tbname ='DYNPSOURCE';
NAME COLTYPE PCTINL
------------------------------ -------- ----------
DYNPNUMBER VARCHAR -1
EXTENSIONS BLOB 53
FIELDINFO BLOB 9
LOGICINFO BLOB 16
PROGNAME VARCHAR -1
R3STATE VARCHAR -1
VERSION SMALLINT -1
7 record(s) selected.
Percentage of inlined XML documents or LOB data. -1 if statistics have not been collected or if LOB INLING is not active.
3. COMPACT LOBS
You can use db2look to check if the LOB columns have the COMPACT option enabled
db2look -d <SID> -z <TABSCHEMA> -t <TABNAME> -e -o <OUTPUTFILE>102200 – DB6: db2look – DDL and statistics information
Reclaiming unused space
At this point we have established an estimate for how much space could be reclaimed. We have a couple of options to reclaim the unused LOB space:
1. DB6CONV
An online conversion with DB6CONV is the recommended procedure for reclaiming space. For further details you can check the following:
1513862 – DB6: Table conversion using DB6CONV version 6 or higher
2. DB2 REORG INDEXESTABLE Command
You can also REORG the data using the native DB2 REORG Command. However, you should be sure to use the LONGLOBDATA option to ensure that the LOB data is included as part of the REORG.
For more details you can check with the details location on the IBM InfoCenter:
Feedback on this post is greatly appreciated.