473,372 Members | 1,471 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Doubts about the "For Read Only" clause

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
Feb 8 '08 #1
3 13141
"Rahul Babbar" <ra***********@gmail.comwrote in message
news:55**********************************@s13g2000 prd.googlegroups.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..
Feb 8 '08 #2
On Feb 8, 12:56 pm, "Mark A" <nob...@nowhere.comwrote:
"Rahul Babbar" <rahul.babb...@gmail.comwrote in message

news:55**********************************@s13g2000 prd.googlegroups.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
Feb 8 '08 #3
Rahul Babbar wrote:
On Feb 8, 12:56 pm, "Mark A" <nob...@nowhere.comwrote:
>"Rahul Babbar" <rahul.babb...@gmail.comwrote in message

news:55**********************************@s13g200 0prd.googlegroups.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
Feb 8 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
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:...
1
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...
3
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...
7
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...
3
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....
21
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...
0
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....
1
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
25
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...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.