|
View previous topic :: View next topic
|
| Author |
Message |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi All,
My cursor query is as below
| Code: |
EXEC SQL
DECLARE CUR2 CURSOR FOR
SELECT COL1, COL2
FROM TABLE1
WHERE COL3 = 'ABC'
END-EXEC. |
For table has only 1 row satisfying above criteria with COL1 = 'XYZ'
Below are the steps I am following
1. Open the cursor
2. Fetch the cursor which will eventually give only one row
3. depending on some condition run below update query
| Code: |
EXEC SQL
UPDATE TABLE1
SET COL1 = 'DEF'
WHERE COL3='ABC' AND COL1='XYZ'
END-EXEC. |
4. Go and fetch the cursor again I was expecting the cursor to return sqlcode 100 but its retruning the row with COL1 = 'DEF'
When I tried same process with using FOR UPDATE and CURRENT OF step 4 above returning sqlcode 100 as expected ans I am aware this is the ideal way to do it which I am following now
I know I am definitely missing something here but not able to figure out what I am missing
My Bind option are as below
ISOLATION LEVEL - CS
CURRENT DATA - YES
Is it something do with CURRENT DATA? I tried to search in manuals but did not find satisfactory answers
Thanks,
Chandan |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Quote: |
| Go and fetch the cursor again I was expecting the cursor to return sqlcode 100 but its returning the row with COL1 = 'DEF' |
Then the cursor must go in infinite loop, how and when does it stop otherwise? So somehow I doubt this behavior is true.
Please double check the rows in underlying table or better option is to check GET DIAGNOSTICS statement after update and add display's ( if its a batch) after every update and debug. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Rohit Umarjikar wrote: |
| Quote: |
| Go and fetch the cursor again I was expecting the cursor to return sqlcode 100 but its returning the row with COL1 = 'DEF' |
Then the cursor must go in infinite loop, how and when does it stop otherwise? So somehow I doubt this behavior is true.
Please double check the rows in underlying table or better option is to check GET DIAGNOSTICS statement after update and add display's ( if its a batch) after every update and debug. |
Its not going in Infinite loop its giving sqlcode 100 for next fetch.
I checked everything on underlying tables and as well as with XPED also. Unfortunately I can't share the screen shots here 
And this program is online as of now but will be used in batch as well eventually.
When I changed program to use FOR UPDATE and CURRENT OF it working absolutely fine. My purpose is solved with that but curiosity didn't 
So though of checking here. I am still researching on it. I will post it here if I get anything. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Quote: |
Unfortunately I can't share the screen shots here
|
Fortunately we do not like screen shots. However, we do like cuts and pastes of screen images which anyone should be able to do. And remember to use the code tags. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Nic Clouston wrote: |
| Quote: |
Unfortunately I can't share the screen shots here
|
Fortunately we do not like screen shots. However, we do like cuts and pastes of screen images which anyone should be able to do. And remember to use the code tags. |
and unfortunately I can't share that as well |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Quote: |
| and unfortunately I can't share that as well |
Even if you obfuscate sensitive data? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Quote: |
| Its not going in Infinite loop its giving sqlcode 100 for next fetch |
So you mean to say even if there is only one eligible row from the cursor , you are able to fetch it twice and on third fetch it is giving you sqlcode 100? |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Rohit Umarjikar wrote: |
| Quote: |
| Its not going in Infinite loop its giving sqlcode 100 for next fetch |
So you mean to say even if there is only one eligible row from the cursor , you are able to fetch it twice and on third fetch it is giving you sqlcode 100? |
Yes if I update the record its giving sqlcode 100 for third fetch..
I tried to check this with skipping update process as well that time its giving sqlcode 100 for second fetch
| Terry Heinze wrote: |
| Quote: |
| and unfortunately I can't share that as well |
Even if you obfuscate sensitive data? |
Problem is I work on remote system to access mainframes and cant access this site from that system so I tried my best to replicate my issue |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Please try declaring it an insensitive cursor and let us know. |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| Please try declaring it an insensitive cursor and let us know. |
chandan.inst already knows how to achieve what he wants. He is more interested in knowing the 'Why' of such a behavior when he doesn't use 'FOR UPDATE' clause.
This Link probably explains the 'Why'. As it says:
| Quote: |
| CS also opens the possibility - though usually only a slight one - that rows can be mistakenly processed twice or not at all. |
. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| RahulG31 wrote: |
This Link probably explains the 'Why'. As it says:
| Quote: |
| CS also opens the possibility - though usually only a slight one - that rows can be mistakenly processed twice or not at all. |
. |
Thanks for sharing the link Rahul..
I had visited this earlier but the explanation mentioned on this link did not convince me completely for my case.
it says updates are impacting the WHERE clause and for that case explanation holds correct.
My case not impacting the where clause but impacting the select clause column. So how is does the re-pointing of cursor will happens for fetched row is my curiosity now
Thanks,
Chandan |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Quote: |
| My case not impacting the where clause but impacting the select clause column. So how is does the re-pointing of cursor will happens for fetched row is my curiosity now |
it is impacting the where clause in your case because you did not get the sqlcode 100 for second fetch. I have said about the insensitive cursor to be tried because your updates are so no acting the current cursor. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Rohit Umarjikar wrote: |
| it is impacting the where clause in your case because you did not get the sqlcode 100 for second fetch |
The OPs point is that the column in the WHERE clause of the cursor (COL3) was not UPDATEd, but in the SELECT (COL1) was. How does trying an insensitive cursor help understand the behavior of the OP's 'normal' cursor? |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Arun Raj wrote: |
| The OPs point is that the column in the WHERE clause of the cursor (COL3) was not UPDATEd, but in the SELECT (COL1) was. How does trying an insensitive cursor help understand the behavior of the OP's 'normal' cursor? |
Was about to ask the same question as I also think the same
But yes Rohit I will give a try and let you know.. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Rohit Umarjikar wrote: |
| Please try declaring it an insensitive cursor and let us know. |
It worked with insensitive cursor same as expected like CURRENT OF but I am pretty sure that I am not touching WHERE clause of the cursor during Update.
Is there any possibility when I updated the record its pointer in database is changing?
Thanks,
Chandan |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| Quote: |
| it says updates are impacting the WHERE clause and for that case explanation holds correct. |
chandan.inst, I don't see anything about impacting the WHERE clause in that link (may be I missed something). But, look at your cursor, it says WHERE COL3='ABC'.
And then you update (what you update is less relevant) using, again, WHERE COL3='ABC'.
and that creates an opportunity to have a pseudo (if I can call that) extra row to be created.
So, your cursor thinks it has 2 rows:
| Code: |
COL1 COL3
XYZ ABC <-- This is already processed and cursor pointer at this row
DEF ABC |
Now, when do you a next fetch it fetches second row i.e. COL1 as 'DEF'.
So, cursor is not repositioning the pointer, it is the extra row (that's what my understanding is).
To validate, add an Order by clause in your cursor i.e. ORDER BY COL1 ASC
When you have a cursor ordered by COL1 ASC , that would mean that the DEF row will be before XYZ and then you'll Not get anything on second fetch. i.e.
| Code: |
COL1 COL3
DEF ABC
XYZ ABC <-- You are already here and No more rows left for fetch |
. |
|
| Back to top |
|
|
|
|
