If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > DB2 > What is FOR FETCH ONLY parameter

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 1,595
What is FOR FETCH ONLY parameter

Hi,

I am using DB2 v7.2 on Windows 2000.

According to the documentation "Administration Guide Performance" the FOR FETCH ONLY parameter after select statement

should benefit the retrive performance, because of minimal locking. Does anybody know why is locking so minimal? What is the diference betwen WITH UR parameter after select statements with also has something to do with locking.

Thanks,
Grofaty
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2002
Location: Germany
Posts: 141
UR stays for Uncommited Read. If you use it, you may get some pieces of information that are "out of date". DB2 will not lock the pages you're reading.
With FOR FETCH ONLY, you're saying to DB2, that you do not intend to update the rows you are reading. DB2 will try to avoid locking the pages you are reading, but will only give you "clean" pages, i.e., pages that are allready commited.

HTH,
__________________
Rodney Krick
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 1,595
Hi,

Thank you RKrick. It helps me very much.

Grofaty

Quote:
Originally posted by RKrick
UR stays for Uncommited Read. If you use it, you may get some pieces of information that are "out of date". DB2 will not lock the pages you're reading.
With FOR FETCH ONLY, you're saying to DB2, that you do not intend to update the rows you are reading. DB2 will try to avoid locking the pages you are reading, but will only give you "clean" pages, i.e., pages that are allready commited.

HTH,
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Location: Canada
Posts: 37
Re: What is FOR FETCH ONLY parameter

Please note the FOR FETCH|READ ONLY and the FOR UPDATE OF ... clauses are ignored for dynamic statements resulting in the creation of ambiguous cursors regardless of their presence. So if you are expecting any performance increase in such environments, you may be in for a bit of surprise.

Cheers,

Julius
Reply With Quote
  #5 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Re: What is FOR FETCH ONLY parameter

Thanks for the info ... It is interesting ....

Do you have a pointer to the IBM manuals which explain this bit for dynamic statements ...

Cheers

Sathyaram


Quote:
Originally posted by jsasvari
Please note the FOR FETCH|READ ONLY and the FOR UPDATE OF ... clauses are ignored for dynamic statements resulting in the creation of ambiguous cursors regardless of their presence. So if you are expecting any performance increase in such environments, you may be in for a bit of surprise.

Cheers,

Julius
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Location: Canada
Posts: 37
Re: What is FOR FETCH ONLY parameter

The following is extracted from the redbook "Squeezing the Most Out of Dynamic SQL with DB2 for z/OS and OS/390" section 10.2 Ambiguous cursors. I hope it helps.

Cheers,

Julius
----

A cursor is ambiguous if:
 It is not defined with the clauses:
FOR FETCH ONLY or FOR READ ONLY. If these clauses are present, it is a read-only cursor.
FOR UPDATE OF. This makes the cursor updatable.
 It is not defined on a read-only result table (which would make it a read-only cursor).
 It is not the target of a WHERE CURRENT clause on an SQL UPDATE or DELETE statement. (This would make the cursor updatable.)
 It is in a plan or package that contains the SQL statements PREPARE or EXECUTE IMMEDIATE:
The presence of any dynamic SQL causes an ambiguous cursors, because DB2 cannot detect what follows in the program.
A cursor can appear to be read-only, but a dynamic SQL statement could modify data through the cursor. Therefore the cursor is ambiguous.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Posts: 3
Grofaty is using DB2 for UDB. DB2 for UDB and DB2 for z/OS don't always work the same way. So here's a another opinion (which is worth what it cost).

The DB2 UDB version 7 SQL reference has the following explanation of the FOR READ ONLY clause. It seems to indicate that it is effective in dynamic SQL.

---------------------------------------------
The FOR READ ONLY clause indicates that the result table is read-only and therefore the cursor cannot be referred to in Positioned UPDATE and DELETE statements. FOR FETCH ONLY has the same meaning.

Some result tables are read-only by nature. (For example, a table based on a read-only view.) FOR READ ONLY can still be specified for such tables, but the specification has no effect.

For result tables in which updates and deletes are allowed, specifying FOR READ ONLY (or FOR FETCH ONLY) can possibly improve the performance of FETCH operations by allowing the database manager to do blocking and avoid exclusive locks. For example, in programs that contain dynamic SQL statements without the FOR READ ONLY or ORDER BY clause, the database manager might open cursors as if the FOR UPDATE clause was specified. It is recommended, therefore, that the FOR READ ONLY clause be used to improve performance except in cases where queries will be used in a Positioned UPDATE or DELETE statements.

A read-only result table must not be referred to in a Positioned UPDATE or DELETE statement, whether it is read-only by nature or specified as FOR READ ONLY (FOR FETCH ONLY). See DECLARE CURSOR for more information about read-only and updatable cursors.
---------------------------------------------

In the DECLARE CURSOR explanation is the following note

---------------------------------------------
A cursor is ambiguous if all of the following are true:

the select-statement is dynamically prepared
the select-statement does not include either the FOR READ ONLY clause or the FOR UPDATE clause
the LANGLEVEL bind option is SAA1
the cursor otherwise satisfies the conditions of a deletable cursor.
An ambiguous cursor is considered read-only if the BLOCKING bind option is ALL, otherwise it is considered deletable.
----------------------------------------------

From these two explanations, I would say that in DB2 UDB the FOR READ ONLY clause is useful in both static and dynamic SQL. This, however, depends on one small word--"all". If DB2 meant to say "A cursor is ambiguous if ANY fo the following are true:" then jsasvari may be correct. (How's that for hedging?)

Plumb
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On