• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2238
  • Last Modified:

It is posible to calculate the table size in MB or GB from a DB2 Schema?

Hi,

I'm wondering if there is a way to calculate or estimate the table sizes for tables under a specific schema?

For example I extracted the data from the follow sql into a excel file:

select * from SYSSTAT.TABLES where TABSCHEMA = "TEST"

Now I would like to calculate the sizes of each table or at least to have an estimate. I do not have access to DBA Tools.

I'm asking because I found some calculations on the web that make me think is posible.

"Calculating table space size with AVGROWLEN
 Using the maximum row size, you get the following results:
Usable page size = 4074 × 0.95 = 3870 bytes
Records per page = MIN(MAXROWS, FLOOR(3870 / 130)) = 29
Pages used = 2 + CEILING(100000 / 29) = 3451
Total pages = FLOOR(3451 × 21 / 20) = 3624
Estimated number of kilobytes = 3624 × 4 = 14496 KB"

Thanks,

P.S. I'm new with DB2
0
NikoVanO
Asked:
NikoVanO
  • 2
1 Solution
 
LowfatspreadCommented:
yes its possible as you describe to calculate a reasonable space requirement for your tables based upon the average row length...

the current physical space used to store the table depends on the amount and type of activity that the table is subjected to..

e.g. lots of "update" and delete activity can result in many pages being allocated to the table but onlu containing few rows...

the space requirements you calculate will be more the optimum, minimal requirements... with high activity on tables you need to schedule regular re-organisations...

you really need to look at tablespace size statistics in conjuction to individual table size calculations...

e.g. db2 environment is different in the exact requirements involved please tell us which system and version you are using.  
0
 
NikoVanOAuthor Commented:
Thanks

We are using DB2 8.2.3 on a Unix Solaris 10.

Can this also be done by something like in Oracle?
"select sum(BYTES/1024/1024) as TOTAL_GIG from user_segments where SEGMENT_NAME = 'TABLE_NAME';"

I mean I just need to get the info in any way posible ASAP.

Thanks in advance

0
 
momi_sabagCommented:
try

select tabname, fpages * pagesize as total_size
from syscat.tables t1
 join syscat.tablespace t2
  on t1.tbspace = t2.tbspace


this will give total_size in bytes
if you want kb divide by 1000
for mb divide by 1000000
etc...
0
 
NikoVanOAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now