How to 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 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.
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.
Source: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.dbobj.doc/doc/t0055407.html
Forgot your password?
Hacker Rangers - Gamification for CyberSecurity