row level locking

Nick Nur

row level locking
Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row level locking
in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one side and
memory and CPU on the other side. I am aware how argue for each side but I
am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes of memory.
So I can manage the analysis of memory. However I like to know how many #
of instructions a row lock needs and how can I compute the additional CPU for
it and if somebody gives me a number could you be kind enough tell me in
which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list to dare
asking such questions pertaining to our work. Thank you in advance.
Nick Nur

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: row level locking
(in response to Nick Nur)
Hi Nik,

How's this for "quick and dirty"

Let's assume that the cost of a "lock" is the same, regardless of
whether it's a page or row lock

If all access is DIRECTLY to a row, then the lock "costs" will be
similar

BUT

As soon as you get into a scan, and start touching every row on a page,
the lock costs will escalate astronomically

What's the average number of rows per page? 50?? Then row locking will
be 50 times as costly as page locking for a sequential scan

Conventional wisdom has it that UNLESS YOU HAVE SPECIFIC OTHER
REQUIREMENTS, page locking is the way to go. Row and Table/Tablespace
locks are solutions for specific problems and should not really be
considered "usual"

Hope this helps (a bit)


Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nick Nur
Sent: 21 November 2007 18:57
To: [login to unmask email]
Subject: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row level
locking in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one side and
memory and CPU on the other side. I am aware how argue for each side but
I am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes of
memory.
So I can manage the analysis of memory. However I like to know how many
# of instructions a row lock needs and how can I compute the additional
CPU for it and if somebody gives me a number could you be kind enough
tell me in which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list to dare
asking such questions pertaining to our work. Thank you in advance.
Nick Nur

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Nick Nur

Re: row level locking
(in response to William Favero)
Thanks very much Phil.
I am aware of what you have explained however I have been asked if I know how much a lock costs in mips in v8.1. if I am able to find that out I can incorporate it into a good work on the subject. I wonder if anybody knows Chuck Hoover telephone number or his email if he still around. I know he can give me the answer.
Thanks Phil again.


"Grainger, Phil" <[login to unmask email]> wrote:
Hi Nik,

How's this for "quick and dirty"

Let's assume that the cost of a "lock" is the same, regardless of
whether it's a page or row lock

If all access is DIRECTLY to a row, then the lock "costs" will be
similar

BUT

As soon as you get into a scan, and start touching every row on a page,
the lock costs will escalate astronomically

What's the average number of rows per page? 50?? Then row locking will
be 50 times as costly as page locking for a sequential scan

Conventional wisdom has it that UNLESS YOU HAVE SPECIFIC OTHER
REQUIREMENTS, page locking is the way to go. Row and Table/Tablespace
locks are solutions for specific problems and should not really be
considered "usual"

Hope this helps (a bit)


Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nick Nur
Sent: 21 November 2007 18:57
To: [login to unmask email]
Subject: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row level
locking in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one side and
memory and CPU on the other side. I am aware how argue for each side but
I am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes of
memory.
So I can manage the analysis of memory. However I like to know how many
# of instructions a row lock needs and how can I compute the additional
CPU for it and if somebody gives me a number could you be kind enough
tell me in which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list to dare
asking such questions pertaining to our work. Thank you in advance.
Nick Nur

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------
Looking for a X-Mas gift? Everybody needs a Flickr Pro Account!

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Nick Nur

Re: row level locking
(in response to Phil Grainger)
Thanks very much Phil.
I am aware of what you have explained however I have been asked if I know how much a lock costs in mips in v8.1. if I am able to find that out I can incorporate it into a good work on the subject. I wonder if anybody knows Chuck Hoover telephone number or his email if he still around. I know he can give me the answer.
Thanks Phil again.


"Grainger, Phil" <[login to unmask email]> wrote:
Hi Nik,

How's this for "quick and dirty"

Let's assume that the cost of a "lock" is the same, regardless of
whether it's a page or row lock

