FREEPAGE and PCTFREE in DB2



IBM's flagship relational database management system

FREEPAGE and PCTFREE in DB2

Postby adurthisridhar » Wed Mar 07, 2012 7:06 pm

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
adurthisridhar
 
Posts: 28
Joined: Thu Mar 04, 2010 6:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: FREEPAGE and PCTFREE use in DB2

Postby dick scherrer » Fri Mar 09, 2012 8:08 am

Hello,

It may help someone help you if you post the info from the manual you referred to. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: FREEPAGE and PCTFREE use in DB2

Postby GuyC » Fri Mar 09, 2012 2:48 pm

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.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: FREEPAGE and PCTFREE use in DB2

Postby sinmani » Fri Mar 23, 2012 6:09 pm

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.
-----------------------------------------
As long as you think you are a student, you are doing well.
The day you consider yourself as the master of the game..........well
sinmani
 
Posts: 93
Joined: Thu Mar 22, 2012 10:02 am
Has thanked: 14 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post