Passing result set through nested stored procedure

Sanjay (Exchange) Jain

Passing result set through nested stored procedure
Hello all,

I have searched though the archive, but can't quite find the answer. So
here it goes :-

DB2 ver 7 on Z/OS

Is it possible to pass a result set back to a client in a nested stored
procedure call? E.g.

1. Client program calls SP1
2. SP1 calls SP2
3. SP2 returns a result set
4. SP1 opens another cursor and returns to client
5. Client program should get both result sets

I understand in UDB, RETURN TO CLIENT clause on the cursor declaration
allows a cursor to be returned back to the client. I do not see similar
option in mainframe DB2.

Has anyone done something similar?

Any pointers are greatly appreciated.


Sanjay Jain




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Agus Kwee

Re: Passing result set through nested stored procedure
(in response to Sanjay (Exchange) Jain)
Sanjay,

Based on my test under our DB2 z/OS V7, the client did not receive the
result set returned by SP2.
You can try it in your DB2 z/OS V7.
You are correct that the DECLARE CURSOR in DB2 z/OS has only
WITH RETURN option.

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com

----- Original Message -----
From: "Jain, Sanjay (Exchange)"
Date: Thursday, January 11, 2007 10:18 pm
Subject: [DB2-L] Passing result set through nested stored procedure
To: [login to unmask email]

> Hello all,
>
> I have searched though the archive, but can't quite find the
> answer. So
> here it goes :-
>
> DB2 ver 7 on Z/OS
>
> Is it possible to pass a result set back to a client in a nested
> storedprocedure call? E.g.
>
> 1. Client program calls SP1
> 2. SP1 calls SP2
> 3. SP2 returns a result set
> 4. SP1 opens another cursor and returns to client
> 5. Client program should get both result sets
>
> I understand in UDB, RETURN TO CLIENT clause on the cursor declaration
> allows a cursor to be returned back to the client. I do not see
> similaroption in mainframe DB2.
>
> Has anyone done something similar?
>
> Any pointers are greatly appreciated.
>
>
> Sanjay Jain
>
>
>
>
> -----------------------------------------------------------------
> ----------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the
> archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG
> DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins
> can be reached at [login to unmask email] Find out the
> latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Passing result set through nested stored procedure
(in response to Agus Kwee)
Sanjay,

The only way to make results from SP2 available to the client is for SP1 to
fetch all rows, insert them to a CTT/DTT and then open a cursor (again!)
from which the client can fetch. Convoluted? You bet!

This is discussed in chap 10 of the SP redbook - SG24-7083.

Contact me offline if you want to discuss further.

Thx
Suresh


>From: "Jain, Sanjay (Exchange)" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: [DB2-L] Passing result set through nested stored procedure
>Date: Thu, 11 Jan 2007 22:07:25 -0500
>
>Hello all,
>
>I have searched though the archive, but can't quite find the answer. So
>here it goes :-
>
>DB2 ver 7 on Z/OS
>
>Is it possible to pass a result set back to a client in a nested stored
>procedure call? E.g.
>
>1. Client program calls SP1
>2. SP1 calls SP2
>3. SP2 returns a result set
>4. SP1 opens another cursor and returns to client
>5. Client program should get both result sets
>
>I understand in UDB, RETURN TO CLIENT clause on the cursor declaration
>allows a cursor to be returned back to the client. I do not see similar
>option in mainframe DB2.
>
>Has anyone done something similar?
>
>Any pointers are greatly appreciated.
>
>
>Sanjay Jain
>
>
>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm


>
>
>***********************************************************************
>Bear Stearns is not responsible for any recommendation, solicitation,
>offer or agreement or any information about any transaction, customer
>account or account activity contained in this communication.
>***********************************************************************
>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

_________________________________________________________________
Get live scores and news about your team: Add the Live.com Football Page
www.live.com/?addtemplate=football&icid=T001MSN30A0701

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sanjay (Exchange) Jain

Re: Passing result set through nested stored procedure
(in response to Suresh Sane)
Suresh, We do not want to go this route.

Agus, Our experience is the same.

Regards

Sanjay Jain



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Suresh Sane
Sent: Friday, January 12, 2007 12:23 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Passing result set through nested stored procedure

Sanjay,