If all access is DIRECTLY to a row, then the lock "costs" will be
similar

BUT

As soon as you get into a scan, and start touching every row on a page,
the lock costs will escalate astronomically

What's the average number of rows per page? 50?? Then row locking will
be 50 times as costly as page locking for a sequential scan

Conventional wisdom has it that UNLESS YOU HAVE SPECIFIC OTHER
REQUIREMENTS, page locking is the way to go. Row and Table/Tablespace
locks are solutions for specific problems and should not really be
considered "usual"

Hope this helps (a bit)


Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nick Nur
Sent: 21 November 2007 18:57
To: [login to unmask email]
Subject: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row level
locking in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one side and
memory and CPU on the other side. I am aware how argue for each side but
I am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes of
memory.
So I can manage the analysis of memory. However I like to know how many
# of instructions a row lock needs and how can I compute the additional
CPU for it and if somebody gives me a number could you be kind enough
tell me in which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list to dare
asking such questions pertaining to our work. Thank you in advance.
Nick Nur

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------
Looking for a X-Mas gift? Everybody needs a Flickr Pro Account!

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Douwe van Sluis

Re: row level locking
(in response to Nick Nur)
Hi Nick,

I found some examples in "Designing for Performance and Availability:
Challenges, Solutions and V8 Susan Lawson - YL&A Performance Seminar
Series September 2005". There are some CPU cost examples included. These
number are from the old Redbook SG24-4725 on page 144.
Furthermore are you in Datasharing also consider the Page P-lock
negotiation overhead for RLL.
I know, it might not be the numbers you are looking for, but w'll keep
on looking.

Vriendelijke groet,
Douwe van Sluis

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Nick Nur
Verzonden: woensdag 21 november 2007 19:57
Aan: [login to unmask email]
Onderwerp: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row level
locking
in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one side and

memory and CPU on the other side. I am aware how argue for each side but
I
am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes of
memory.
So I can manage the analysis of memory. However I like to know how many
#
of instructions a row lock needs and how can I compute the additional
CPU for
it and if somebody gives me a number could you be kind enough tell me in

which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list to dare
asking such questions pertaining to our work. Thank you in advance.
Nick Nur

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Douwe van Sluis

Re: row level locking
(in response to Douwe van Sluis)
Hi Nick,

Here again some info I found. This time in the latest "Susan Lawson and
Dan Luksetich Explain Application Tuning for DB2" as provided by CA,
page 145.

Tips for Avoiding Locks
Lock avoidance has been around since Version 3, but are you getting it?
Lock avoidance can be a key component to high performance because to
take an actual lock is about 400 CPU instructions and 540 bytes of
memory, to avoid a lock a latch is taken by the buffer manager and the
cost is significantly less.

Vriendelijke groet,
Douwe van Sluis

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Nick Nur
Verzonden: woensdag 21 november 2007 19:57
Aan: [login to unmask email]
Onderwerp: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row level
locking
in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one side and

memory and CPU on the other side. I am aware how argue for each side but
I
am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes of
memory.
So I can manage the analysis of memory. However I like to know how many
#
of instructions a row lock needs and how can I compute the additional
CPU for
it and if somebody gives me a number could you be kind enough tell me in

which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list to dare
asking such questions pertaining to our work. Thank you in advance.
Nick Nur

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Nick Nur

Re: row level locking
(in response to Douwe van Sluis)
Hartelijk bedunkt !

That is exactly the figure I want.
Is it possible to have a copy of that document that you were quoting from?
thanks again
Nick Nur

Douwe van Sluis <[login to unmask email]> wrote:
Hi Nick,

Here again some info I found. This time in the latest "Susan Lawson and
Dan Luksetich Explain Application Tuning for DB2" as provided by CA,
page 145.

Tips for Avoiding Locks
Lock avoidance has been around since Version 3, but are you getting it?
Lock avoidance can be a key component to high performance because to
take an actual lock is about 400 CPU instructions and 540 bytes of
memory, to avoid a lock a latch is taken by the buffer manager and the
cost is significantly less.

