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.

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

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.

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.

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

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 ?

It depends on the data, doesn't it. I cannot give a definitive answer because you mileage will vary.

Andy

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?

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