The only way to make results from SP2 available to the client is for SP1
to fetch all rows, insert them to a CTT/DTT and then open a cursor
(again!) from which the client can fetch. Convoluted? You bet!

This is discussed in chap 10 of the SP redbook - SG24-7083.

Contact me offline if you want to discuss further.

Thx
Suresh


>From: "Jain, Sanjay (Exchange)" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG
><[login to unmask email]>
>To: [login to unmask email]
>Subject: [DB2-L] Passing result set through nested stored procedure
>Date: Thu, 11 Jan 2007 22:07:25 -0500
>
>Hello all,
>
>I have searched though the archive, but can't quite find the answer. So

>here it goes :-
>
>DB2 ver 7 on Z/OS
>
>Is it possible to pass a result set back to a client in a nested stored

>procedure call? E.g.
>
>1. Client program calls SP1
>2. SP1 calls SP2
>3. SP2 returns a result set
>4. SP1 opens another cursor and returns to client
>5. Client program should get both result sets
>
>I understand in UDB, RETURN TO CLIENT clause on the cursor declaration
>allows a cursor to be returned back to the client. I do not see similar

>option in mainframe DB2.
>
>Has anyone done something similar?
>
>Any pointers are greatly appreciated.
>
>
>Sanjay Jain
>
>
>
>
>-----------------------------------------------------------------------
>---------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
>archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.

>From that page select "Join or Leave the list". The IDUG DB2-L FAQ is
>at http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
>conferences at http://conferences.idug.org/index.cfm


>
>
>***********************************************************************
>Bear Stearns is not responsible for any recommendation, solicitation,
>offer or agreement or any information about any transaction, customer
>account or account activity contained in this communication.
>***********************************************************************
>
>
>-----------------------------------------------------------------------
>---------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
>archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.

>From that page select "Join or Leave the list". The IDUG DB2-L FAQ is
>at http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG
>conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Get live scores and news about your team: Add the Live.com Football Page
www.live.com/?addtemplate=football&icid=T001MSN30A0701

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
***********************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: Passing result set through nested stored procedure
(in response to Sanjay (Exchange) Jain)
Wouldn't it be simpler for SP2 to insert the rows into the CTT/DTT? The
end client then has to open a cursor on the TT and fetch the data.

This way SP1 isn't involved in manipulating the data - which I guess is what
Sanjay wants to avoid.

James Campbell

On 12 Jan 2007 at 11:22, Suresh Sane wrote:

> Sanjay,
>
> The only way to make results from SP2 available to the client is for SP1 to
> fetch all rows, insert them to a CTT/DTT and then open a cursor (again!)
> from which the client can fetch. Convoluted? You bet!
>
> This is discussed in chap 10 of the SP redbook - SG24-7083.
>
> Contact me offline if you want to discuss further.
>
> Thx
> Suresh
>
>
> >From: "Jain, Sanjay (Exchange)" <[login to unmask email]>
> >Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
> >To: [login to unmask email]
> >Subject: [DB2-L] Passing result set through nested stored procedure
> >Date: Thu, 11 Jan 2007 22:07:25 -0500
> >
> >Hello all,
> >
> >I have searched though the archive, but can't quite find the answer. So
> >here it goes :-
> >
> >DB2 ver 7 on Z/OS
> >
> >Is it possible to pass a result set back to a client in a nested stored
> >procedure call? E.g.
> >
> >1. Client program calls SP1
> >2. SP1 calls SP2
> >3. SP2 returns a result set
> >4. SP1 opens another cursor and returns to client
> >5. Client program should get both result sets
> >
> >I understand in UDB, RETURN TO CLIENT clause on the cursor declaration
> >allows a cursor to be returned back to the client. I do not see similar
> >option in mainframe DB2.
> >
> >Has anyone done something similar?
> >
> >Any pointers are greatly appreciated.
> >
> >
> >Sanjay Jain
> >
> >
> >
> >
> >---------------------------------------------------------------------------------
> >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> >page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> >"Join or Leave the list". The IDUG DB2-L FAQ is at
> >http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> >[login to unmask email] Find out the latest on IDUG conferences at
> >http://conferences.idug.org/index.cfm
>
>
> >
> >
> >***********************************************************************
> >Bear Stearns is not responsible for any recommendation, solicitation,
> >offer or agreement or any information about any transaction, customer
> >account or account activity contained in this communication.
> >***********************************************************************
> >
> >
> >---------------------------------------------------------------------------------
> >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> >page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> >"Join or Leave the list". The IDUG DB2-L FAQ is at
> >http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> >[login to unmask email] Find out the latest on IDUG conferences at
> >http://conferences.idug.org/index.cfm
>
> _________________________________________________________________
> Get live scores and news about your team: Add the Live.com Football Page
> www.live.com/?addtemplate=football&icid=T001MSN30A0701
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Passing result set through nested stored procedure
(in response to James Campbell)
James,

