Resizing DB2 LUW tablespaces for freeing up unused space and giving back to filesystem

Back ground

As DB2 LUW DBAs, most of the times we've come across situations where in we observe that there is a lot of space is allocated to the tablespace above high watermark and it is not being used by the tablespace. I feel we can say that this space as wasted space per tablespace.

For optimal utilization of storage, we will try to release this unused space from tablespaces to filesystems. However, as per the restrictions (not applicable for tablespaces created in V9.7 or above) in DB2 we can release only unused space above highwater mark of the tablespaces. 

Hence for releasing unused space (above highwatermark) of tablespaces in versions lesser than V9.7, consider using  below commands. The commands are different for partitioned and non partitioned databases.

Resizing tablespaces in Data partitioning feature (Non DPF or single partitioned) not enabled databases

Execute below command to analyze tablespaces and get the output redirected to a file "resize.sql"

db2 -x "SELECT TBSP_ID,SUBSTR(TBSP_NAME,1,30) TABLESPACE_NAME,TBSP_TOTAL_PAGES  TOTAL_PGS, TBSP_USABLE_PAGES,TBSP_USED_PAGES, TBSP_FREE_PAGES,TBSP_PAGE_TOP, TBSP_PAGE_SIZE PG_SZ,DBPARTITIONNUM PARTNUM,TBSP_EXTENT_SIZE EXTNT_SZ, TBSP_FREE_SIZE_KB,TBSP_NUM_CONTAINERS NUM_CONTS,TBSP_TOTAL_PAGES - TBSP_PAGE_TOP AS TOTALPG_MINUS_TOPPG, (TBSP_PAGE_TOP + (TBSP_EXTENT_SIZE * 10)) TOP_PLUS_EXT10, (TBSP_PAGE_TOP + (TBSP_EXTENT_SIZE * 10))/(TBSP_NUM_CONTAINERS) TOP_PLUS_EXT10_DIV_NOOFCONTS   FROM  SYSIBMADM.TBSP_UTILIZATION WHERE TBSP_AUTO_RESIZE_ENABLED=1 AND TBSP_TYPE <> 'SMS'  AND TBSP_NAME NOT LIKE 'SYS%' and (TBSP_TOTAL_PAGES - TBSP_PAGE_TOP) > 100 and TBSP_USING_AUTO_STORAGE=0 ORDER BY TBSP_ID with ur" | awk '{print "alter tablespace " $2 " resize (all " $15 ");"}' | tee -a resize.sql

Execute the generated resize commands in "resize.sql" file to free up the space from tablespaces and give it back to underlying filesystems.

db2 -tvf resize.sql -z resize.sql.out

Resizing tablespaces for Data partitioning feature (DPF) enabled databases

Execute below command to analyze tablespaces and get the output redirected to a file "resize.sql" 

db2 -x "with temptbsp_util as

(select MAX(TBSP_PAGE_TOP) TBSP_PAGE_TOP, MAX(TBSP_EXTENT_SIZE) EXTNT_SZ_MAX,MAX(TBSP_NUM_CONTAINERS) NUM_CONTS_MAX,  SUBSTR(TBSP_NAME,1,30) TBSP_NAME from  SYSIBMADM.TBSP_UTILIZATION where TBSP_AUTO_RESIZE_ENABLED=1 and TBSP_TYPE <> 'SMS'  and TBSP_NAME NOT LIKE 'SYS%' and (TBSP_TOTAL_PAGES - TBSP_PAGE_TOP) > 1000 and TBSP_USING_AUTO_STORAGE=0 group by TBSP_NAME)

select TBSP_PAGE_TOP, EXTNT_SZ_MAX,NUM_CONTS_MAX,TBSP_NAME,(TBSP_PAGE_TOP + (EXTNT_SZ_MAX * 2 * NUM_CONTS_MAX))/(NUM_CONTS_MAX) TOP_PLUS_EXT10_DIV_NOOFCONTS from TEMPTBSP_UTIL with ur" | awk '{print "alter tablespace " $4 " resize (all " $5 ");"}' | tee -a resize.sql

Execute the generated resize commands in "resize.sql" file to free up the space from tablespaces and give it back to underlying filesystems.

db2 -tvf resize.sql -z resize.sql.out

In my next post, I would be blogging on how free up unused space under high watermark of the tablespaces in your databases.

Stay tuned till then!