Raghu On Tech

Pro tips on managing data at scale

How To Measure Tablespace Utilization In DB2 ?

by | Mar 31, 2019 | 2 comments

Problem:

You would like to find out quickly how much space your tablespaces are utilizing and how much space is being wasted below the high watermark with in a tablespace, how would you look for it ? While there are multiple ways to pull out that information but ever since MON_GET functions were introduced I rarely have to look past them.

Enough with the talk, lets take a look at the SQL…

Solution:

Simply running below SQL will provide you with high level tablespace utilization and space wastage report for top 20 of the tablespaces with in your database.

with tbsp_size as (select distinct substr(TBSP_NAME,1,30) as TBSP_NAME, TBSP_PAGE_SIZE, TBSP_TOTAL_PAGES,
case when TBSP_PAGE_SIZE=32768 then
(TBSP_USED_PAGES*32768)/(1024*1024*1024) when TBSP_PAGE_SIZE=16384 then
(TBSP_USED_PAGES*16384)/(1024*1024*1024) when TBSP_PAGE_SIZE=8192 then
(TBSP_USED_PAGES*8192)/(1024*1024*1024) when TBSP_PAGE_SIZE=4096 then
(TBSP_USED_PAGES*4096)/(1024*1024*1024) end as TBSP_USED_GB,
case when TBSP_PAGE_SIZE=32768 then
(TBSP_TOTAL_PAGES*32768)/(1024*1024*1024) when TBSP_PAGE_SIZE=16384 then
(TBSP_TOTAL_PAGES*16384)/(1024*1024*1024) when TBSP_PAGE_SIZE=8192 then
(TBSP_TOTAL_PAGES*8192)/(1024*1024*1024) when TBSP_PAGE_SIZE=4096 then
(TBSP_TOTAL_PAGES*4096)/(1024*1024*1024) end as TBSP_TOTAL_GB
FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t order by TBSP_TOTAL_GB desc fetch first 20 rows only with ur)
select TBSP_NAME, TBSP_PAGE_SIZE, TBSP_TOTAL_PAGES, TBSP_USED_GB, TBSP_TOTAL_GB, 
(TBSP_TOTAL_GB-TBSP_USED_GB) as WASTED_SPACE_GB from tbsp_size

 

Running the above SQL will provide you with an output that looks something like below. Note that here I am only asking for the top 20 tablespaces based on the space utilization. You could ask for more or less depending on what you need. Also don’t think that we are crazy by naming all the tablespaces as TABLESPACE* 🙂 I am just trying to be safe by not disclosing any of my employers business related information.

As you can see from the above output, there are a few tablespaces where space could be reclaimed from them. Depending on the type of tablespace you may be able to reclaim all of the wasted storage. If you would like to know how to reclaim the unused storage from a tablespace, please take a look at the below blog post.

How To Reclaim Wasted Storage From A DMS/AS Tablespace

Hope you enjoyed this short blog post from me, I will try to blog more frequently as I am getting more requests from the readers.

2 Comments

  1. Krishna

    Raghu Very Excellent SQl’s and detailed information, however the SQL has missing first 14 lines, can you fix or email me the full sql ,

    Reply
  2. swapna

    Hi Raghu,

    This is a good sql to retrieve tablespace usage but can we get which application is consuming more tablespace at the run time?

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shares
Share This

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close