Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003

    Question Unanswered: 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,

  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    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/2012

    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

  3. #3
    Join Date
    May 2003
    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

  4. #4
    Join Date
    Aug 2001
    And moreover, remember, you cannot have more than 255 rows per page

    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2003
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts