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 SP - Return a returned resultset from called SP

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

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Wed Oct 28, 2015 1:45 am    Post subject: DB2 SP - Return a returned resultset from called SP
Reply with quote

Hi,

I have a DB2 cobol stored procedure SP1 calling(sql call) to the stored procedure SP2.

SP2 returns a resultset. Now, SP1 can use ASSOCIATE LOCATOR and ALLOCATE CURSOR to access the resultset, fetch the data, insert into a temporary table and open a returnable cursor on the temporary table.

SP1 has its own resultset to return along with the data retrieved from SP2 as another resultset.

I am looking for a way to define the cursor as returnable during the ALLOCATE CURSOR statement or some other way to avoid the temporary table.

Further nesting upwards would mean each SP creating its own set of temporary tables and inserting data from the retrieved resultsets.

The reason why SP2 is returning a resultset is because this may be invoked by distributed side as a standalone SP.

Any suggestions would be helpful.

-Thanks,
Rakesh.
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


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

PostPosted: Wed Oct 28, 2015 2:48 am    Post subject:
Reply with quote

Quote:
The reason why SP2 is returning a resultset is because this may be invoked by distributed side as a standalone SP.


Are you not sure? you used the word "may be".

However. I would add one more parm to SP2 which will tell how SP2 has been called (say LK-CALL-FROM and values would be 'M' or 'D' ) . Now you code your SP2 accordingly based on this value. If it is 'M' then simply insert into temp table and open a cursor on it , no need to do all that locate business in SP1 (or any caller) as you described . and if it is 'D' then just open the same cursor or whatever and no temp table logic.

M-Mainframes (SP1 or any other)
D- Distributed
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Wed Oct 28, 2015 2:39 pm    Post subject:
Reply with quote

Thanks for your response Rohit!

Are you trying to imply tht the cursor opened in SP2 will be returned as a resultset in SP1 if we let it stay open and not do the ASSOCIATE and ALLOCATE?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Wed Oct 28, 2015 9:38 pm    Post subject:
Reply with quote

yes, Right.
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 How does a called pgm know if its cal... Graeme Westerman COBOL Programming 4 Tue Nov 29, 2016 9:25 pm
No new posts DFHWS2LS return value divated CICS 4 Mon Nov 07, 2016 5:44 pm
No new posts Cobol list of programs being called biswajit.dattagupta COBOL Programming 5 Tue Nov 01, 2016 2:10 am
This topic is locked: you cannot edit posts or make replies. How to supply called sub-program from... dchristensen JCL & VSAM 25 Wed Sep 14, 2016 11:36 pm
No new posts How to get the program name of callin... pkmurali COBOL Programming 8 Mon Aug 01, 2016 3:36 am


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