Size of The Table

koganti · #1 (**permalink**) 07-16-07, 15:52

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

koganti · #2 (**permalink**) 07-16-07, 16:38

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)

stolze · #3 (**permalink**) 07-18-07, 06:15

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.

koganti · #4 (**permalink**) 07-18-07, 07:52

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

ARWinner · #5 (**permalink**) 07-18-07, 08:58

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

Internet Services

Web Development

Digital Marketing

Consumer Tech