Space: Calculating size of unused LOB space

[david_godson]

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 KB

In 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:

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001966....

Feedback on this post is greatly appreciated.