Page 1 of 1

### Size of a table

Posted: Mon Oct 03, 2011 9:14 pm
Hello,

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

Thanks for your help .

### Re: Size of a table

Posted: 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.

### Re: Size of a table

Posted: Wed Oct 05, 2011 1:27 pm
Hello db2sysdba.

Did the unload data have the same size ?

Thanks !

### Re: Size of a table

Posted: 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. . .?

### Re: Size of a table

Posted: 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.

### Re: Size of a table

Posted: 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...

### Re: Size of a table

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

### Re: Size of a table

Posted: 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 = ?

### Re: Size of a table

Posted: 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