|
View previous topic :: View next topic
|
| Author |
Message |
Susanta
Active User
Joined: 17 Nov 2009
Posts: 129
Location: India
|
|
|
|
Hi
I just need to know whether a DB2 cursor gets closed by a -911 error or not?
Please help.
Thanks |
|
| Back to top |
|
 |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 129
Location: India
|
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| 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 |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 129
Location: India
|
|
|
|
Hi
Now its confirmed that a -911 closes the cursor . 
If i declare the cursor with WITH HOLD .. will it overcome such a closure by -911 ? |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| 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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi All,
Apologies.. my bad..lesson leanred
Regards,
Chandan |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 129
Location: India
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| 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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| BTW, your retry is done with issuing a delay (by something) in the execution? Please explain. |
|
| Back to top |
|
|
Susanta
Active User
Joined: 17 Nov 2009
Posts: 129
Location: India
|
|
|
|
| yes we are using a counter to have dealy. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
