How to calcuate the space for a table in terms of pages

rajandhla · Posted: Wed Jun 28, 2006 8:10 pm

Hi,

Could any one let me know the details how to calcuate the space fora table in terms of pages.

Suppose i want to create a table to hold 10000 rows with average rec length of 100 bytes and the page is 4 kb.

Then what would be the best number of pages for primary quantity and secondary quantity..

regards
jayaram

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

Query To find the size of the table in bytes:

SELECT TAB.NPAGES * TS PAGESIZE AS BYTES FROM
SYSCAT.TABLES TAB,
SYSCAT.TABLESPACES TS
WHERE TAB.TNAME='Ur Tablename'
AND TAB.TBSAPCE=TS.TBSPACE;

4*1024= 16384bytes

1 row 100bytes
x rows 16384bytes

x=16384/100=163.84 rows

163.84rows 4KB
10000rows XKb

X=(4*10000)/163.84=244pages approximately.

Please consider the free page and PCTFREE parameter.

Thanks,
Ravi.

senthilssg · New User Joined: 09 Dec 2005 Posts: 64 Location: USA

Hi rajandhla

you can able to store 4045 bytes in 4k page and reamiing 51 bytes are used for page header .

According to your requirement,

rajandhla · Posted: Thu Jun 29, 2006 9:26 pm

Thanks all for you replies...

I have done analysis from my side please let me know if any corrections required. Here iam explaning the number of rows can fit in page...

How many rows can fit in a page (considering 4096 bytes or 4kb)

Each page begins with a 20-byte header that records control information about the rest of the page. For example, the header contains the page set page number, pointers to free space in the page, and information pertaining to the validity and recoverability of the page.

At the very end of the page is a 1-byte trailer used as a consistency check token. DB2 checks the value in the trailer byte against a single bit in the page header to ensure that the data page is sound.

The next-to-last byte of each page contains a pointer to the next available ID map entry. The ID map is a series of contiguous 2-byte row pointers. One row pointer exists for every data row in the table. A maximum of 255 of these pointers can be defined per data page. The maximum number of rows per page can be specified for each table space using the MAXROWS clause. Each row pointer identifies the location of a data row in the data page.

So 20 bytes (header) + 1(trailer bit) + 1(pointer to next available ID map entry) = 22 bytes default bytes used for control information. And for row inserted in the page will have 6 bytes header and 2 bytes row pointer the maximum rows in a page are 255. Therefore the row size is = average row size+ 8 bytes.

If we consider above average row length of table is 100 + 8 = 108. The total page size available to insert the rows is 4096-22-248 (5% PCTFREE) =3826 bytes.

So the number of pages can accommodate in a page for TOSACCU is 3826/108= 300 . If we consider PCTFREE page has 0% we can accommodate 302 rows in page.

Around 35 pages are required..
i.e primary 35 pages secondary 4 pages

corrections are welcomed....

Regards,
jayaram

senthilssg · New User Joined: 09 Dec 2005 Posts: 64 Location: USA

Hi jayaram ,

A small correction in your calculation

rajandhla · Posted: Fri Jun 30, 2006 2:40 pm

Thanks for the correction........

Regards,
jai

Gurmeet · New User Joined: 22 Feb 2006 Posts: 46 Location: Pune

Hi everyone,

A query on the post above,

We have calculated the primary quantity but how have we reached a figure for the secondary quantity. Also, how DB2 uses the secondary quantity.

According to my understanding, when we define a tablespace, DB2 will allocate the primary quantity and the secondary quantity is used when the data grows on it beyond the primary quantity. Now, I am not clear with how this secondary quantity is used and what space should be available on a storage group for a particular priqty and secqty.

Please advise.

Regards,
~Gurmeet

rajandhla · Posted: Thu Jul 06, 2006 4:29 pm

Gurmeet,

Secondary Qty figure is reached by taking the 10% (default) of the primary qty.

regarding usage of secondary storage i have got the same understanding that you have got.

Regards,
jai

Gurmeet · New User Joined: 22 Feb 2006 Posts: 46 Location: Pune

Thanks Jai,

Regarding usage of secondary quantity what I am looking at is, if say for ex:

priqty = 1000 KB
secqty = 100KB

The space available on the storage group on which this tablespace will be created should be atleast 1000kb but if it grows beyond that what would be the complete space requirement for this TS? In other words we can say when will DB2 flash a error message for us to increase the primary & secondary quantity, will it be once 1100KB(1000 + 100) is utilized?

Regards,
~Gurmeet