Page 1 of 1

Size of a table

PostPosted: Mon Oct 03, 2011 9:14 pm
by samb01
Hello,

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

Thanks for your help .

Re: Size of a table

PostPosted: Tue Oct 04, 2011 6:46 pm
by db2sysdba
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.

Re: Size of a table

PostPosted: Wed Oct 05, 2011 1:27 pm
by samb01
Hello db2sysdba.

Did the unload data have the same size ?

Thanks !

Re: Size of a table

PostPosted: Wed Oct 05, 2011 9:27 pm
by dick scherrer
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. . .?

Re: Size of a table

PostPosted: Thu Oct 06, 2011 1:08 pm
by samb01
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.

Re: Size of a table

PostPosted: Thu Oct 06, 2011 7:26 pm
by samb01
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...

Re: Size of a table

PostPosted: Fri Oct 07, 2011 1:16 am
by Akatsukami
Does not the SPACEF column relate to the storage group, and not to the table space itself?

Re: Size of a table

PostPosted: Fri Oct 07, 2011 3:25 pm
by GuyC
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 = ?

Re: Size of a table

PostPosted: Fri Oct 07, 2011 6:59 pm
by enrico-sorichetti
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