Vriendelijke groet,
Douwe van Sluis

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Nick Nur
Verzonden: woensdag 21 november 2007 19:57
Aan: [login to unmask email]
Onderwerp: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row level
locking
in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one side and

memory and CPU on the other side. I am aware how argue for each side but
I
am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes of
memory.
So I can manage the analysis of memory. However I like to know how many
#
of instructions a row lock needs and how can I compute the additional
CPU for
it and if somebody gives me a number could you be kind enough tell me in

which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list to dare
asking such questions pertaining to our work. Thank you in advance.
Nick Nur

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------
Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Nick Nur

Re: row level locking
(in response to Nick Nur)
Yes we are datasharing and I am aware of the overhead of negotiating the P-Lock. Thank a lot.
Nick Nur

Douwe van Sluis <[login to unmask email]> wrote:
Hi Nick,

I found some examples in "Designing for Performance and Availability:
Challenges, Solutions and V8 Susan Lawson - YL&A Performance Seminar
Series September 2005". There are some CPU cost examples included. These
number are from the old Redbook SG24-4725 on page 144.
Furthermore are you in Datasharing also consider the Page P-lock
negotiation overhead for RLL.
I know, it might not be the numbers you are looking for, but w'll keep
on looking.

Vriendelijke groet,
Douwe van Sluis

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
Nick Nur
Verzonden: woensdag 21 november 2007 19:57
Aan: [login to unmask email]
Onderwerp: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row level
locking
in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one side and

memory and CPU on the other side. I am aware how argue for each side but
I
am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes of
memory.
So I can manage the analysis of memory. However I like to know how many
#
of instructions a row lock needs and how can I compute the additional
CPU for
it and if somebody gives me a number could you be kind enough tell me in

which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list to dare
asking such questions pertaining to our work. Thank you in advance.
Nick Nur

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



---------------------------------
Looking for a X-Mas gift? Everybody needs a Flickr Pro Account!

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: row level locking
(in response to Nick Nur)
Nick

Head to www.ca.com/db and there is a link to the Performance Handbook

Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.




________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nicola Nur
Sent: 22 November 2007 14:29
To: [login to unmask email]
Subject: Re: [DB2-L] row level locking


Hartelijk bedunkt !

That is exactly the figure I want.
Is it possible to have a copy of that document that you were quoting
from?
thanks again
Nick Nur

Douwe van Sluis <[login to unmask email]> wrote:

Hi Nick,

Here again some info I found. This time in the latest "Susan
Lawson and
Dan Luksetich Explain Application Tuning for DB2" as provided by
CA,
page 145.

Tips for Avoiding Locks
Lock avoidance has been around since Version 3, but are you
getting it?
Lock avoidance can be a key component to high performance
because to
take an actual lock is about 400 CPU instructions and 540 bytes
of
memory, to avoid a lock a latch is taken by the buffer manager
and the
cost is significantly less.

Vriendelijke groet,
Douwe van Sluis

-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email]
Namens
Nick Nur
Verzonden: woensdag 21 november 2007 19:57
Aan: [login to unmask email]
Onderwerp: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row
level
locking
in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one
side and

memory and CPU on the other side. I am aware how argue for each
side but
I
am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes
of
memory.
So I can manage the analysis of memory. However I like to know
how many
#
of instructions a row lock needs and how can I compute the
additional
CPU for
it and if somebody gives me a number could you be kind enough
tell me in

which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list
to dare
asking such questions pertaining to our work. Thank you in
advance.
Nick Nur


------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From
that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L
discussion listserv that are being implemented to improve reliability
and the overall user experience of DB2-L. These changes are coming on
November 30th. Details at http://www.idug.org


------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



________________________________

