|
View previous topic :: View next topic
|
| Author |
Message |
rakesh1155
New User
Joined: 21 Jan 2009
Posts: 84
Location: India
|
|
|
|
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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
rakesh1155
New User
Joined: 21 Jan 2009
Posts: 84
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| yes, Right. |
|
| Back to top |
|
|
|
|
