ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Returning multiple resultset from DB2 Stored Procedure

Post new topic  Reply to topic
 Returning multiple resultset from DB2 Stored Procedure « View previous topic :: View next topic » 
Author Message
Partha.Baidya
PostPosted: Tue Aug 07, 2018 8:37 am    Post subject: Returning multiple resultset from DB2 Stored Procedure Reply with quote

Voyager

Joined: 05 Nov 2009
Posts: 97

We are trying to invoke a DB2 z/OS stored procedure(SP) using ESQL. The SP will return multiple result set.
I have followed in documentation in IIB v10 knowledge center.
https://www.ibm.com/support/knowledgecenter/en/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ac17040_.htm

This documentation gives example of 2 result sets but I do not find any information on ESQL coding if there are more than 2 result sets.

Is it possible to retrieve multiple resultsets using IIB/ESQL or only 2 at this moment?
Back to top
View user's profile Send private message
abhi_thri
PostPosted: Tue Aug 07, 2018 11:20 pm    Post subject: Reply with quote

Knight

Joined: 17 Jul 2017
Posts: 516
Location: UK

hi...as the 'The following restrictions apply to the use of stored procedures' section at that infocenter link doesn't call out any such limits I guess it should work fine.

Have you tried invoking it? Are you getting any errors? If you have access to any DB you could try creating a dummy stored proc and invoke it.
Back to top
View user's profile Send private message
mgk
PostPosted: Wed Aug 08, 2018 1:49 am    Post subject: Reply with quote

Padawan

Joined: 31 Jul 2003
Posts: 1638

You can return any number of result sets.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.
Back to top
View user's profile Send private message
Partha.Baidya
PostPosted: Wed Aug 08, 2018 7:33 am    Post subject: Reply with quote

Voyager

Joined: 05 Nov 2009
Posts: 97

Quote:
Have you tried invoking it? Are you getting any errors? If you have access to any DB you could try creating a dummy stored proc and invoke it.
I have tried for 3 resultset using the following syntax.

call statement
Code:
CALL DBSP224 (claimnum,  sqlreturncode, Environment.ResultSet1[], Environment.ResultSet2[], Environment.ResultSet3[]) IN Database.DBS4;

SP Delcaration
Code:

   CREATE PROCEDURE DBSP224 (IN CLAIM_NUM INT, OUT SQL_CODE INT)
     LANGUAGE DATABASE
    DYNAMIC RESULT SETS 3
     EXTERNAL NAME "DBSP224";

As you can see I need to provide no of result sets returning by SP in the ESQL SP declaration as well as providing the result set array while calling the SP.
I tried to put 'N' in order to receive any no of result sets, but ESQL syntax check throws an error if I mention N over there.
Code:

   CREATE PROCEDURE NFOSP224 (IN CLAIM_NUM INT, OUT SQL_CODE INT)
     LANGUAGE DATABASE
    DYNAMIC RESULT SETS [b]N[/b]
     EXTERNAL NAME "DBSP224";

I could not find the syntax for accepting 'N' no of result sets in ESQL library.
Could anybody has already done this? Or where can I find the correct ESQL syntax of receiving multiple result sets out of DB stored proc?
Back to top
View user's profile Send private message
mpong
PostPosted: Wed Aug 08, 2018 11:54 am    Post subject: Reply with quote

Disciple

Joined: 22 Jan 2010
Posts: 164

https://www.ibm.com/support/knowledgecenter/en/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04970_.html

look at Database routine example 7. May be helpful
Back to top
View user's profile Send private message
Partha.Baidya
PostPosted: Wed Aug 08, 2018 2:26 pm    Post subject: Reply with quote

Voyager

Joined: 05 Nov 2009
Posts: 97

Quote:
look at Database routine example 7. May be helpful

I have checked this example and able to fetch 3 to 5 result sets, it works fine.

But I want to know the syntax where there will multiple no of result sets which I do not know during code development.
Back to top
View user's profile Send private message
timber
PostPosted: Thu Aug 09, 2018 1:51 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

You are not asking about returning multiple result sets. You are asking about how to return an unknown number of result sets. The IIB knowledge center is 100% clear:
https://www.ibm.com/support/knowledgecenter/en/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ak04970_.htm:
Quote:
The DYNAMIC RESULT SETS clause is allowed only for database routines. It is required only if a stored procedure returns one or more result sets. The integer parameter to this clause must be 0 (zero) or more, and specifies the number of result sets to be returned."

The PASSTHRU statement might be one way around this restriction.
Back to top
View user's profile Send private message
mgk
PostPosted: Thu Aug 09, 2018 4:28 am    Post subject: Reply with quote

Padawan

Joined: 31 Jul 2003
Posts: 1638

Quote:
The PASSTHRU statement might be one way around this restriction.


PASSTHRU will not help here as this is restricted to only returning one result set.

However, as long as you know the upper limit to the number of result sets being returned you should be able to manage. Just set the DYNAMIC RESULT SETS to the maximum to be returned (e.g. 12) and provide the same number of result set destinations on the CALL. Now as long as the procedure returns <=12 result sets (in this example) you will be OK - the CALL statement will automatically set unused results set parameters to empty - this is expected behaviour. For example if (as in this example), you returned 8 result sets you would have that last 4 result locations on the call set to empty results. If you need to know how many result sets were actually returned, you can pass this back from the procedure as a RETURN value or as an OUT or INOUT parameter.

I hope this helps.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.


Last edited by mgk on Fri Aug 10, 2018 2:22 am; edited 2 times in total
Back to top
View user's profile Send private message
Partha.Baidya
PostPosted: Thu Aug 09, 2018 1:47 pm    Post subject: Reply with quote

Voyager

Joined: 05 Nov 2009
Posts: 97

Thanks mgk, I think the solution which you proposed is going to work.
Quote:

You are not asking about returning multiple result sets. You are asking about how to return an unknown number of result sets. The IIB knowledge center is 100% clear:

Hi timber, you are correct I want to know how to return unknown number of result sets.

Is there any coding option in ESQL to achieve returning unknown number of result sets?
I think the coding example given in infocentre not going to support this.
Back to top
View user's profile Send private message
timber
PostPosted: Fri Aug 10, 2018 1:50 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Quote:
Is there any coding option in ESQL to achieve returning unknown number of result sets?
No. The approach outlined by mgk is the only option.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Returning multiple resultset from DB2 Stored Procedure
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.