Size of a table



IBM's flagship relational database management system

Size of a table

Postby samb01 » Mon Oct 03, 2011 9:14 pm

Hello,

how can i get the size (in cylinders) of a table ?

Thanks for your help .
samb01
 
Posts: 295
Joined: Mon Nov 16, 2009 7:24 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Size of a table

 

Re: Size of a table

Postby db2sysdba » Tue Oct 04, 2011 6:46 pm

Go to QMF and fire the below Query

SELECT SPACEF FROM SYSIBM.SYSTABLES
WHERE NAME='your table name'
WITH UR;

(Provided you must have run RUNSTATS on the table which you are checking the size.)

The value of SPACEF is in Kilobytes.

Convert that value to Cylinders by using the below formula.

1 Cylinder = (15 * 48) KB.
db2sysdba
 
Posts: 18
Joined: Wed Sep 28, 2011 4:14 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Size of a table

Postby samb01 » Wed Oct 05, 2011 1:27 pm

Hello db2sysdba.

Did the unload data have the same size ?

Thanks !
samb01
 
Posts: 295
Joined: Mon Nov 16, 2009 7:24 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Size of a table

Postby dick scherrer » Wed Oct 05, 2011 9:27 pm

Hello,

Did the unload data have the same size ?
Huh? Same size as what?

Is there some reason that you cannot perform the calculation and run the unload and see for yourself?

I suspect they will not be the exact same size. . .

What is it you want to learn really. . .?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6269
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Size of a table

Postby samb01 » Thu Oct 06, 2011 1:08 pm

Hello dick scherrer.

It's a very big table(Over than 500 Millions lines...) . I can not test the unload because i'm afraid of the real size and i don't want to take many disks... only for a test.
samb01
 
Posts: 295
Joined: Mon Nov 16, 2009 7:24 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Size of a table

Postby samb01 » Thu Oct 06, 2011 7:26 pm

db2sysdba wrote:SELECT SPACEF FROM SYSIBM.SYSTABLES
WHERE NAME='your table name'
WITH UR;


Sorry, there's no column SPACEF into the table SYSIBM.SYSTABLES.. but in SYSIBM.SYSTABLESPACE

I can't explique the diffence betwenne the two numbers...

Into de the table, i found :

And when i take the numbers of lines : 570 000 000 and the lenght of a line : 755, i find : 430350000 Kb

and in SPACEF, i find : +0.13350672E+09 Kb is equivalent : 133506720 Kb


I can't anderstand the difference...
samb01
 
Posts: 295
Joined: Mon Nov 16, 2009 7:24 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Size of a table

Postby Akatsukami » Fri Oct 07, 2011 1:16 am

Does not the SPACEF column relate to the storage group, and not to the table space itself?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Size of a table

Postby GuyC » Fri Oct 07, 2011 3:25 pm

do an unload from table mytable sample 001.0000 limit 100
that way you have an idea of the external reclength of the output file

model 3390 disk:
--56664 bytes in a track,
--15 tracks /cyl
--849960 byte /cyl

select bigint(? * cardf)           as bytes
     , bigint(? * cardf ) / 56664  as tracks
     , bigint(? * cardf ) / 849960 as cyls
from sysibm.systables tb
where creator = ? and NAME = ?
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Size of a table

Postby enrico-sorichetti » Fri Oct 07, 2011 6:59 pm

model 3390 disk:
--56664 bytes in a track,
--15 tracks /cyl
--849960 byte /cyl


that' s the theoretical capacity of the <thing>
the best fit would be only for 55996 bytes x track ( 2 x 27998 )

but then for real <data>utilization,
the idiosyncrasies of the DBMS and vsam should also be taken into account
as per ( vsam )
CONTROLINTERVALSIZE(size)
...
You can define a size from 512, to 8K in increments of 512 or from 8K to 32K in increments of 2K (where K is 1024 in decimal notation). If you use a size that is not a multiple of 512 or 2048, VSAM chooses the next higher multiple.
...


for exact computations see here
http://ibmmainframes.com/about55037.html

running with record length 1 will reproduce the table in the 3390 manual


a nice place to hang around for DB2 insight ad user tools is
http://www.idug.org
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2797
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 148 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post