DB2 - L

Expand all | Collapse all

database Size

  • 1.  database Size

    Posted 27 days ago
    Edited by Ron Thomas 27 days ago
    Hello -

    I have written the below query to find the size of DB, kindly let me know is this correct ?

    SELECT 'KB='||CHAR(SUM(A.KB+B.KB)),

    'MB='||CHAR(SUM(A.KB+B.KB)/1024)

    FROM

    (SELECT SUM(SPACE) AS KB

    FROM SYSIBM.SYSTABLEPART

    WHERE DBNAME ='CXITEM'

    AND TSNAME IN ( SELECT TSNAME FROM SYSIBM.SYSTABLES

    WHERE DBNAME ='CXITEM')) A,

    (SELECT SUM(SPACE) AS KB

    FROM SYSIBM.SYSINDEXPART

    WHERE IXNAME IN (SELECT NAME FROM SYSIBM.SYSINDEXES

    WHERE DBNAME = 'CXITEM'

    AND CREATOR ='CXITEM')) B

     
    Also, is there way we can get the database size growth year wise?

    Regards
    Ron T



    ------------------------------
    RonThomasXYZ Corp
    ------------------------------


  • 2.  RE: database Size

    Posted 24 days ago
    Well using SPACE is dangerous, you should be using SPACEF and watching out for negative values as well. Naturally this query is only as accurate as the last RUNSTATS or STOSPACE. My version looks like:

    SELECT 'KB='||CHAR(SUM(A.KB+B.KB))
    ,'MB='||CHAR(SUM(A.KB+B.KB)/1024)
    FROM
    (SELECT SUM(TP.SPACEF) AS KB
    FROM SYSIBM.SYSTABLEPART TP
    WHERE TP.DBNAME = 'IQAD0610'
    AND TP.SPACEF > 0 ) A,
    (SELECT SUM(IP.SPACEF) AS KB
    FROM SYSIBM.SYSINDEXPART IP
    ,SYSIBM.SYSINDEXES IX
    WHERE IX.CREATOR = IP.IXCREATOR
    AND IX.NAME = IP.IXNAME
    AND IX.DBNAME = 'IQAD0610'
    AND IP.SPACEF > 0 ) B
    ;



    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 3.  RE: database Size

    Posted 23 days ago
    Hi Ron

      This may not be the answer you're looking for. I used to do it with DCOLLECT instead of queries since it switches the focus to storage admin and gives full view of everything. It would also prove to be crucial for any kind of migrations. Since you're mentioning yearly analysis, maybe there's a chance your storage admins already have the info and you might just need to point out the SMS storage group.

      The initial reference for DCOLLECT is here..
    https://www.ibm.com/docs/en/zos/2.4.0?topic=commands-dcollect

    Have a nice day :)

    ------------------------------
    Javier Estrada Benavides
    Mexico / Czech Republic
    IBM Champion
    ------------------------------