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
NikoVanOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NikoVanOAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.