Stored Procedure Calling Stored Procedure?

[login to unmask email]

Stored Procedure Calling Stored Procedure?





Hello,

Something in the back of my head makes me think that there is a restriction
that when a stored procedure calls another stored procedure the second
stored procedure must be local. However, I can't seem to confirm this.
Hopefully, somebody on the list can.

We are DB2 V7 for OS/390.

Thanks,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 356-5317, Tie Line 8-697-5317

-----------------------------------------------------
Happiness is not around the corner.
Happiness is the Contour (SVT) of the road.
-----------------------------------------------------
The early bird gets the worm,
but the second mouse gets the cheese.
-----------------------------------------------------
---------------------------------------------------------------------------------
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

Myron Miller

Re: Stored Procedure Calling Stored Procedure?
(in response to damcon2@US.IBM.COM)
We call SPs that are built by the Stored Procedure Builder all the time and all
of them are built the same way. None of them are specified any differently.
It just gets a bit tricky if one of the called procedures needs to pass data
back to the original person, but other than that, everything works the same.

Myron
--- Jackson Reavill <[login to unmask email]> wrote:
>
>
>
>
>
> Hello,
>
> Something in the back of my head makes me think that there is a restriction
> that when a stored procedure calls another stored procedure the second
> stored procedure must be local. However, I can't seem to confirm this.
> Hopefully, somebody on the list can.
>
> We are DB2 V7 for OS/390.
>
> Thanks,
> Jay
>
>
> Jay Reavill
> [login to unmask email]
> IBM Global Services
> Tampa, Florida
> Tel: (813) 356-5317, Tie Line 8-697-5317
>
> -----------------------------------------------------
> Happiness is not around the corner.
> Happiness is the Contour (SVT) of the road.
> -----------------------------------------------------
> The early bird gets the worm,
> but the second mouse gets the cheese.
> -----------------------------------------------------
>
---------------------------------------------------------------------------------
> 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
>


__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus

---------------------------------------------------------------------------------
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

Ava Collins

Re: Stored Procedure Calling Stored Procedure?
(in response to Myron Miller)
Jay wrote:
Something in the back of my head makes me think that there is a restriction
that when a stored procedure calls another stored procedure the second
stored procedure must be local. However, I can't seem to confirm this.
Jay,

The second stored procedure doesn't have to be local. In an application that
collected information from multiple lpars, a stored procedure did a CONNECT
and the called the remote SP.

The "commit coordinator" is the key to getting things to work properly! In
this application, the remote SP's COMMIT worked fine when called remotely from
the mainframe (one hop), but when the web based application via DB2 Connect
called a SP on LPAR A and that SP called a SP on LPAR B (double hop), the
COMMIT could not be executed in the remote SP. It would receive a -751.

Maybe this relates to the info in the back of your head.

Jacquie

---------------------------------------------------------------------------------
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

[login to unmask email]

Re: Stored Procedure Calling Stored Procedure?
(in response to Ava Collins)





Thank you Jacquie for finding the lost something in the back of my head.

Regards,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 356-5317, Tie Line 8-697-5317

-----------------------------------------------------
Happiness is not around the corner.
Happiness is the Contour (SVT) of the road.
-----------------------------------------------------
The early bird gets the worm,
but the second mouse gets the cheese.
-----------------------------------------------------




[login to unmask email]
M To: [login to unmask email]
Sent by: DB2 Data cc:
Base Discussion Subject: Re: Stored Procedure Calling Stored Procedure?
List
<[login to unmask email]
ORG>


01/12/2004 08:40
PM
Please respond to
DB2 Database
Discussion list
at IDUG






Jay wrote:
Something in the back of my head makes me think that there is a
restriction
that when a stored procedure calls another stored procedure the second
stored procedure must be local. However, I can't seem to confirm this.
Jay,

The second stored procedure doesn't have to be local. In an application
that collected information from multiple lpars, a stored procedure did a
CONNECT and the called the remote SP.

The "commit coordinator" is the key to getting things to work properly!
In this application, the remote SP's COMMIT worked fine when called
remotely from the mainframe (one hop), but when the web based application
via DB2 Connect called a SP on LPAR A and that SP called a SP on LPAR B
(double hop), the COMMIT could not be executed in the remote SP. It would
receive a -751.

Maybe this relates to the info in the back of your head.

Jacquie
---------------------------------------------------------------------------------
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