Skip to content

storage_usage.md

Latest commit

 

History

History
91 lines (59 loc) · 3.53 KB

storage_usage.md

File metadata and controls

91 lines (59 loc) · 3.53 KB
Raw
copyright
years
2025
lastupdated 2025-11-26
keywords storage space
subcollection db2-saas

Diagnosing and resolving high storage usage

{: #stor_intro_db2_saas}

{:external: target="_blank" .external} {:shortdesc: .shortdesc} {:codeblock: .codeblock} {:screen: .screen} {:tip: .tip} {:important: .important} {:note: .note} {:deprecated: .deprecated} {:pre: .pre}

This document describes ways to diagnose and, if necessary, reduce the storage usage on your {{site.data.keyword.Db2_on_Cloud_long}} system. {: shortdesc}

Check storage usage

{: #stor_chk}

Run the following SQL query as an admin user to check the current storage usage on every Db2 partition in your {{site.data.keyword.Db2_on_Cloud_long_notm}} instance:

db2 "SELECT DBPARTITIONNUM , ROUND((SUM(FS_USED_SIZE) / SUM(FS_TOTAL_SIZE)::DECFLOAT) * 100,2)  AS UTILIZATION_PERCENT, ROUND(( SUM(FS_TOTAL_SIZE) - SUM(FS_USED_SIZE)::DECFLOAT) / 1073741824,2) AS FREE_GB FROM TABLE(ADMIN_GET_STORAGE_PATHS(NULL,-2)) AS T WHERE STORAGE_GROUP_NAME NOT LIKE '%TEMP%' GROUP BY DBPARTITIONNUM ,CASE WHEN DBPARTITIONNUM = 0 THEN 'NP' ELSE 'P' END"

{: codeblock}

Or see Using the Db2 Database Assistant to ask the database assistant to show storage information.

There are two possible causes for high storage usage:

  • The storage attached to your system is reaching maximum capacity.
  • There is empty space on the disks that must be reclaimed.

Diagnosing and resolving

{: #stor_diag}

If the output from the query indicates that storage usage on your instance is high, the following steps help to diagnose and resolve this scenario:

  1. Check if the instance has reclaimable space by running the following SQL query as an admin user:

    db2 "SELECT SUBSTR(TBSP_NAME, 1,30) AS TBSP_NAME, SUM(TBSP_USED_PAGES * TBSP_PAGE_SIZE)/ 1073741824 AS TBSP_USED_SIZE_GB, SUM((TBSP_PENDING_FREE_PAGES+ tbsp_free_pages) * TBSP_PAGE_SIZE)/ 1073741824 AS FREE_OR_PENDING_GB FROM TABLE(MON_GET_TABLESPACE('',-2)) where TBSP_NAME not like '%SYS%' and TBSP_NAME not like '%TEMP%' and RECLAIMABLE_SPACE_ENABLED = 1 and (TBSP_PENDING_FREE_PAGES+ tbsp_free_pages) >= 1 GROUP BY TBSP_NAME ORDER BY 3 DESC"

    {: codeblock}

  2. If one or more table spaces have reclaimable space, complete the following steps to reclaim that space:

    Do not reclaim space during an online backup. The reclaim operation can also impact the performance of ongoing workloads, so it is best to run the following steps during off-peak hours. {: important}

    a. Verify that an online backup is not running:

    db2 "SELECT MEMBER,SUBSTR(UTILITY_DETAIL, 1, 150) AS CMD FROM TABLE(MON_GET_UTILITY(-2)) AS T"

    {: codeblock}

    b. For every table space in the list generated by the previous step, reclaim the unused space by running the following command:

    db2 alter tablespace <tablespace_name> reduce max

    {: codeblock}

  3. If there is no disk space that can be reclaimed, you must either purge unwanted data or scale up the storage on your {{site.data.keyword.Db2_on_Cloud_long_notm}} instance to ensure database stability.

Additional information

{: #stor_add_info}