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
 

 

DB2 cursor gets closed by a -911

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

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Wed Oct 03, 2012 5:00 pm    Post subject: DB2 cursor gets closed by a -911
Reply with quote

Hi

I just need to know whether a DB2 cursor gets closed by a -911 error or not?

Please help.

Thanks
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Oct 03, 2012 5:13 pm    Post subject:
Reply with quote

As per me it does not get closed until and unless you intentionally close the cursor

or it will get closed when you program processing is over

Regards,
Chandan
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Wed Oct 03, 2012 5:25 pm    Post subject: Reply to: DB2 cursor
Reply with quote

Hi

I found in this old link saying it closes the cursor.


http://www.dbforums.com/db2/1032341-db2-retry-logic-deadlock-handling-simulating-testing.html

Our shop uses DB2 for Zos V10.. hopefully if does not closes the cursor on -911 . But i have still doubt.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Oct 03, 2012 5:32 pm    Post subject: Reply to: DB2 cursor
Reply with quote

since the 911 implies a rollback, better that the cursor be closed

a rollback implies that the business logic must be restarted from the last commit
so a leftover open cursor has very little significance
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Wed Oct 03, 2012 5:33 pm    Post subject:
Reply with quote

Please read.
Quote:
Rollback operation, SQLCODES -404, -652, -679, -802, -901, -904, -909, -910, -911, -913, and -952 may force the cursor to close.

I think, if you've a mechanism like BMC Restart control for DB2, you may control over -911 situation. Again, I need to confirm it with the manual, Sorry.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Oct 03, 2012 5:36 pm    Post subject:
Reply with quote

chandan.inst wrote:
As per me it does not get closed until and unless you intentionally close the cursor

or it will get closed when you program processing is over

Regards,
Chandan


You need to look in manual, this is from the DB V9 manual for sql code -507


Code:
Programmer response: Check for a previous SQL
return code that might have closed the cursor.
SQLCODES -404, -652, -679,-802, -901, -904, -909, -910,
-911, -913, and -952 may force the cursor to close. After
the cursor is closed, any fetches or close cursor
statements receive SQLCODE -501. Any updates or
deletes receive this SQLCODE -507. Correct the logic of
the application program to ensure that the specified
cursor is open at the time the UPDATE or DELETE
statement is executed.
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Wed Oct 03, 2012 5:49 pm    Post subject: Reply to: DB2 cursor
Reply with quote

Hi

Now its confirmed that a -911 closes the cursor . icon_sad.gif

If i declare the cursor with WITH HOLD .. will it overcome such a closure by -911 ?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Wed Oct 03, 2012 6:00 pm    Post subject:
Reply with quote

Quote:
If i declare the cursor with WITH HOLD .. will it overcome such a closure by -911 ?
No.

If you get -911 and it's an error situation... take a long breath, then check if DEADLOCK OR TIMEOUT has occured, next is appropriate action(ie, restart) based on the findings.
Please tell us more about your requirement and the problem you've faced for better suggestion.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Oct 03, 2012 6:30 pm    Post subject:
Reply with quote

Hi All,

Apologies.. my bad..lesson leanred

Regards,
Chandan
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Oct 03, 2012 6:54 pm    Post subject:
Reply with quote

Gnanas N wrote:
Please tell us more about your requirement and the problem you've faced for better suggestion.


Though your comment was right on the money,
and one of the most appropriate comments thus far
the problem, as i see it,
is the TS does not understand what a -911 indicates.
until he does,
he will be content with a typical head-in-the-sand answer of yes or no.
and he will probably not provide a response to your question Gnanas N.
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Thu Oct 04, 2012 10:21 am    Post subject:
Reply with quote

Sorry to all for late reply.. had to leave early due to bad health.

My queries were related to retry logic.

I found the work around from some one here.
We have a cursor and then a delete sql in the pgm.
We don't want to exit the pgm with a -911 or -904 for the delete sql , we will retry the delete sql for certain number of times, if it deletes the row within maximum try and **we will reopen the cursor. if maximum number of retry fails to delete the row then we will exit the program with abend.

**To know when a reopen of cursor required we have to check for sqlcode after every fetch to see if it is -501 or not . if -501 then we need to reopen the cursor. For a -904/-911 error db2 implicitly closes all open cursor and consecutive fetch cursor gives a -501.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Thu Oct 04, 2012 11:06 am    Post subject:
Reply with quote

Okay. Did you care to know why some task/transaction is accessing the resource (ie, rows) at the same time you're trying to delete?
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Thu Oct 04, 2012 11:22 am    Post subject:
Reply with quote

BTW, your retry is done with issuing a delay (by something) in the execution? Please explain.
Back to top
View user's profile Send private message
Susanta

Active User


Joined: 17 Nov 2009
Posts: 118
Location: India

PostPosted: Thu Oct 04, 2012 11:24 am    Post subject:
Reply with quote

yes we are using a counter to have dealy.
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: Thu Oct 04, 2012 7:02 pm    Post subject:
Reply with quote

Hello,

Quote:
yes we are using a counter to have dealy.
Hopefully, this process does not go into a loop adding 1 to a counter until some value is reached. . .

Have you looked at how much cpu is required to do this. The people who monitor system usage will come looking for you if this is something that happens regularly.

You need to consider a different implementation . . .
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 Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm
No new posts ISREDIT - Position Cursor on the Comm... Tarique Anwer TSO/ISPF 5 Wed Feb 03, 2016 12:44 pm
No new posts Need a cursor to achieve the below re... sandeep kumar302 DB2 10 Tue Mar 31, 2015 10:52 am


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