Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Unanswered: Size of The Table

    How to find out the size of a table in V8 UDB.
    I have multiple tables in the tablespace. All I know is total number of rows in each table. I know in Control center you can estimate the average row size.

    I was wondering if there is any command line utility to find out the size of each table in a Tablesapce

    thx ahead

  2. #2
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Never Mind. I found the answer. Reorgchk will provide the size of table in bytes. If you want more accurate table size.

    Step1:

    db2 "select substr(TBNAME,1,30),sum(AVGCOLLEN)+10 from sysibm.syscolumns group by TBNAME having tbname
    in (select NAME from sysibm.systables where type='T')"

    The above query will sum the average column length for each table and that will be average row length.

    Step2:
    Total rows in the table*(average row length obtained in step1)

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Note that the average column length will only be accurate after RUNSTATS was performed. Also, you could combine steps 1 and 2 quite easily into a single query:
    Code:
    SELECT SUBSTR(c.tabname, 1, 30),
           (SUM(AVGCOLLEN) + 10) * ( SELECT card
                                     FROM   syscat.tables AS t
                                     WHERE  t.tabname = c.tabname )
    FROM   syscat.columns AS c
    GROUP BY tabname
    HAVING tabname IN ( SELECT tabname
                        FROM   syscat.tables
                        WHERE  type = 'T' )
    Furthermore, you calculate the number of bytes that the data in a table requires. But the rows are stored on pages and SYSCAT.TABLES.NPAGES gives you the number of pages for a table. Multiply that by the page size and you will get the real storage occupied by the table (again, RUNSTATS is necessary to get an accurate page count).

    p.s: If you are on DB2 LUW, you should not use the internal and undocumented tables in schema SYSIBM. Instead, refer to the official catalog views in the SYSCAT schema. Otherwise, it will be your fault if your application breaks due to catalog changes.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Thank you Stolze. This is exactly what iam looking for.

    I used to this query to find out the size of the database(summation of all table sizes) and compared against the tablespace usage.

    My Tables usage came around 300GB and my tablespace usage came around 500GB (excluding Temp spaces). I see a huge difference (200GB) of storage that was not accounted for.

    How to get the most accurate space occupied by a table


    Thx
    koganti

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are only looking at table sizes. There are other objects like indexes, packages, etc.

    I have left a query in the sticky section that will give more accurate sizes of tables and indexes. You can find it here: http://www.dbforums.com/showthread.p...30#post6284730

    Andy

Posting Permissions

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