APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO

venkata rama rajesh mallina

APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO

APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO

Is the usual formula for massive and concurrent Insert performance. But the new doubt is FOR UPDATE OF

FREEPAGE 0 PCTFREE 0 FOR UPDATE 20

FREEPAGE 0 PCTFREE 0 FOR UPDATE -1

 

Making UPDATE 0 also required for INSERT performance ?

Even UPDATE -1 mean 5% as default.

For fixed length rows UPDATE -1 is required ?

Venkata Rama Rajesh

Martin Ålund

RE: APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO
(in response to venkata rama rajesh mallina)

Hi!

As you are optimizing for fast INSERT you should use PCTFREE 0 FOR UPDATE 0.

But if you will be updating the rows later and that may make the rows longer you should probably use PCTFREE 0 FOR UPDATE -1. The inserts will not entierly fill up the pages, but there will be room for the updates so Db2 will not have to move the updated rows to the end of the partition and leave indirect references from the old page to the new one..

Regards Martin

venkata rama rajesh mallina

APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO
(in response to Martin Ålund)
Thaks Martin go it completely Now got additional doubt. Maling FREEPAGE 0 and PCTFREE 0 for indexes also improves INSERT performance ? Thanks & Regards
Venkata Rama Rajesh IT Specialist - Mainframe DB2 DBA  IBM Certified Database Administrator IBM Certified Solution DeveloperIBM Certified Database AssociateEmail : [login to unmask email]: +91-7338817414 IBM Services ----- Original message -----
From: "Martin Ålund" <[login to unmask email]>
To: [login to unmask email]
Cc:
Subject: [DB2-L] - RE: APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO
Date: Fri, Jul 13, 2018 2:13 PM

Hi!
As you are optimizing for fast INSERT you should use PCTFREE 0 FOR UPDATE 0.
But if you will be updating the rows later and that may make the rows longer you should probably use PCTFREE 0 FOR UPDATE -1. The inserts will not entierly fill up the pages, but there will be room for the updates so Db2 will not have to move the updated rows to the end of the partition and leave indirect references from the old page to the new one..
Regards Martin 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]
Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
http://www.ESAIGroup.com/idug

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

Michael Hannan

RE: APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO
(in response to venkata rama rajesh mallina)



In Reply to venkata rama rajesh mallina:

 Making FREEPAGE 0 and PCTFREE 0 for indexes also improves INSERT performance ?

These are instructions for Reorg or Load. They don't really affect Insert performance, when using Append. However in the special case MC00, it is used as a hint to append where Append was not specified.

It is best you try to understand what things are actually there for rather than learn rules.

The advice to go PCTFREE 0 and FREEPAGE 0 is based on the assumption if you insert at the end you won't be using any freespace spread throughout the table, put there by reorg or Load, so pages might as well be full unless Update will come along and increase row length.
 
So if you use Append, will PCTFREE 5 be harmful to your INSERT? No, not at all. It may affect cost of scans though slightly, as potentially less rows per page after a Reorg.

If you don't use Append or MC00, then an INSERT can go looking to use any freespace left there by reorg, when cannot insert into the most desired page. In the modern day, this idea to going looking to use distributed freespace is probably not a good one. Would be nice to be able to disable that even when not using Append instead using Cluster Index. IBM provides the search for freespace mainly because they don't want to see failures when Tablespace runs out of space but is not full. Would be very handy to switch that off without appending as first choice, e.g. use target cluster page, then anything in the same segment, and then append.

In that scenario, a recent bad performing Insert would not have been performing so badly at all. Performance over space saving for me!

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jul 13, 2018 - 11:37 AM (Europe/Berlin)

venkata rama rajesh mallina

APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO
(in response to Michael Hannan)
Thank you Thanks & Regards
Venkata Rama Rajesh IT Specialist - Mainframe DB2 DBA  IBM Certified Database Administrator IBM Certified Solution DeveloperIBM Certified Database AssociateEmail : [login to unmask email]: +91-7338817414 IBM Services ----- Original message -----
From: Michael Hannan <[login to unmask email]>
To: [login to unmask email]
Cc:
Subject: [DB2-L] - RE: APPEND YES MEMBER CLUSTER YES FREEPAGE 0 PCTFREE 0 TRACKMODE NO
Date: Fri, Jul 13, 2018 2:58 PM



In Reply to venkata rama rajesh mallina: Making FREEPAGE 0 and PCTFREE 0 for indexes also improves INSERT performance ?
These are instructions for Reorg or Load. They don't really affect Insert performance. However in the special case MC00, it is used as a hint to append where Append was not specified.
It is best you try to understand what things are actually there for rather than learn rules.
The advice to go PCTFREE 0 and FREEPAGE 0 is based on the assumption if you insert at the end you won't be using any freespace spread throughout the table, put there by reorg or Load, so pages might as well be full unless Update will come along and increase row length.

So if you use Append, will PCTFREE 5 be harmful to your INSERT? No, not at all. It may affect cost of scans though slightly, as potentially less rows per page after a Reorg.
Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd 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]
Faster data refresh is here! The long waits and babysitting of unload/load jobs is over. Contact
ESAi to learn about BCV5 & XDM. Be a hero to users with fast on-demand test/QA data provisioning.See
http://www.ESAIGroup.com/idug

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