Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Wed Jun 28, 2006 8:10 pm    Post subject: How to calcuate the space for a table in terms of pages
Reply with quote

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
Back to top
View user's profile Send private message

ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Thu Jun 29, 2006 2:30 pm    Post subject: Re: How to calcuate the space for a table in terms of pages
Reply with quote

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.
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Thu Jun 29, 2006 6:40 pm    Post subject:
Reply with quote

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,

Quote:
Record length: 100

no of rows :10000


Total bytes required : 100*10000=10,00,000 bytes

In each page u can able to store 40 rows (40*100=4000 bytes) .

For 40 rows =1 4k page
For 10000 rows =250 4k pages =1000kb

So you can choose your priqty 1000 and secqty 100
(usually priqty and secqty are expressed in KB )


Please correct me if i am wrong

Regards
Senthil
Back to top
View user's profile Send private message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Thu Jun 29, 2006 9:26 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Thu Jun 29, 2006 9:49 pm    Post subject:
Reply with quote

Hi jayaram ,


A small correction in your calculation
Quote:
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



3826/108 approxiamtely 35 rows . So that you need 10000 /35 =286 pages(4k) (apparoximately)

so priqty = 286 pages(4k) and secqty 4 pages(4k)


Please correct me I am wrong


Regards
Senthil
Back to top
View user's profile Send private message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Fri Jun 30, 2006 2:40 pm    Post subject:
Reply with quote

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

Regards,
jai
Back to top
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Thu Jul 06, 2006 4:15 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Thu Jul 06, 2006 4:29 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Thu Jul 06, 2006 4:38 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts VSAM Space Allocation mrgnndhmk JCL & VSAM 7 Sat Apr 22, 2017 12:42 am
This topic is locked: you cannot edit posts or make replies. Need help in estimating space of unlo... ashek15 IMS DB/DC 12 Fri Apr 07, 2017 5:11 am
No new posts Need inputs on Space requirement and... ashek15 IMS DB/DC 0 Sat Apr 01, 2017 8:26 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us