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 > growthing of tabalespace becomes high when changed fron 8k to 16k

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,106
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,386
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,106
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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 ?
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,106
It depends on the data, doesn't it. I cannot give a definitive answer because you mileage will vary.

Andy
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,106
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
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