Calculating table space usage

You can determine how much of your table space is currently in use with the MON_GET_TABLESPACE table function. The information this function returns can help you determine whether you should attempt to reclaim free storage.

About this task

This task will provide you with information that you can use to determine the extent to which you have unused space below the high water mark for your table space. Based on this, you can make a determination as to whether reclaiming free storage would be beneficial.

Restrictions

Although you can determine various usage attributes about all your table spaces, only table spaces created with Db2® Version 9.7 or later have the reclaimable storage capability. If you want to be able to reclaim storage in table spaces created with earlier versions of the Db2 database product, you either must unload then reload the data into a table space created with Db2 Version 9.7, or move the data with an online move. The reclaimable storage capability can only be used with the IBM® Db2® pureScale® Feature starting in v11.1 M4 FP4.

Procedure

To determine how much free space exists below the high water mark:

Formulate a SELECT statement that incorporates the MON_GET_TABLESPACE table function to report on the state of your table spaces. For example, the following statement will display the total pages, free pages, used pages, for all table spaces, across all database partitions:
```
SELECT varchar(tbsp_name, 30) as tbsp_name, 
  reclaimable_space_enabled,
  tbsp_free_pages,
  tbsp_page_top,
  tbsp_usable_pages
FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t 
ORDER BY tbsp_free_pages ASC
```

Run the statement.

You will see output that resembles this:

TBSP_NAME                      RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_USABLE_PAGES
------------------------------ ------------------------- -------------------- -------------------- --------------------
TEMPSPACE1                                             0                    0                    0                    1
SYSTOOLSTMPSPACE                                       0                    0                    0                    1
TBSP1                                                  1                    0                 1632                 1632
SMSDEMO                                                0                    0                    0                    1
SYSCATSPACE                                            1                 2012                10272                12284
USERSPACE1                                             1                 2496                 1696                 4064
IBMDB2SAMPLEREL                                        1                 3328                  736                 4064
TS1                                                    1                 3584                  480                 4064
TS2                                                    1                 3968                   96                 4064
TBSP2                                                  1                 3968                   96                 4064
TBSAUTO                                                1                 3968                   96                 4064
SYSTOOLSPACE                                           1                 3976                  116                 4092

  12 record(s) selected.

Use the following formula to determine the number of free pages below the high water mark:
freeSpaceBelowHWM = tbsp_free_pages - (tbsp_usable_pages - tbsp_page_top)

Results

Using the information from the report in step 2, the free space below the high water mark for USERSPACE1 would be 2496 - (4064 - 1696) = 128 pages. This represents just slightly over 5% of the total free pages available in the table space.

What to do next

In this case, it might not be worth trying to reclaim this space. However, if you did want to reclaim those 128 pages, you could run an ALTER TABLESPACE USERSPACE1 REDUCE MAX statement. If you were to do so, and then run the MON_GET_TABLESPACE table function again, you would see the following:

TBSP_NAME                      RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_USABLE_PAGES
------------------------------ ------------------------- -------------------- -------------------- --------------------
TEMPSPACE1                                             0                    0                    0                    1
USERSPACE1                                             1                    0                 1568                 1568
SYSTOOLSTMPSPACE                                       0                    0                    0                    1
TBSP1                                                  1                    0                 1632                 1632
SMSDEMO                                                0                    0                    0                    1
SYSCATSPACE                                            1                 2012                10272                12284
IBMDB2SAMPLEREL                                        1                 3328                  736                 4064
TS1                                                    1                 3584                  480                 4064
TS2                                                    1                 3968                   96                 4064
TBSP2                                                  1                 3968                   96                 4064
TBSAUTO                                                1                 3968                   96                 4064
SYSTOOLSPACE                                           1                 3976                  116                 4092

  12 record(s) selected.