Be smarter than spam. See how smart SpamGuard is at giving junk email
the boot with the All-new Yahoo! Mail
< http://ca.promos.yahoo.com/newmail/overview2/ > IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: row level locking
(in response to Phil Grainger)
Chuck Hoover has finally really retired.

He came to Compuware several years ago after taking early retirement
after 30+ years with IBM.
He sortof retired maybe 4 or 5 years ago and would do occasional
presentations for us if he chose to.

I think it's been a couple or more years since he retired for good.
He might even have retired from playing hockey, but I'm not sure about
that.

Dave




The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Nicola Nur
Sent: Wednesday, November 21, 2007 9:39 PM
To: [login to unmask email]
Subject: Re: [DB2-L] row level locking


Thanks very much Phil.
I am aware of what you have explained however I have been asked if I
know how much a lock costs in mips in v8.1. if I am able to find that
out I can incorporate it into a good work on the subject. I wonder if
anybody knows Chuck Hoover telephone number or his email if he still
around. I know he can give me the answer.
Thanks Phil again.


"Grainger, Phil" <[login to unmask email]> wrote:

Hi Nik,

How's this for "quick and dirty"

Let's assume that the cost of a "lock" is the same, regardless
of
whether it's a page or row lock

If all access is DIRECTLY to a row, then the lock "costs" will
be
similar

BUT

As soon as you get into a scan, and start touching every row on
a page,
the lock costs will escalate astronomically

What's the average number of rows per page? 50?? Then row
locking will
be 50 times as costly as page locking for a sequential scan

Conventional wisdom has it that UNLESS YOU HAVE SPECIFIC OTHER
REQUIREMENTS, page locking is the way to go. Row and
Table/Tablespace
locks are solutions for specific problems and should not really
be
considered "usual"

Hope this helps (a bit)


Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the
address
set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On
Behalf Of Nick Nur
Sent: 21 November 2007 18:57
To: [login to unmask email]
Subject: [DB2-L] row level locking

Hi colleagues
I need your help if possible.
I am preparing a case with advantages and disadvantages of row
level
locking in DB2 z/OS v8.1.
To me the whole issue is a trade off between concurrency on one
side and
memory and CPU on the other side. I am aware how argue for each
side but
I am asked to quantify my argument.
I know from memory point of view a single lock costs 540 bytes
of
memory.
So I can manage the analysis of memory. However I like to know
how many
# of instructions a row lock needs and how can I compute the
additional
CPU for it and if somebody gives me a number could you be kind
enough
tell me in which manual or document it is mentioned.
Does the diagnosis manual have such info.
thank God that we DBA's and system programmers have such a list
to dare
asking such questions pertaining to our work. Thank you in
advance.
Nick Nur


------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From
that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences
at http://conferences.idug.org/index.cfm


------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



________________________________

Looking for a X-Mas gift? Everybody needs a Flickr Pro Account!
< http://www.flickr.com/gift/ > IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

William Favero

Re: row level locking
(in response to David Seibert)
I can't imagine Chuck ever NOT playing Hockey... (smile)..

Willie