You are correct in that this would be simpler.

It does mean the client has a connection to the server (other than a CALL),
which may not be desirable. Some front-ends will process the FETCH from
aresult set easily, but the logic to code a cursor as you suggest would need
to be custom-built.

So, the suggested (convoluted) way is more general, but may not be the best.

Thanks for pointing it out.

Thx
Suresh


>From: James Campbell <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: [DB2-L] Passing result set through nested stored procedure
>Date: Sat, 13 Jan 2007 14:25:08 +1100
>
>Wouldn't it be simpler for SP2 to insert the rows into the CTT/DTT? The
>end client then has to open a cursor on the TT and fetch the data.
>
>This way SP1 isn't involved in manipulating the data - which I guess is
>what
>Sanjay wants to avoid.
>
>James Campbell
>
>On 12 Jan 2007 at 11:22, Suresh Sane wrote:
>
> > Sanjay,
> >
> > The only way to make results from SP2 available to the client is for SP1
>to
> > fetch all rows, insert them to a CTT/DTT and then open a cursor (again!)
> > from which the client can fetch. Convoluted? You bet!
> >
> > This is discussed in chap 10 of the SP redbook - SG24-7083.
> >
> > Contact me offline if you want to discuss further.
> >
> > Thx
> > Suresh
> >
> >
> > >From: "Jain, Sanjay (Exchange)" <[login to unmask email]>
> > >Reply-To: DB2 Database Discussion list at IDUG
><[login to unmask email]>
> > >To: [login to unmask email]
> > >Subject: [DB2-L] Passing result set through nested stored procedure
> > >Date: Thu, 11 Jan 2007 22:07:25 -0500
> > >
> > >Hello all,
> > >
> > >I have searched though the archive, but can't quite find the answer. So
> > >here it goes :-
> > >
> > >DB2 ver 7 on Z/OS
> > >
> > >Is it possible to pass a result set back to a client in a nested stored
> > >procedure call? E.g.
> > >
> > >1. Client program calls SP1
> > >2. SP1 calls SP2
> > >3. SP2 returns a result set
> > >4. SP1 opens another cursor and returns to client
> > >5. Client program should get both result sets
> > >
> > >I understand in UDB, RETURN TO CLIENT clause on the cursor declaration
> > >allows a cursor to be returned back to the client. I do not see similar
> > >option in mainframe DB2.
> > >
> > >Has anyone done something similar?
> > >
> > >Any pointers are greatly appreciated.
> > >
> > >
> > >Sanjay Jain
> > >
> > >
> > >
> > >
> >
> >---------------------------------------------------------------------------------
> > >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home
> > >page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
>select
> > >"Join or Leave the list". The IDUG DB2-L FAQ is at
> > >http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> > >[login to unmask email] Find out the latest on IDUG
>conferences at
> > >http://conferences.idug.org/index.cfm
> >
> >
> > >
> > >
> > >***********************************************************************
> > >Bear Stearns is not responsible for any recommendation, solicitation,
> > >offer or agreement or any information about any transaction, customer
> > >account or account activity contained in this communication.
> > >***********************************************************************
> > >
> > >
> >
> >---------------------------------------------------------------------------------
> > >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home
> > >page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
>select
> > >"Join or Leave the list". The IDUG DB2-L FAQ is at
> > >http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> > >[login to unmask email] Find out the latest on IDUG
>conferences at
> > >http://conferences.idug.org/index.cfm
> >
> > _________________________________________________________________
> > Get live scores and news about your team: Add the Live.com Football Page
> > www.live.com/?addtemplate=football&icid=T001MSN30A0701
> >
> >
>---------------------------------------------------------------------------------
> > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
>select "Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

_________________________________________________________________
Find sales, coupons, and free shipping, all in one place!  MSN Shopping
Sales & Deals
http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm