R: [DB2-L] [z/OS]High Getpages & Buffer Updates for Insert on one member in Data S

Mauro Moschelli

R: [DB2-L] [z/OS]High Getpages & Buffer Updates for Insert on one member in Data S
Hi Adrian,
we recently had a similar issue on a LOAD SHRLEVEL CHANGE utility job, which under the covers does a massive insert. For less than 1 million inserts we saw up to 70 million getpage and the same number of Global locks.
In our case locksize was page, segmented tablespace with compress yes and GBP Dependent. The elapsed time went from a few minutes to some hours. If it is not GBP Dep the time is again few minutes. We solved by ALTERing the tablespace to CLOSE YES, which helps it not being GBP Dep at the time of the execution and, when possible, STOP/START of the tablespace before execution. However we are already working with IBM folks and opening a PMR.

HTH

Ciao

Mauro Moschelli
Intesa Sanpaolo S.p.A.

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS

-----Messaggio originale-----
Da: DB2 Data Base Discussion List [mailto:[login to unmask email] Per conto di Adrian Collett
Inviato: mercoledì 21 gennaio 2009 10.49
A: [login to unmask email]
Oggetto: Re: [DB2-L] [z/OS]High Getpages & Buffer Updates for Insert on one member in Data Sharing

Thanks for the help Robert,
I think you're probably right, it has to be something to do with RLL and the Partition being GBP - hoiwever, what is really puzzling me is the reason for so many getpages/updates ... for a simple insert we are seeing an average of 1000 getpages/updates...when the process runs "normally" the average is 2 !!

Even if all the rows are inserted on the same page with all the extra page p-locking I can't see why we should be getting 1000 getpages per insert.... any ideas ??

Thanks once again.

Ciao,
Adrian




Robert Catterall ha scritto:
> Row-level locking can indeed be used in a data sharing environment -
> there is no technical restriction on the use of LOCKSIZE ROW in a DB2
> data sharing system.
>
> Here's how row-level locking affects overhead in a data sharing
> environment: as I mentioned in responding to Adrian's question, when a
> tablespace is defined with LOCKSIZE ROW, and a data page is to be
> updated (via UPDATE, INSERT, or DELETE), and the page set (or
> partition) is group buffer pool dependent (which generally means that
> there is currently inter-DB2 read/write interest in the page set or
> partition), the updating DB2 will request a physical lock on the page
> in addition to requesting the logical (i.e., "regular") lock on the
> row that will be inserted/updated/deleted. If page-level locking is
> used for the tablespace, that page P-lock won't be requested when the
> page is to be updated - only the page-level logical lock will be
> requested. There is extra overhead associated with requesting the
> page P-lock (necessary for data coherency protection when the page
> can't be logically locked, as it can't when logical locking is at the
> row level), but what can really make overhead jump is when another
> program running on a different DB2 member wants to change a different
> row on the same page. It also requests the page P-lock on that page,
> and through page P-lock negotiation the page gets sent via the group
> buffer pool from the one DB2 member (the one that first acquired the
> P-lock on the page) to the other member. If rows on the same page are
> being changed with great frequency by programs running on two or more
> members of the data sharing group, the page in question will be
> shipped back and forth between the systems with great frequency.
>
> Keep in mind that page-level P-locking will occur in a data sharing
> group regardless of whether or not you have any tablespaces defined
> with LOCKSIZE ROW - you'll get page P-locks on space map pages and
> index pages when they are updated. So, row-level locking doesn't
> introduce page-level P-locking, but it does add to the volume of page
> P-lock requests.
>
> In some cases, row-level locking carries with it a relatively low
> overhead cost, even in a data sharing environment, and it can be a
> very useful means of alleviating timeout and deadlock problems, just
> as in a non-data sharing environment. In other cases (e.g., when
> certain pages of a tablespace can become very "hot"), row-level
> locking can significantly increase data sharing overhead.
>
> Bottom line: you CAN user row-level locking in a data sharing
> environment, and sometimes it's a good idea to do so. That said, you
> ONLY want to use row-level locking in a data sharing environment if
> you NEED to use it, because you have to ensure that the benefit will
> outweigh the cost.
>
> Robert
>


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Prima di stampare, pensa all'ambiente ** Think about the environment before printing


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html