|
View previous topic :: View next topic
|
| Author |
Message |
hallecodec
New User
Joined: 05 Sep 2006
Posts: 30
Location: Philippines
|
|
|
|
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 |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
ksk
Active User
.jpg)
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
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 |
|
|
hallecodec
New User
Joined: 05 Sep 2006
Posts: 30
Location: Philippines
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
hallecodec
New User
Joined: 05 Sep 2006
Posts: 30
Location: Philippines
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
| Back to top |
|
|
hallecodec
New User
Joined: 05 Sep 2006
Posts: 30
Location: Philippines
|
|
|
|
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 |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
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 |
|
|
|
|
