Remote package usage

Hans-Joachim Mai

Remote package usage
We have an existing application running on DB2A (location locdb2a)
Another existing application on DB2B (locdb2b) now wants to
access our data by using one of our programs. (Both on z/OS)
Actually, it has been made work the following way:
Aliases on DB2B for our tables (with the needed grants on our side)
locally bound our package into DB2B and bound this into their plan.

I wonder if their ain't an easier solution.
What first came into my mind was:
Just bind our package locdb2a.<our collection>.*
into the DB2B plan with an additional execute grant on our side.

Is this possible, or am I missing something?

What other solutions will work that don't need those aliases and
don't require our programs to be changed?

Thanks in advance,

Achim

--
Hans-Joachim Mai mailto:[login to unmask email]
<mailto:[login to unmask email]>
sd&m AG http://www.sdm.de
< http://www.sdm.de >
software design & management
Luebecker Strasse 1, 22087 Hamburg, Germany
Tel +49 40 254491-29 Fax -10



Chris Pomasl

Re: Remote package usage
(in response to Hans-Joachim Mai)
Mai, Hans-Joachim wrote:

>We have an existing application running on DB2A (location locdb2a)
>Another existing application on DB2B (locdb2b) now wants to
>access our data by using one of our programs. (Both on z/OS)
>Actually, it has been made work the following way:
>Aliases on DB2B for our tables (with the needed grants on our side)
>locally bound our package into DB2B and bound this into their plan.
>
>I wonder if their ain't an easier solution.
>What first came into my mind was:
>Just bind our package locdb2a.<our collection>.*
>into the DB2B plan with an additional execute grant on our side.
>
>Is this possible, or am I missing something?
>
>What other solutions will work that don't need those aliases and
>don't require our programs to be changed?
>
I just did this sort of research for a customer.
If you rebind your packages using "location.collection.pgmname"
And then include in your plan bind the PKLISTS "location.collection.*"
And set the CURRENTSERVER in the bind plan to the location also.

This will use your local subsystem as a conduit to the remote one and
all SQL will be shipped to the remote system and be run there as though
it were local. All tables and access will be done on the remote system.
I even tried running a query that indicated a location of the original
subsystem (round trip) but this was not entirely successful. Sometimes
it worked ok and sometimes not.

If this is the kind of transparency you want, then this might be the way
to go.
You can always set up one plan for local access and one plan for the
remote access described above.


--
Christopher J. Pomasl
Senior Software Engineer - DB2 products
Computer Associates, inc

Always remember, you are unique.....just like everyone else!!



Hans-Joachim Mai

AW: Remote package usage
(in response to Chris Pomasl)
Well, this will not fully solve our problem.
Two things seem clear to me:
If I have pure access to a remote location,
the currentserver option of the bind will work.
If I have the same table locally and remotely,
I have to have a local and a remote package bound into
my local plan, and the need to set the current server or
package set to tell the db which one to use.
The one that's not clear to me is the following:
The tables and packages on DB2A and DB2B are completly disjoint.
The app at locdb2b does its work mainly on DB2B and has to do a check
(e.g. of existance) on DB2A which has to happen in the same transaction.
Now assume the plan on DB2B would be
pklist(collb.*, locdb2a.colla.pack_a)
with collb being a local collection on locdb2b
and locdb2a.colla.pack_a being the original package of the app at locdb2a.
Will the app/DB2B be able to use pack_a (no corresponding pack_a on DB2B!)
without prior setting of the current server or package set.

Many thanks,

Achim

Mai, Hans-Joachim wrote:

>We have an existing application running on DB2A (location locdb2a)
>Another existing application on DB2B (locdb2b) now wants to access our
>data by using one of our programs. (Both on z/OS) Actually, it has been
>made work the following way: Aliases on DB2B for our tables (with the
>needed grants on our side) locally bound our package into DB2B and
>bound this into their plan.
>
>I wonder if their ain't an easier solution.
>What first came into my mind was:
>Just bind our package locdb2a.<our collection>.*
>into the DB2B plan with an additional execute grant on our side.
>
>Is this possible, or am I missing something?
>
>What other solutions will work that don't need those aliases and don't
>require our programs to be changed?
>
I just did this sort of research for a customer.
If you rebind your packages using "location.collection.pgmname" And then
include in your plan bind the PKLISTS "location.collection.*" And set the
CURRENTSERVER in the bind plan to the location also.

This will use your local subsystem as a conduit to the remote one and all
SQL will be shipped to the remote system and be run there as though it were
local. All tables and access will be done on the remote system. I even
tried running a query that indicated a location of the original subsystem
(round trip) but this was not entirely successful. Sometimes it worked ok
and sometimes not.

If this is the kind of transparency you want, then this might be the way to
go. You can always set up one plan for local access and one plan for the
remote access described above.


--
Christopher J. Pomasl
Senior Software Engineer - DB2 products
Computer Associates, inc

Always remember, you are unique.....just like everyone else!!








Walter Jani&#223;en

Re: AW: Remote package usage
(in response to Hans-Joachim Mai)
Achim

Some years ago there came out the redbook 'DB2 Packages: Implentation and
Use' (my book is from Oct. 93), so I don't know if it's still right. In
that manual there is a chart showing the Package Search Process. If I
understand it correctly, then a package for a remote location will only be
used, if the current server register is set.

HTH