Is it possible to use an alias to LUW in a Db2 for z/OS SQL stored procedure?

Philip Nelson

Is it possible to use an alias to LUW in a Db2 for z/OS SQL stored procedure?
Folks,

I've got to write a stored procedure which processes data in a local Db2
for z/OS subsystem and also in a number of remote Db2 for LUW databases.

I've created aliases to the remote tables I need and can access them
through SPUFI.

I create a stored procedure which tries to read from the alias.

The SP deploys but when I try to run it I get a -805 (package missing) on
the remote server.

Is it actually possible to use an alias to a remote LUW table from a z/OS
native SQL stored procedure?

If so, how do I get the package I need out there?

Phil

Nadir Doctor

Is it possible to use an alias to LUW in a Db2 for z/OS SQL stored procedure?
(in response to Philip Nelson)
Hi Phil,

You may find federation to be helpful -
https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.qb.dbconn.doc/doc/c0010189.html


Nadir




On Wed, Jul 1, 2020 at 10:58 AM Philip Nelson <[login to unmask email]> wrote:

> Folks,
>
> I've got to write a stored procedure which processes data in a local Db2
> for z/OS subsystem and also in a number of remote Db2 for LUW databases.
>
> I've created aliases to the remote tables I need and can access them
> through SPUFI.
>
> I create a stored procedure which tries to read from the alias.
>
> The SP deploys but when I try to run it I get a -805 (package missing) on
> the remote server.
>
> Is it actually possible to use an alias to a remote LUW table from a z/OS
> native SQL stored procedure?
>
> If so, how do I get the package I need out there?
>
> Phil
>
> -----End Original Message-----
>

James Campbell

Is it possible to use an alias to LUW in a Db2 for z/OS SQL stored procedure?
(in response to Philip Nelson)
"> If so, how do I get the package I need out there?"

Did you try doing a remote bind copy?

On z/OS
BIND PACKAGE(<remote-luw-database>.<collection>) COPY(<local-collection>.<package>)
etc

"> I've created aliases to the remote tables I need and can access them
> through SPUFI.
"

You'll probably find that whoever installed / migrated Db2 took the DSNTISG job and bound
the SPUFI packages on the remote database.

James Campbell


On 1 Jul 2020 at 16:58, Philip Nelson wrote:

> Folks,
>
> I've got to write a stored procedure which processes data in a local Db2
> for z/OS subsystem and also in a number of remote Db2 for LUW databases.
>
> I've created aliases to the remote tables I need and can access them
> through SPUFI.
>
> I create a stored procedure which tries to read from the alias.
>
> The SP deploys but when I try to run it I get a -805 (package missing) on
> the remote server.
>
> Is it actually possible to use an alias to a remote LUW table from a z/OS
> native SQL stored procedure?
>
> If so, how do I get the package I need out there?
>
> Phil
>

--
This email has been checked for viruses by AVG.
https://www.avg.com

Philip Nelson

Is it possible to use an alias to LUW in a Db2 for z/OS SQL stored procedure?
(in response to James Campbell)
James / Nadir,

Thanks for your input.

It was me who bound SPUFI against the remote systems, so I know that
connectivity works.

I was trying to do a BIND COPY and wasn't having a lot of success. I
eventually found that if I created an alias on the remote (LUW) side with
the same name as the z/OS alias then I could get the bind copy to work, and
even a very simple SP to run.

But as soon as I try to do anything useful in the SP it starts to get a
series of errors, most recently "Mutually exclusive fields cannot both
contain non-null values: error code -4499". This seems to be some issue
with the JDBC driver which calls the SP (from Data Studio) - although the
PMR I found on this relates to DB2 --> DB2 code rather than DB2 --> LUW and
I have a JDBC driver newer than the one that fixed that problem.

Maybe I do need to cut my losses on this one and drive everything from the
LUW side. I've been doing federated stored procedures from LUW for a long
time, so I do know that works. But I was hoping to drive this from the
mainframe side.

I'm only looking at application code for this at all because of another
issue with LOAD from CURSOR over federation where the tables involved
include XML or LOBs. The amount of data being transferred is huge
compared to the size of the actual data - and a previous PMR I opened on
this shows that the XML / LOB is being expanded in an internal buffer to
the maximum allowable length before transmission. I couldn't get this
fixed, since I couldn't convince support that this wasn't working correctly
(I think because they'd have to go into the guts of the memory management
code to fix something that very few people use.

At the end of the day, I've got to consolidate about 4 TB of XMLs from 3
LUW databases into a single z/OS database and will have to find some way of
doing it (and as efficiently as possible).

Phil

On Thu, 2 Jul 2020 at 06:18, James Campbell <[login to unmask email]> wrote:

> "> If so, how do I get the package I need out there?"
>
> Did you try doing a remote bind copy?
>
> On z/OS
> BIND PACKAGE(<remote-luw-database>.<collection>)
> COPY(<local-collection>.<package>)
> etc
>
> "> I've created aliases to the remote tables I need and can access them
> > through SPUFI.
> "
>
> You'll probably find that whoever installed / migrated Db2 took the
> DSNTISG job and bound
> the SPUFI packages on the remote database.
>
> James Campbell
>
>
> On 1 Jul 2020 at 16:58, Philip Nelson wrote:
>
> > Folks,
> >
> > I've got to write a stored procedure which processes data in a local Db2
> > for z/OS subsystem and also in a number of remote Db2 for LUW databases.
> >
> > I've created aliases to the remote tables I need and can access them
> > through SPUFI.
> >
> > I create a stored procedure which tries to read from the alias.
> >
> > The SP deploys but when I try to run it I get a -805 (package missing) on
> > the remote server.
> >
> > Is it actually possible to use an alias to a remote LUW table from a z/OS
> > native SQL stored procedure?
> >
> > If so, how do I get the package I need out there?
> >
> > Phil
> >
>
> --
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> -----End Original Message-----
>
>

Geoff Davies

RE: Is it possible to use an alias to LUW in a Db2 for z/OS SQL stored procedure?
(in response to Philip Nelson)

Hi - I'm using 3 part names with z/OS Stored Procs that are driven by a 'control' table that lives on LUW to actually then call DSNTILU to load (cursor) directly from LUW tables into z/OS tables.  Works a treat - all run from Windows servers.

After creating SP on z/OS, my remote bind is like:

BIND PACKAGE (DB_RDSR.TESTF) -

COPY (TESTF.TDG_HI_DEL) COPYVER(V1) - 

OWNER(TESTF ) QUALIFIER(TESTF) SQLERROR(CONTINUE);
//*

Whereby DB_RDSR is the name of my LUW DB per sysibm.syslocations.

I also was very pleasantly surprised how well/easy DSNTILU worked in this situatiion.

regards

Jorg Lueke

RE: Is it possible to use an alias to LUW in a Db2 for z/OS SQL stored procedure?
(in response to Philip Nelson)

If you can it will still be worth asking if you should. One shop used the stored procedure calls to IMS to get around which IMS systems were upon which LPARS and the uniqueness of that process caused headaches for a good decade.

Db2 Dba, Sysprog, Architect

https://www.linkedin.com/in/jorg-lueke-8b391b4/