Hi,
I had the following doubts about the "For Read Only" clause.
1. How does a "for Read only" clause improve the performance?
2. How does a "for Read only" clause compare with "With UR" clause in
performance? Which is faster?
Can someone clarify on that?
Thanks a lot.
Rahul
3  13223 
"Rahul Babbar" <ra***********@ gmail.comwrote in message
news:55******** *************** ***********@s13 g2000prd.google groups.com...
Hi,
I had the following doubts about the "For Read Only" clause.
1. How does a "for Read only" clause improve the performance?
2. How does a "for Read only" clause compare with "With UR" clause in
performance? Which is faster?
Can someone clarify on that?
Thanks a lot.
Rahul
The "for read only" can minimize the degree of locking in certain cases to
prevent anything higher than a share lock from being taken where DB2 thinks
your cursor "intent" is ambiguous and it takes a lock stronger than share.
It can also affect cursor blocking.
WITH UR prevents even a share lock on a SELECT statement. This potentially
might be help prevent lock contention in some cases, so long as you are
willing to live with any issues caused by inconsistent data being read
(where another transaction has not finished its unit of work and you are
seeing only some of the updates in an uncommitted transaction). But keep in
mind that multiple share locks on the same resource (row, index, table, etc)
do not conflict (and do not cause lockwaits), so WITH UR will only help in
"reading through" IX, U, X, etc locks that can cause lockwaits or deadlocks
on the statement that has the WITH UR, or another transaction that is trying
to update a row the WITH UR is selecting..
On Feb 8, 12:56 pm, "Mark A" <nob...@nowhere .comwrote:
"Rahul Babbar" <rahul.babb...@ gmail.comwrote in message
news:55******** *************** ***********@s13 g2000prd.google groups.com...
Hi,
I had the following doubts about the "For Read Only" clause.
1. How does a "for Read only" clause improve the performance?
2. How does a "for Read only" clause compare with "With UR" clause in
performance? Which is faster?
Can someone clarify on that?
Thanks a lot.
Rahul
The "for read only" can minimize the degree of locking in certain cases to
prevent anything higher than a share lock from being taken where DB2 thinks
your cursor "intent" is ambiguous and it takes a lock stronger than share.
It can also affect cursor blocking.
Thanks a lot.
How does the DB decide whether the cursor "intent" in ambigous?
Can you please explain that?
WITH UR prevents even a share lock on a SELECT statement. This potentially
might be help prevent lock contention in some cases, so long as you are
willing to live with any issues caused by inconsistent data being read
(where another transaction has not finished its unit of work and you are
seeing only some of the updates in an uncommitted transaction). But keep in
mind that multiple share locks on the same resource (row, index, table, etc)
do not conflict (and do not cause lockwaits), so WITH UR will only help in
"reading through" IX, U, X, etc locks that can cause lockwaits or deadlocks
on the statement that has the WITH UR, or another transaction that is trying
to update a row the WITH UR is selecting..
Also,
I read that "for read only" may improve the performance because the
DBM can retrieve blocks of data.
What exactly does that mean?
If it does increase the performance of the DB, will it be better "With
UR"? (i personally don't think so, i mean if the DBM can retrieve
blocks of data in a read only clause, why not it can do the same thing
in "with UR")
Thanks a Lot
Rahul
Rahul Babbar wrote:
On Feb 8, 12:56 pm, "Mark A" <nob...@nowhere .comwrote:
>"Rahul Babbar" <rahul.babb...@ gmail.comwrote in message
news:55******* *************** ************@s1 3g2000prd.googl egroups.com...
>>Hi,
I had the following doubts about the "For Read Only" clause.
1. How does a "for Read only" clause improve the performance?
2. How does a "for Read only" clause compare with "With UR" clause in
performance ? Which is faster?
Can someone clarify on that?
Thanks a lot.
Rahul
The "for read only" can minimize the degree of locking in certain cases to
prevent anything higher than a share lock from being taken where DB2 thinks
your cursor "intent" is ambiguous and it takes a lock stronger than share.
It can also affect cursor blocking.
Thanks a lot.
How does the DB decide whether the cursor "intent" in ambigous?
Can you please explain that?
The intent is ambigous if you have NOT specified our intent (read,
update, delete), but the cursor is "deletable" .
I like this link to define what that means (never mind that it talks
about views. One set of rules for all): http://publib.boulder.ibm.com/infoce.../c0052324.html
>WITH UR prevents even a share lock on a SELECT statement. This potentially
might be help prevent lock contention in some cases, so long as you are
willing to live with any issues caused by inconsistent data being read
(where another transaction has not finished its unit of work and you are
seeing only some of the updates in an uncommitted transaction). But keep in
mind that multiple share locks on the same resource (row, index, table, etc)
do not conflict (and do not cause lockwaits), so WITH UR will only help in
"reading through" IX, U, X, etc locks that can cause lockwaits or deadlocks
on the statement that has the WITH UR, or another transaction that is trying
to update a row the WITH UR is selecting..
I read that "for read only" may improve the performance because the
DBM can retrieve blocks of data.
What exactly does that mean?
This is what Mark referred to as "blocking".
Blocking means that DB2 will toss a "block" of rows over to the client.
Subsequent FETCH operation by the client are served from this buffer,
greatly reducing latency. So blocking is very important for application
performance.
If it does increase the performance of the DB, will it be better "With
UR"? (i personally don't think so, i mean if the DBM can retrieve
blocks of data in a read only clause, why not it can do the same thing
in "with UR")
These are really orthogonal. The point is that if a cursor is FOR UPDATE
or FOR DELETE then DB2 has to keep the position of the cursor because it
expects an DELETE/UPDATE WHERE CURRENT OF.
That is the reason to not do blocking.
Aside from the concurrency, semantic differences WITH UR has one more
effect:
A lock that is not taken does not have codepath overhead.
In a data warehouse environement the difference between CS and UR can be
significant (high single to low double digit percent!)
So what you want to do is this:
If a cursor is meant to be read only: Mark it as READ ONLY
AND
If you don't mind reading uncommitted data specify WITH UR
Always tell DB2 what you expect to get the best performance.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Invalid User |
last post by:
While trying to print a none empty list, I accidentaly put an "else"
statement with a "for" instead of "if". Here is what I had:
if ( len(mylist)> 0) :
for x,y in mylist:
print x,y
else:
print "Empty list"
which was supposed to be:
|
by: Fie Fie Niles |
last post by:
I have IIS installed on XP Professional workstation machine. I have an ASP
page that open connection to an Access database, then when trying to update
the database, it gave me the error "cannot update database or object is
read-only". This is a workstation machine, not connected to any other
computer, and I login to the PC using an administrator account. I already
check the .MDB file is NOT read-only. But, I do not see the .LDB file....
|
by: Martin |
last post by:
Hello everybody,
I have the following question.
As a join clause on Oracle we use " table1.field1 = table2.field1 (+) "
On SQL Server we use " table1.field1 *= table2.field1 "
Does DB2 have the same type of operator, without using the OUTER JOIN
syntax ?
|
by: Rich |
last post by:
Hi,
I'm having problems with changing the Read Only properties.
Running Winxp and i cannot get the "read only" to clear off a folder.
The folder is one that i want to change the data in and it won't
let me.
I have two accounts on the main system and cannot change the
properties from either administrator account.
I have tried renaming the folder and putting it back into the program
and it still won't work. All files in the folder are...
|
by: Vic |
last post by:
Dear All,
I have a database of laboratory records in Access 2000. There is one
form which acts as an interface to input experimetal data. This form
incorporates information from several tables. I have a flag (yes/no
field) indicating whether a particular experiment (one record) is
completed (ie all data belonging to that record is inputted and
quality controlled). What I want is that when I change the flag to
"yes" the particular record...
| | |
by: Helge Jensen |
last post by:
I've got some data that has Set structure, that is membership, insert
and delete is fast (O(1), hashing). I can't find a System.Collections
interface that matches the operations naturally offered by Sets.
- ICollection cannot decide containment
- IList promises indexability by the natural numbers, which is not
achievable (since i hash elements, not sort them).
- IDictionary is definatly not setlike.
Although I can, of course, define...
|
by: John E Katich |
last post by:
When attempt to use the Event Wizard I get the following error message:
"Add/Remove of the function impossible, because the parent class code is
read only"
The Project was convert from VC 6.0. The files in question do not have the
Read-Only Attribute on.
Any Ideas?
|
by: S.Guhananth |
last post by:
I need to capture a country from dropdown list and use that in sql query.
How to do this
protected DataSet GetCountry()
{
I have the code for this block
|
by: tsaar2003 |
last post by:
Hi Pythonians,
To begin with I'd like to apologize that I am not very experienced
Python programmer so please forgive me if the following text does not
make any sense.
I have been missing constants in Python language. There are some
workarounds available, for example the const-module. To me, this looks
quite cumbersome and very unintuitive. For the interpreter, in the
efficiency-wise, I just cannot tell.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
