IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 WITH HOLD cursor


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
debojyoti

New User


Joined: 11 Jun 2008
Posts: 1
Location: Kolkata

PostPosted: Wed Jun 11, 2008 10:31 pm
Reply with quote

Hi, I need a help to a problem I am facing.

I have a Program A that calls a program B. In program B, I have declared a cursor WITH HOLD on a DB2 table. Now, each time A calls B, a row is fetched from the cursor and returned to pgm A. Now, if program A terminates without closing the cursor in program B, will the cursor be closed automatically. or it will remain open.

After the program exits, is there any way to check if the cursor was closed, or is it still in open state?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Jun 12, 2008 6:26 am
Reply with quote

Hello debojyoti and welcome to the forums,

Quote:
Now, if program A terminates without closing the cursor in program B, will the cursor be closed automatically. or it will remain open.
When programA terminates, the cursor will no longer exist and any holds will be released. This would happen whether the cursor was used in programA or programB. When A terminates, the resources for that run unit are released.
Back to top
View user's profile Send private message
anv2005

New User


Joined: 14 Jul 2005
Posts: 44
Location: US

PostPosted: Thu Jun 12, 2008 11:25 pm
Reply with quote

I respectfully disagree.

Many shops utilize what they call a SQL driver program. This is a sub-program that contains all their SQL statements. Such sub-program is called by many callers and may OPEN, FETCH and CLOSE cursor(s) SQL statements among any other SQL requests.

IBM LE does not destroy any resources held by a COBOL sub-program if the sub-program returns control to its caller via the GOBACK construct, unless the sub-program is placed into initial state via the INITIAL keyword of the PROGRAM ID. paragraph.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Jun 12, 2008 11:42 pm
Reply with quote

Quote:
respectfully disagree.

You' d better change Your mind,

when program A terminates, control will return to db2..
I.E to the RUN command/subcommand which will carry on all the termination houskeeping, closing cursor, committing all the changes
Back to top
View user's profile Send private message
anv2005

New User


Joined: 14 Jul 2005
Posts: 44
Location: US

PostPosted: Thu Jun 12, 2008 11:49 pm
Reply with quote

Oops, I missed the part that debojyoti wrote that program A terminates and not the program B.
Back to top
View user's profile Send private message
himanshupant

New User


Joined: 21 Mar 2007
Posts: 46
Location: India

PostPosted: Tue Dec 01, 2009 10:11 pm
Reply with quote

Assuming in the same example A calls B. B is retrieving rows from a DB2 table using WITH HOLD option and passes back the rows in say sets of 10 rows of data. Now if the result set of the SQL in B has more than 10 rows, an indicator is passed back to A along with the 10 blocks which would tell A that more rows have to be returned and B needs to be called again.
A would then again call B. Will the cursor retrieve records from the 11 record onwards ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Dec 01, 2009 10:39 pm
Reply with quote

if you mean row 11 onwards, yes.
Back to top
View user's profile Send private message
himanshupant

New User


Joined: 21 Mar 2007
Posts: 46
Location: India

PostPosted: Tue Dec 01, 2009 10:41 pm
Reply with quote

dbzTHEdinosauer wrote:
if you mean row 11 onwards, yes.


Yes I meant 11 row onwards only... Would this in any way depend on the way program B is called dynamically / statically ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Dec 01, 2009 10:45 pm
Reply with quote

no -
unless,
the program id keyword initial is used in program b
and program b has a routine to open the cursor,
then you will get an sql error trying to open an open cursor.
Back to top
View user's profile Send private message
himanshupant

New User


Joined: 21 Mar 2007
Posts: 46
Location: India

PostPosted: Tue Dec 01, 2009 10:49 pm
Reply with quote

dbzTHEdinosauer wrote:
no -
unless,
the program id keyword initial is used in program b
and program b has a routine to open the cursor,
then you will get an sql error trying to open an open cursor.



The program id doesnt have keyword INITIAL for B

Program B has a routine to open the cursor but this would only be executed when the cursor is opened for the first time using application logic. In other words if the flag is set denoting that this is not the first call to B for that particular set of predicates , then the cursor would not be opened.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Dec 01, 2009 11:15 pm
Reply with quote

12.gif 12.gif 12.gif
you said the same thing that i did.

i wanted to qualifiy the problem with the INITIAL keyword.
were it used, the problem would not be with db2, it would be application logic:
on the second invocation your lil'flag would indicate that the cursor is not open.
Back to top
View user's profile Send private message
himanshupant

New User


Joined: 21 Mar 2007
Posts: 46
Location: India

PostPosted: Tue Dec 01, 2009 11:29 pm
Reply with quote

dbzTHEdinosauer wrote:
12.gif 12.gif 12.gif
you said the same thing that i did.

i wanted to qualifiy the problem with the INITIAL keyword.
were it used, the problem would not be with db2, it would be application logic:
on the second invocation your lil'flag would indicate that the cursor is not open.


I am sorry for any confusion in my note.what I meant was that I am not using INITIAL keyword and am using GOBACK in program B to return to program A after each call.so after first call the flow would GOBACK to A.. Now since the code knows that more rows have to be returned,B wud be again called.now on this CALl of B,the cursor would not be opened.So now the FETCH should return from 11row onwards.I believe this is what would happen and this is what you are saying.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Dec 01, 2009 11:49 pm
Reply with quote

yes, i did
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Dec 01, 2009 11:51 pm
Reply with quote

topic has been ...
Code:
Resurrected after ...seconds    46390920
Resurrected after ...minutes      773182
Resurrected after ...hours         12886
Resurrected after ...days            536
Resurrected after ...months           18
Resurrected after ...years 1           1 ( years difference )
Resurrected after ...years 2           1 ( months difference / 12 )
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Dec 02, 2009 12:05 am
Reply with quote

thx for the update, enrico
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
No new posts Restart logic by using cursor name in... DB2 1
No new posts Seeking Resolution for SQKCODE -991 o... DB2 2
No new posts Multiple rows within Cursor when Coun... DB2 14
No new posts Dynamic cursor name in Cobol program COBOL Programming 1
Search our Forums:

Back to Top