If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Physical Tablespace Sizing

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-14-06, 17:35
rubystep rubystep is offline
Registered User
 
Join Date: Sep 2003
Posts: 85
Question Physical Tablespace Sizing

In the UDB ESE (v8.2) Administration Guide: Planning, there's a section in Chapter 5: Physical database design which discusses space requirements for user data. On p. 75 of the pdf's hardcopy near the bottom of the pages it states:

The number of 4 KB pages for each user table in the database can be estimated by calculating:
ROUND DOWN(4028/(average row size + 10)) = records_per_page

and then inserting the result into:
(number_of_records/records_per_page) * 1.1 = number_of_pages .

My question is if I determine that the tables shold be placed in a 16K pagesize tablespace, what should I use instead of 4028?

I am trying to estimate the number of 16K pages required for a fact table and then evenly spread out the number of pages per tablespace container.

Much obliged,
Ruby
Reply With Quote
  #2 (permalink)  
Old 06-15-06, 03:23
Wim Wim is offline
Registered User
 
Join Date: Nov 2004
Posts: 1,279
In a 16K size page you'll be able to store 4* more records than in a 4K size page. So replace 4028 in the formula with 16384.
__________________
With kind regards . . . . . SQL Server 2000/2005/2008/2008 R2 Earned beers: 16
Wim
Beware of bugs in the above code; I have only proved it correct, not tried it. -- Donald Knuth
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Reply With Quote
  #3 (permalink)  
Old 06-15-06, 03:56
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,196
Quote:
Originally Posted by Wim
In a 16K size page you'll be able to store 4* more records than in a 4K size page. So replace 4028 in the formula with 16384.
16384 is a tad bit too high because there needs to be space for page overhead. That is why only 4028 is available on a page size of 4096. The page and row overhead calculations are only important if you have a large row size, with relatively few rows per page.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #4 (permalink)  
Old 06-15-06, 05:39
sathyaram_s sathyaram_s is offline
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,534
And moreover, remember, you cannot have more than 255 rows per page

Sathyaram
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #5 (permalink)  
Old 06-16-06, 09:56
rubystep rubystep is offline
Registered User
 
Join Date: Sep 2003
Posts: 85
This is my take:

Not that I can say for sure, but I have a hunch that the answer is something like 16384 (16k) - 68 = 16316.
Why 4028? My guess is that there's a 68 bytes header in each page, and my guess that 10 bytes are tacked onto a given row for the prefix to the row (6 bytes ?) and for the RID map per row (4 bytes).

When I said tacked onto a row, just the prefix is tacked onto the row and the RID map stuff (at least on the z/OS side) is at the end of the page.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On