There is one doubt in making APPEND YES tables to FREEPAGE 0 and PCTFREE 0.

venkata rama rajesh mallina

There is one doubt in making APPEND YES tables to FREEPAGE 0 and PCTFREE 0.
There is no problem for the tables fixed length rows. Since even after UPDATE operation there is no chance of increasing record length.
 
If Table has variable length columns. Number of UPDATE's are more. Then I hope PCTFREE 0 is not recommended. 5% value is appropriate for PCTFREE. since updates can you that space. Otherwise updates performance will become bad . To be more clear, If record length is increasing after update, then for extra part of record can not get free space in same page, it needs to go end of partition/table space. So subsequently it will Update performance falls. 

Could you people put your comments on this. Let me know if my analysis is wrong. Or If I missing/misunderstanding  concept.
 


Venkata Rama Rajesh

Paul Ogborne

There is one doubt in making APPEND YES tables to FREEPAGE 0 and PCTFREE 0.
(in response to venkata rama rajesh mallina)
Hi Venkata,


Updating variable length rows and potentially making them longer can in itself, create issues where sufficient free space does not exist. This situation alone can cause performance issues.


The MC00 WITH APPEND (MEMBER CLUSTER APPEND YES FREEPAGE 0 PCTFREE 0) strategy is rather more suited for situations where there is contention due to high insert activity and so I would be unlikely to use this course for tables which also have to cope with significant update activity, and, even more so for your situation where variable length changes are involved.


Regards,
Paul



-----Original Message-----
From: venkata rama rajesh mallina <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Tue, 3 Jul 2018 18:48
Subject: [DB2-L] - There is one doubt in making APPEND YES tables to FREEPAGE 0 and PCTFREE 0.



There is no problem for the tables fixed length rows. Since even after UPDATE operation there is no chance of increasing record length.

If Table has variable length columns. Number of UPDATE's are more. Then I hope PCTFREE 0 is not recommended. 5% value is appropriate for PCTFREE. since updates can you that space. Otherwise updates performance will become bad . To be more clear, If record length is increasing after update, then for extra part of record can not get free space in same page, it needs to go end of partition/table space. So subsequently it will Update performance falls.

Could you people put your comments on this. Let me know if my analysis is wrong. Or If I missing/misunderstanding concept.



Venkata Rama Rajesh



Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription

This email has been sent to: [login to unmask email]
** ** ** IDUG Db2 Tech Conference in Sydney, Australia 2018 ** ** **
---> Sydney, Australia 11 - 13 September, 2018 <---
http://www.idug.org/au


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




Bruce Williamson

RE: There is one doubt in making APPEND YES tables to FREEPAGE 0 and PCTFREE 0.
(in response to venkata rama rajesh mallina)

PCTFREE 5 Defeats the purpose of MC00 & APPEND YES! Indeed it becomes MC05 which is largely ineffective for improving INSERT performance.

Depending on what version of DB2 you are at, if it's DB2 11 or 12, a better option to reduce page splitting due to UPDATEs and the increased REORG frequency due to MC00 is to specify PCTFREE_UPD -1, which then lets DB2 use RTS info to optimise how much free space to reserve for updates. It works pretty well in my experience.

Cheers
Bruce


In Reply to venkata rama rajesh mallina:

There is no problem for the tables fixed length rows. Since even after UPDATE operation there is no chance of increasing record length.
 
If Table has variable length columns. Number of UPDATE's are more. Then I hope PCTFREE 0 is not recommended. 5% value is appropriate for PCTFREE. since updates can you that space. Otherwise updates performance will become bad . To be more clear, If record length is increasing after update, then for extra part of record can not get free space in same page, it needs to go end of partition/table space. So subsequently it will Update performance falls. 

Could you people put your comments on this. Let me know if my analysis is wrong. Or If I missing/misunderstanding  concept.
 

 

Venkata Rama Rajesh



P.S. Want to make a difference to DB2 but don't know how? Join the RFE Community?

P.P.S. While you're at it why not join the "All DB2 for z/OS" group and vote on community proposals

Michael Hannan

RE: There is one doubt in making APPEND YES tables to FREEPAGE 0 and PCTFREE 0.
(in response to venkata rama rajesh mallina)

Venkat,

I am not really agreeing with anyone completely.

PCTFREE 5 for a tablespace is relevant for what happens on LOAD and REORG to a table. It does not prevent Insert from using the space. 

However MC with Append will prevent Inserts from taking distributed freespace (I assume), so I don't see why it would defeat the purpose of MC Append at all. Note that I left off the 00 from MC00 on purpose. I think FREEPAGE 0 is probably best. I am not aware of why MC05 (or is it  MC50? PCTFREE 5 FREEPAGE 0) would be so ineffective, provided used with APPEND. Append should be Append regardless. MC is relevant for Data Sharing.  We are allowed to specify Append without MC at all. Most references to APPEND YES in the Managing Performance manual do not mention the PCTFREE. John Campbell presentations usually do mention PCTFREE.

So I will quote John Campbell from one of his Insert presentations:

"In Version 9, we've introduced a new option on the DDL called APPEND, and APPEND
does actually what it should be doing. It is always going to append the new rows at the
end of the table space. It will never try to fill in the holes left by deletes or updates. So if
there is a lot of space caused by deletes, or updates causing rows to be relocated in
different parts, REORG has to be run in order to reclaim the space freed up."

So MC00 was a spepcial hint to DB2 not to go searching for space (when Append was not specified).

Frequent Reorg would be useful if still want some index to be well clustered. Page Splits are an Index thing so not relevant. 

When not using MC and Append but wanting to preserve some freespace for row Update increase in length, in the past I used MAXROWS to limit how many rows could be Inserted to a page, to help somewhat. In DB2 V11 NFM there is PCTREE n FOR UPDATE x as someone else already mentioned, to provide a certain amount of freespace that cannot be stolen by Insert.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jul 04, 2018 - 10:09 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jul 04, 2018 - 10:12 AM (Europe/Berlin)