Results 1 to 2 of 2

Thread: DB2 SQL Query

  1. #1
    Join Date
    Feb 2010
    Posts
    9

    Unanswered: DB2 SQL Query

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think this is a wrong use of DISTINCT. Consider using GROUP BY instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •