Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2010
    Posts
    44

    growthing of tabalespace becomes high when changed fron 8k to 16k

    Hi All

    we had a 8k tablespace with one table in it (db2 v7.2) , for some reason we decided to move the table on 16k tablespace, then created 16k tablespace and moved the table in it and dropped the old one.

    since that time the rate of growth of tablespace has become 5 times more. do you thing change of page size is a factor of this and how ?

    thank you in advance for your help.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,147
    Yes, it could be. The reason depends on several factors. One could be that with an 8K page size you were near the limit on rows per page. When you went to 16K, you reached the max rows per page and the rest is wasted space.

    You need to mention what type of tablespace it is/was. Is the row count growing at a larger rate also?

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes, it could be as Andy described.

    here is a related document
    SQL and XML limits - IBM DB2 9.7 for Linux, UNIX, and Windows

    Please see "Maximum records per page for a regular table space" or "Maximum records per page for a large table space"
    (again, Andy wrote "You need to mention what type of tablespace it is/was. ...")
    in Table 7. Database Manager Page Size-specific Limits.

  4. #4
    Join Date
    Jan 2010
    Posts
    44
    sorry, you are right, the type of tablespace old and new is regular DMS tablespace.

    Andy/tonkuma,

    the avg row length is 410, so in old tablespace (8k)every page occupied ~ 20 rows and in new tablespace(16K) every page occupied ~40 rows, do you think do we have any waste space ? but I read somewhere at index portion may be we have waste space. does it make sense ? table has 100 mil recoreds with 10 indexes.
    really appreciate in advance for your help.

  5. #5
    Join Date
    Jan 2010
    Posts
    44
    Can PCTFREE be a factor for growing up the usage space after moving table from 8k tablespace to 16k tablespace?

  6. #6
    Join Date
    Jan 2003
    Posts
    4,147
    Quote Originally Posted by mars View Post
    Can PCTFREE be a factor for growing up the usage space after moving table from 8k tablespace to 16k tablespace?
    Yes, having each page reserve some space for expansion (PCTFREE), will cause less rows per page to exists. This means that you need more pages to store the data.

    Andy

  7. #7
    Join Date
    Jan 2010
    Posts
    44
    Hi Andy,

    "will cause less rows per page to exists"

    compare with 8k, do you think the differentiate row per page is the same. could you please explain more ?

  8. #8
    Join Date
    Jan 2003
    Posts
    4,147
    It depends on the data, doesn't it. I cannot give a definitive answer because you mileage will vary.

    Andy

  9. #9
    Join Date
    Jan 2010
    Posts
    44
    Andy,

    if we change the pctfree you are right, but if we do not change the pctfree from 8k to 16k moving, do you think still PCTFREE is a factor for growing up the size of database?

  10. #10
    Join Date
    Jan 2003
    Posts
    4,147
    Again you want a specific answer to a generic question. It depends. Just remember 10% of 8K is 0.8K and 10% of 16K is 1.6K, so you are reserving more space with the large page size.

    Andy

Posting Permissions

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