Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Handling result sets in the calling Native procedure

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

New User


Joined: 04 Feb 2010
Posts: 25
Location: United States

PostPosted: Fri Apr 13, 2018 12:25 am    Post subject: Handling result sets in the calling Native procedure
Reply with quote

Hello Team,
I need your inputs on following scenario. i tried multiple things but no luck.
Here is challenge,
I am working on DB2 Native procedure. I created procedure (SP1), and after performing some logic it is returning result into one cursor say CURSOR1. This CURSOR1 is extracting data from GLOBAL TEMPORARY TABLE which is declared in SP1. CURSOR1 is defined WITH RETURN TO CLIENT FOR clause.
Second Stored procedure SP2 is calling SP1, in order to read CURSOR1 (output of SP1) and process it. I tried using ASSOCIATE RESULT SET LOCATOR, however after executing SP2, i see in no result. It is giving error as,
"CURSOR NAME CURSOR1 IS NOT DECLARED".

Here is code which i did in SP2 to process CURSOR1:
ASSOCIATE RESULT SET LOCATOR(result1)
WITH PROCEDURE SCHEMA.SP1
ALLOCATE CURSOR1 CURSOR FOR RESULT SET result1

-- after this I am fetching CURSOR1 and processing it.

========
Note: In SP2 i defined result1 as,
DECLARE result1 RESULT_SET_LOCATOR VARYING;
========
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2002
Location: UK

PostPosted: Fri Apr 13, 2018 1:27 am    Post subject:
Reply with quote

Please use the code tags when presenting code and data. After so many years as a member you should be familiar with this.
Back to top
View user's profile Send private message
Rusikesh

New User


Joined: 04 Feb 2010
Posts: 25
Location: United States

PostPosted: Fri Apr 13, 2018 4:35 am    Post subject: Reply to: Handling result sets in the calling Native procedure
Reply with quote

Here is code snippet,
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8657
Location: Back in jolly old England

PostPosted: Fri Apr 13, 2018 11:41 am    Post subject:
Reply with quote

Nic Clouston wrote:
Please use the code tags when presenting code and data. After so many years as a member you should be familiar with this.

Rusikesh wrote:
Here is code snippet,
[attachment here]


Spot the difference ............. REQUESTED NE SUPPLIED

Please DO NOT post attachments as
a) Not everyone is permitted to open them
b) Not everyone will trust you that your attachment is safe, I FOR ONE DON'T
c) If you cannot be bothered to post in an easy and open fashion some people may not have enough "bothered" to open your attachment

So tip of the day, help yourself to help yourself.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Apr 13, 2018 8:23 pm    Post subject:
Reply with quote

Quote:
ALLOCATE CURSOR1 CURSOR FOR RESULT SET result1

You have got the same cursor name in both sps.
However, why don't you INSERT into temp table in SP1 from CURSOR1 and then reference that into SP2 to do further processing?
Back to top
View user's profile Send private message
Rusikesh

New User


Joined: 04 Feb 2010
Posts: 25
Location: United States

PostPosted: Fri Apr 13, 2018 9:40 pm    Post subject: Reply to: Handling result sets in the calling Native procedure
Reply with quote

Rohit, I result from SP1 is named as CURSOR1 and for locator i am using it as result1. i do not think so. i am using same name in both procedures.


@Expat, i understand your concern but i do not know how to place image in message body. so i have to go with attachment.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Sat Apr 14, 2018 12:10 am    Post subject:
Reply with quote

Quote:
Rohit, I result from SP1 is named as CURSOR1 and for locator i am using it as result1. i do not think so. i am using same name in both procedures.

You are not paying attention here. See if wither one is the problem,
1.ALLOCATE CURSOR1 CURSOR FOR RESULT SET result1
CURSOR1 name in sp2 is same as sp1 try with below declaration,
ALLOCATE CURSOR2 CURSOR FOR RESULT SET result1
ALLOCATE CURSOR
2. You are not calling the SP1 before executing the above code.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2002
Location: UK

PostPosted: Sat Apr 14, 2018 1:11 pm    Post subject:
Reply with quote

Quote:
but i do not know how to place image in message body

Cut and paste. Simple.
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 Get the list of data sets on DASD and... rakaitn JCL & VSAM 3 Thu Mar 08, 2018 12:38 pm
No new posts CICS called and calling program rizwanaccy CICS 1 Thu Feb 01, 2018 4:34 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Calling IKJEFT01 program from Rexx upendrasri CLIST & REXX 8 Wed Nov 15, 2017 4:27 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us