Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2010
    Posts
    207

    Unanswered: Size of DB2 Schema

    Does anyone know how i can find out the size of my db2 schema?
    thanks in advance

    DB_N00b

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You can check the tablespace size if you store schemas in different tablespaces

  3. #3
    Join Date
    Jan 2010
    Posts
    207
    Thats actually not what i going to do.

    I want to see the size of an excisting db schema :-)

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You can use get_dbsize_info if you have one schema or you can query the catalog

  5. #5
    Join Date
    Jan 2010
    Posts
    207
    whats the command?

  6. #6
    Join Date
    Jan 2010
    Posts
    207
    I have a link on the IBM Info Center, but its not working properly.

    Example 1: Get the database size and capacity using a default refresh window of 30 minutes. The database size and capacity will be recalculated when the cached data is older than 30 minutes.

    CALL GET_DBSIZE_INFO(?, ?, ?, -1)

  7. #7
    Join Date
    Jan 2010
    Posts
    207
    I have no idea what this means ...

    can anyone help?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I posted something earlier that you might use:

    http://www.dbforums.com/db2/1615607-script-library.html

    Your runstats need to be current.

    Andy

  9. #9
    Join Date
    Jan 2010
    Posts
    207
    Sorry, but your scripts dont help me much.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why did they not help you?

    Andy

  11. #11
    Join Date
    Jan 2010
    Posts
    207
    I dont know which one to use. All i want is to get the size of one schema i have in my database

  12. #12
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by DB_N00b View Post
    I dont know which one to use. All i want is to get the size of one schema i have in my database
    i don't think 'schema size' makes sense - schema is not a physical object. what makes sense is the size of all objects under certain schema. then you probably need to write a query to find all objects (tables, indexes) and calculate and sum their sizes to get total size.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  13. #13
    Join Date
    Apr 2011
    Posts
    19

    simple

    Code:
    db2 "select sum(NPAGES)*4096 from syscat.tables where TABSCHEMA = 'MYSCHEMA'" and type = 'T'
    Will get you close...if you have 4k pages that is

    a DBA should make this table his or her best friend

    Code:
    db2 describe table syscat.tables

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DebianDog View Post
    Code:
    db2 "select sum(NPAGES)*4096 from syscat.tables
    Does not account for the size of indexes; are they part of a schema?

  15. #15
    Join Date
    Apr 2011
    Posts
    19
    Quote Originally Posted by n_i View Post
    Does not account for the size of indexes; are they part of a schema?
    Oh right DUH like it would be that easy. Yes you would have to multiply NLEAF times page size then add the two together. What I get for hurrying.

    You may be able to modify this query to suit your needs as it will find the index size for 1 table.

    Code:
    SELECT DEC(( FLOAT(( Sum(CAST(nleaf AS BIGINT)) * x.pagesize )) /
                        FLOAT(( 1024 * 1024 )) ), 8, 3) "Index_size_in_MBs"
    FROM   syscat.indexes i,
           (SELECT s.tabschema,
                   s.tabname,
                   st.pagesize
            FROM   syscat.tables s,
                   sysibm.systablespaces st
            WHERE  s.index_tbspace = st.tbspace) AS x
    WHERE  x.tabname = i.tabname
           AND i.tabname = 'YOUR_TABLE_NAME'
    GROUP  BY x.pagesize

Posting Permissions

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