db2 load performance

[rejeevkumar]

Hi,

Please explain TOTALFREESPACE=x, INDEXFREESPACE=x, PAGEFREESPACE=x options in load. How these options help in gaining performance.

Appreciate if you could provide some examples also.

Thanks,
Rejeev.

[momisabag]

Hi,

Please explain TOTALFREESPACE=x, INDEXFREESPACE=x, PAGEFREESPACE=x options in load. How these options help in gaining performance.

Appreciate if you could provide some examples also.

Thanks,
Rejeev.

Hi
well, the parameters you ask about are :
TOTALFREESPACE: the value must be in the range of 0 to 100, and is interpreted as a percentage of the total pages in the table that are to be appended to the end of the table as free space.
PAGEFREESPACE: the value must be in the range of 0 to 100, and is interpreted as a percentage of each data page that is to be left as free space.
INDEXFREESPACE: the value must be in the range of 0 to 99, and is interpreted as a percentage of each index page that is to be left as free space when loading indexes.

so what are these values good for ? that depends on the way your table is being accessed.
there are 3 types of operations that change the physical layout of data :
1) delete - remove a record so space becomes available
2) insert - add a new record
3) update - updates existing record, if the record is varying length (has any varchar column) than the record might become larger, and no longer fit where it used to be. when this happens , db2 finds a new place for the record, and the old place points to the new place, so if the new place is on a different page, we get 2 io operations each time we access this record (called overflow record)

so what these parameters do is telling db2 how much freespace to reserve in each page (last 2), and how many empty pages to reserve for the entire table.
free space and free pages are good for insert and update operations for :
1) allowing db2 to maintain clustering order
2) avoid overflow records as possible

so, if you have a read only table, you can set all of the above as 0,
if you have a table that you only insert data in asc order (such as a log table) you can set all of the above to 0 as well,
for all other cases you should consider how much work is done with this table and find the right balance for these parameters (remember - if you set them too high you are waisting space in the tablespace and in the bufferpool)

i hope it's clearer now

Momi