Page 1 of 1

FREEPAGE and PCTFREE in DB2

PostPosted: Wed Mar 07, 2012 7:06 pm
by adurthisridhar
Hi,

I read in IBM documents the use of FREEPAGE and PCTFREE parameter usgae during tablespace creation. I would like to know the use of leaving a blank space between the data? Can any one explain me why we are leaving a blank space using FREEPAGE option.

Also, can any one explain me the use of PCTFREE with clarity. I could not understand the usage of PCTFREE.

Thanks,
Sridhar A

Re: FREEPAGE and PCTFREE use in DB2

PostPosted: Fri Mar 09, 2012 8:08 am
by dick scherrer
Hello,

It may help someone help you if you post the info from the manual you referred to. . .

Re: FREEPAGE and PCTFREE use in DB2

PostPosted: Fri Mar 09, 2012 2:48 pm
by GuyC
why:
DB2 tries to store new rows close to the position according to the clustering index.
If there is no space he will search for space closeby.
if a row changes in length (varchar updates) you'll need some space to accommodate for this. if there is not enough space on the page, a part of the row wil be a on another page; closeby if available, at the end of the table if no space available closeby.

what:
during reorg
freepage = to leave a empty page every n pages
pctfree = leave in each page n% empty.

Thus specifying these can keep your table organized longer.

Re: FREEPAGE and PCTFREE use in DB2

PostPosted: Fri Mar 23, 2012 6:09 pm
by sinmani
FREEPAGE

We need to leave free pages so as to accomodate new rows being inserted close to the already present rows.
This helps is faster fetching and better performance by DB2.
As time goes by and new rows are inserted these spaces gets filled up.
Thats why we need to REORG from time to time so as to align the data in order again and again allocate free pages for future insertion.

PCTFREE
PCTFREE free space allows for insertion of new records and for expansion of
variable length fields.
PCTFREE parameters will be 0 for read only tablespace and indexspace.