Hi,

Previously I used this query to find the free space left on OS for each of my tablespaces -
db2 "select FSFreeSizeKB from (select distinct SUM(FS_TOTAL_SIZE_KB)-SUM(FS_USED_SIZE_KB) FSFreeSizeKB from SYSIBMADM.CONTAINER_UTILIZATION where TBSP_NAME = 'USERSPACE1' AND ACCESSIBLE = 1 group by FS_ID, CONTAINER_ID, DBPARTITIONNUM) TotalFSFreeSizeKB"

The above query gives OS free space for each tablespace separately as we use the where clause.
I have modified the above query to get the results for all tablespaces by excluding the where clause as shown -
select TBSP_NAME, sum(distinct FS_TOTAL_SIZE_KB) - sum(distinct FS_USED_SIZE_KB) result from SYSIBMADM.CONTAINER_UTILIZATION where ACCESSIBLE = 1 group by TBSP_NAME

Now my question is if I have 2 tablespace containers on C and D drive whose total size is 10GB each then in that case it would consider only one of the drive total space because of the distinct clause. How can I avoid this?

I am using the distinct clause as there could be more than 2 containers on C drive in that case it should consider total space on C drive once.

Thanks,
Ronak.

I think this is a wrong use of DISTINCT. Consider using GROUP BY instead.