Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
"FOR FETCH ONLY" verification

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
hallecodec

New User


Joined: 05 Sep 2006
Posts: 30
Location: Philippines

PostPosted: Thu Jun 26, 2008 11:56 am    Post subject: "FOR FETCH ONLY" verification
Reply with quote

hi,

i would like to ask if including a "FOR FETCH ONLY" parameter in declaring a CURSOR inside a COBOL program is also equal to including a "WITH UR" in running SQL statements in QMF. Please advise. Thanks in advance.
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jun 26, 2008 3:00 pm    Post subject:
Reply with quote

UR stands for Uncommited Read.
If you use it, you may get some pieces of information that are "out of date" or basically phantoms, nonrepeateable reads, and dirty reads can occur .... . DB2 will not lock the pages you're reading. ... and DB2 will lock the table if any other transaction tried to drop the table which you are reading ...

With FOR FETCH ONLY, you're telling 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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Jun 26, 2008 3:01 pm    Post subject:
Reply with quote

NO. The two mean different things:

FOR FETCH ONLY - means the cursor is read only, issues different types of locks and does not allow for update.


WITH UR - means instead of retrieving only committed work, your SELECT or FETCH will also retrieve rows that have not been committed by other tasks.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Thu Jun 26, 2008 3:13 pm    Post subject:
Reply with quote

Hi,

If a cursor is meant to be read only, Mark it as FOR FETCH ONLY
and
If you don't mind reading uncommitted data specify WITH UR.
Back to top
View user's profile Send private message
hallecodec

New User


Joined: 05 Sep 2006
Posts: 30
Location: Philippines

PostPosted: Thu Jun 26, 2008 4:12 pm    Post subject: Reply to: "FOR FETCH ONLY" verification
Reply with quote

hi guys,

thanks for your replies.

with that, i'm planning to use the "FOR FETCH ONLY" parameter to my CURSOR (SELECT) in a COBOL program so it will not "lock" the DB2 table it's SELECTing. Because this is the problem i'm encountering:

Quote:

Contention scenario: PROGRAM1 accesses DB2 table DBTAB1 by using a cursor. Then, while PROGRAM1 is still running, PROGRAM2 also accessed DBTAB1 by inserting records to it. With that, the INSERT of PROGRAM2 ended up in error (sqlcode -911).


Will "FOR FETCH ONLY" command avoid DB2 contention in my jobs? please advise.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Jun 26, 2008 4:20 pm    Post subject:
Reply with quote

read only selects and fetches also require periodic commits. I imagine that is your problem. you don't realize that even a read only will issue locks. The periodic commits will release those locks and allow other tasks to access/update the table.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jun 26, 2008 4:32 pm    Post subject:
Reply with quote

read only issues the share lock where other transactions can only read but not modify the data ...
Make your cursor unambiguous so that DB2 does not have to assume and take locks on the table ... use WITH UR in ur cursor declaration ...
Back to top
View user's profile Send private message
hallecodec

New User


Joined: 05 Sep 2006
Posts: 30
Location: Philippines

PostPosted: Fri Jun 27, 2008 11:46 am    Post subject: Reply to: "FOR FETCH ONLY" verification
Reply with quote

thanks again for your answers and tips.
pardon me for this question, but if I declared a simple cursor (with no "FOR FETCH ONLY" parameter) like this:

Code:

EXEC SQL
DECLARE CURNAME1 CURSOR FOR
SELECT FIELD1, FIELD2
FROM TABLE1
WHERE FIELD3=VAR3
    AND FIELD4=VAR4
    AND FIELD5=VAR5
END-EXEC.


what will be its default setting? or please advise for a link to a manual/website where the answer for my question could be obtained. thanks.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jun 27, 2008 12:04 pm    Post subject:
Reply with quote

Hello,

See if this helps - the link is from the DB2 V8 SQL Reference material available vis the "IBM Manuals" link at the top of the page:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/5.42
Back to top
View user's profile Send private message
hallecodec

New User


Joined: 05 Sep 2006
Posts: 30
Location: Philippines

PostPosted: Fri Jun 27, 2008 1:52 pm    Post subject: Reply to: "FOR FETCH ONLY" verification
Reply with quote

hi dick scherrer, thanks for the link.

i've research that site and seen this:

Code:

4.4 select-statement   
    ________________________________________________________________________

   |                                                                        |
   | >>__ _______________________________ __fullselect____________________> |
 | |     |_WITH__common-table-expression_|                                  |
   |                                                                        |
   |                            <_______________________  (3)               |
   | >__ ____________________ ___ _____________________ |________________>< |
   |    |                 (1)|   |_fetch-first-clause__|                    |
   |    |_order-by-clause____|   |               (2)   |                    |
   |                             |_update-clause_______|                    |
   |                             |_read-only-clause____|                    |
   |                             |_optimize-for-clause_|                    |
   |                             |_isolation-clause____|                    |
   |                             |_queryno-clause______|                    |



It can be seen that the 'read-only-clause' was an optional parameter for the select statement. Am i right to say that by default, a cursor with no "FOR FETCH ONLY" will lock a DB2 table? and by specifying that clause, the cursor will not lock the DB2 table and other jobs/programs could do other manipulation like INSERT/UPDATE? please confirm/advise.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Jun 27, 2008 7:38 pm    Post subject:
Reply with quote

Hi

There are three types of cursor.

1. Read Only
2. Updatable
3. Ambiguous Cursor

Read Only Cursor:

In Read Only cursor, DB2 will be able to perform record blocking to retrieve multiple records and does not worry about acquiring locks that allow the rows to be updated.

If we specify FOR FETCH ONLY, the DB2 will only block the records and it will not put any locks on the selected records.

If we use Order by or Group by clause then DB2 will automatically assume this as Read only Cursor.

Updatable Cursor:
A cursor is updatable when FOR UPDATE clause is specified in its SELECT
statement, meaning that rows are updated through an Update Where Current Of statement. There can only be one table referenced in the SELECT statement.

Ambiguous cursors

A cursor is ambiguous when DB2 cannot determine from the
cursor definition whether it is read-only or updatable. In other words, when the cursor's SELECT statement has neither FOR READ ONLY nor FOR UPDATE specified, it is ambiguous.

For an Ambiguous cursor, DB2 chooses whether to do
record blocking for the select based on the value of the BLOCKING option on the BIND command for the application.

The above cursor which you have given is an ambiguous and records would be selected based on your BLOCKING option on the BIND command.

Hope this helps.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts Changeman get multiple "Browse C... elixir1986 Compuware & Other Tools 4 Fri Jul 14, 2017 1:48 am
No new posts Include/omit with "ALL" pa... mgl DFSORT/ICETOOL 6 Wed Jun 14, 2017 2:31 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us