WITH UR and FOR FETCH ONLY

[Glenn Mackey]

Hi,
I want to know if it is redundant to specify the "For Fetch Only" clause when
specifying the "With UR" clause on a SELECT statement.

My understanding is the "For Fetch Only" will invoke block fetching and use lock
avoidance techniques.
This will also happen if "For Fetch Only" is not specified if DB2 can determine
that the SQL is read only.

"With UR" is read only, so will "With UR" cursors get block fetching (it will
get lock avoidance ++). I assume yes so ...

TIA

Glenn Mackey
515 267 5767

[James Gutru]

Glenn,
Block fetching is a function of the CURRENTDATA(YES/NO) bind parameter.
CURRNTDATA(YES) will disable blockfetch. FOR FETCH ONLY
(or V5 FOR READ ONLY) Tells DB2 you are going to only read data when you are
using an ambiguous cursor. It is a good practice to
specify FOR FETCH ONLY or FOR UPDATE OF... for all cursors so there can be no
doubt what you are doing. WITH UR determines the
isolation level at which the statement is executed. The result table must be
read only for UR to be in effect, if not you get CS. To
ensure the result table is read only specify FOR FETCH ONLY and also specify
WITH UR, not redundant. (SQL Reference SC26-8966-00, CH5
Queries). For block fetch to be in effect be sure to bind with CURRENTDATA(NO).
Good Luck,
Jim

Glenn Mackey wrote:

--
James C.. Gutru
Senior Systems Advisor
Themis Education, Inc 1-800-756-3000
Email: jgu...@mix-net.net or jgu...@themised.com
Web: http://www.themised.com
(All suggestions are personal opinions - no warranty expressed or implied)

[Truman G. Brown]

Not redundant; lock avoidance is one thing and read-thru-locks
is another. The difference will be that data read with UR may not be
current/consistent and will incur no locks.

From: owner...@AMERICAN.EDU AT INTERNET on 08/26/98 02:22 PM

To: BRIAN KHONG/EMPL/VA/Bell-Atl, DAN BROGDON/VEND/VA/Bell-Atl,
DEBAPRATIM MUKHERJEE/EMPL/VA/Bell-Atl, TRUMAN G.
BROWN/EMPL/MD/Bell-Atl, DB...@AMERICAN.EDU AT INTERNET@CCDOMAIN
cc:
Subject: WITH UR and FOR FETCH ONLY