Monday, December 12, 2011

DB2: Find out the total size of a database

The below SQL query adds the space of all the table spaces and index spaces and that will give us the total size of the database.


SELECT 'KB='||CHAR(SUM(A.KB+B.KB)),                  
   'MB='||CHAR(SUM((A.KB+B.KB)/1024)),               
   'GB='||CHAR(SUM((A.KB+B.KB)/1024/1024)),          
   'CYLS='||CHAR(SUM((A.KB+B.KB)/720))               
FROM                                                 
  (SELECT SUM(SPACE) AS KB                           
       FROM SYSIBM.SYSTABLEPART                      
       WHERE DBNAME = 'TESTDB' AND TSNAME IN (     
   SELECT TSNAME FROM SYSIBM.SYSTABLES WHERE         
          CREATOR = 'TESTDB')) A,                  
   (SELECT SUM(SPACE) AS KB                          
       FROM SYSIBM.SYSINDEXPART                      
       WHERE IXCREATOR = 'TESTDB' AND IXNAME IN (  
     SELECT NAME FROM SYSIBM.SYSINDEXES WHERE        
       CREATOR = 'TESTDB')) B                      



The result of the above query will be as shown below


+---------------------------------------------------------------------+
| KB=311377632   | MB=304079      | GB=296         | CYLS=432468      |
+---------------------------------------------------------------------+

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.