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 WITH HOLD cursor

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 WITH HOLD cursor
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

Site Director


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

PostPosted: Thu Jun 12, 2008 6:26 am    Post subject:
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Jun 12, 2008 11:42 pm    Post subject: Reply to: DB2 WITH HOLD cursor
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    Post subject:
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    Post subject: Reply to: DB2 WITH HOLD cursor
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
Reply with quote

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

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Dec 01, 2009 11:51 pm    Post subject: Reply to: DB2 WITH HOLD cursor
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    Post subject:
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    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 How LOAD PROGRAM and LOAD on HOLD is ... pkmurali CICS 4 Tue May 03, 2016 6:26 pm
No new posts How to hold a daily job for a monthly... anandgbe CA Products 8 Mon Mar 07, 2016 12:59 pm
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm


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