Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Updating Cursor row withour using FOR UPDATE

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

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Nov 08, 2016 11:17 am    Post subject: Updating Cursor row withour using FOR UPDATE
Reply with quote

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
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Nov 09, 2016 3:10 am    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Nov 09, 2016 11:19 am    Post subject:
Reply with quote

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 icon_sad.gif

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 icon_smile.gif

So though of checking here. I am still researching on it. I will post it here if I get anything.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Wed Nov 09, 2016 4:45 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Nov 09, 2016 5:31 pm    Post subject:
Reply with quote

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 icon_smile.gif
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Wed Nov 09, 2016 7:39 pm    Post subject:
Reply with quote

Quote:
and unfortunately I can't share that as well

Even if you obfuscate sensitive data?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Nov 09, 2016 9:48 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Nov 09, 2016 9:57 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Nov 10, 2016 12:50 am    Post subject:
Reply with quote

Please try declaring it an insensitive cursor and let us know.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Nov 10, 2016 4:01 am    Post subject: Reply to: Updating Cursor row withour using FOR UPDATE
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Nov 10, 2016 10:32 am    Post subject: Re: Reply to: Updating Cursor row withour using FOR UPDATE
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Nov 10, 2016 10:42 am    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2164
Location: @my desk

PostPosted: Thu Nov 10, 2016 12:08 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Nov 10, 2016 12:18 pm    Post subject:
Reply with quote

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
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Nov 10, 2016 5:24 pm    Post subject:
Reply with quote

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
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Thu Nov 10, 2016 9:36 pm    Post subject: Reply to: Updating Cursor row withour using FOR UPDATE
Reply with quote

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
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 Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Allocate dataset withour BLKSIZE Susanta CLIST & REXX 2 Thu Sep 22, 2016 11:51 am
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us