Seibert, Dave wrote:
> Chuck Hoover has finally really retired.
>
> He came to Compuware several years ago after taking /*early*/
> retirement after 30+ years with IBM.
> He sortof retired maybe 4 or 5 years ago and would do occasional
> presentations for us if he chose to.
>
> I think it's been a couple or more years since he retired for good.
> He might even have retired from playing hockey, but I'm not sure about
> that.
>
> Dave
>
>
>
>
>
> The contents of this e-mail are intended for the named addressee only.
> It contains information that may be confidential. Unless you are the
> named addressee or an authorized designee, you may not copy or use it,
> or disclose it to anyone else. If you received it in error please
> notify us immediately and then destroy it.
>
> *From:* DB2 Data Base Discussion List [mailto:[login to unmask email] *On
> Behalf Of *Nicola Nur
> *Sent:* Wednesday, November 21, 2007 9:39 PM
> *To:* [login to unmask email]
> *Subject:* Re: [DB2-L] row level locking
>
> Thanks very much Phil.
> I am aware of what you have explained however I have been asked if I
> know how much a lock costs in mips in v8.1. if I am able to find that
> out I can incorporate it into a good work on the subject. I wonder if
> anybody knows Chuck Hoover telephone number or his email if he still
> around. I know he can give me the answer.
> Thanks Phil again.
>
>
> */"Grainger, Phil" <[login to unmask email]>/* wrote:
>
> Hi Nik,
>
> How's this for "quick and dirty"
>
> Let's assume that the cost of a "lock" is the same, regardless of
> whether it's a page or row lock
>
> If all access is DIRECTLY to a row, then the lock "costs" will be
> similar
>
> BUT
>
> As soon as you get into a scan, and start touching every row on a
> page,
> the lock costs will escalate astronomically
>
> What's the average number of rows per page? 50?? Then row locking will
> be 50 times as costly as page locking for a sequential scan
>
> Conventional wisdom has it that UNLESS YOU HAVE SPECIFIC OTHER
> REQUIREMENTS, page locking is the way to go. Row and Table/Tablespace
> locks are solutions for specific problems and should not really be
> considered "usual"
>
> Hope this helps (a bit)
>
>
> Phil Grainger
> CA
> Product Management Director
> Phone: +44 (0)1753 577 733
> Mobile: +44 (0)7970 125 752
> eMail: [login to unmask email]
>
> Ditton Park
> Riding Court Road
> Datchet
> Slough
> SL3 9LL
>
> CA plc a company registered in England and Wales under company
> registration number 1282495 with its registered office at the address
> set out above. VAT number 697904179.
>
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Nick Nur
> Sent: 21 November 2007 18:57
> To: [login to unmask email]
> Subject: [DB2-L] row level locking
>
> Hi colleagues
> I need your help if possible.
> I am preparing a case with advantages and disadvantages of row level
> locking in DB2 z/OS v8.1.
> To me the whole issue is a trade off between concurrency on one
> side and
> memory and CPU on the other side. I am aware how argue for each
> side but
> I am asked to quantify my argument.
> I know from memory point of view a single lock costs 540 bytes of
> memory.
> So I can manage the analysis of memory. However I like to know how
> many
> # of instructions a row lock needs and how can I compute the
> additional
> CPU for it and if somebody gives me a number could you be kind enough
> tell me in which manual or document it is mentioned.
> Does the diagnosis manual have such info.
> thank God that we DBA's and system programmers have such a list to
> dare
> asking such questions pertaining to our work. Thank you in advance.
> Nick Nur
>
> ------------------------------------------------------------------------
> ---------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences
> at http://conferences.idug.org/index.cfm
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> and home page at http://www.idugdb2-l.org/archives/db2-l.html.
> From that page select "Join or Leave the list". The IDUG DB2-L FAQ
> is at http://www.idugdb2-l.org. The IDUG List Admins can be
> reached at [login to unmask email] Find out the latest on
> IDUG conferences at http://conferences.idug.org/index.cfm
>
>
> ------------------------------------------------------------------------
> Looking for a X-Mas gift? *Everybody needs a Flickr Pro Account!*
> < http://www.flickr.com/gift/ > IMPORTANT NOTICE:
>
> IDUG is pleased to announce a series of upgrades to the DB2-L
> discussion listserv that are being implemented to improve reliability
> and the overall user experience of DB2-L. These changes are coming on
> November 30th. Details at http://www.idug.org
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>
>
> IMPORTANT NOTICE:
>
> IDUG is pleased to announce a series of upgrades to the DB2-L
> discussion listserv that are being implemented to improve reliability
> and the overall user experience of DB2-L. These changes are coming on
> November 30th. Details at http://www.idug.org
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>

--
Willie
My DB2 blog --> http://blogs.ittoolbox.com/database/db2zos

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm