Re: Three part naming error connecting to a particular lpar.

You can create a UDTF that runs a dynamic statement that selects data from
a remote system and inserts it into a local table..

then after all the data is available locally, you can return the data.

The UTDF can even be used in a local view.

now a SELECT * FROM MYVIEW, will cause the data to be retrieved and
presented to you.

Charles



On Wed, Jul 15, 2020 at 6:31 PM Peter Dow <petercdow@xxxxxxxxx> wrote:

Hi Rob,

I'm peripherally involved with a project where they would like to be
able to access files on multiple systems within a single SQL SELECT
statement, and when I first read this thread, it sounded like you were
doing that, but I'm not sure. Having tried to do it and failed, I
re-read your first post and it seems like you do one system at a time,
right?

I was reading

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/ddp/rbal1distributeddata.htm
where it says "Within one SQL statement, an application running against
a local database can direct SQL requests to one or more remote
databases. For example, a program can perform updates to table A on
database 1 and table B on database 2 within one SQL statement. The
following figure illustrates this idea."

But then I got past the fancy graphics and read "IBM® idistributed
relational database supports a subset of the distributed request
functionality. It allows update access or read-only access to a single
local or remote database in one SQL statement."

My question is, is there some additional software available from IBM or
other vendor(s) that allows "access to a single local or remote database
in one SQL statement."?

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx>

/
On 7/13/2020 8:30 AM, Rob Berendt wrote:

Here was the issue...
When you do three part naming 'interactively' like from Run SQL scripts

or whatever your preferred tool is it was working fine. However, when I
ran it from a stored procedure it's considered not dynamic SQL. When it's
not dynamic SQL the sql statement is bound to a package.

Why was this package a big deal when talking to particular lpars?

Because those lpars were part of high availability pairs. And with H/A
pairs you do some unique things with the remote database names. For
example, you may have PROD1 and PROD2 and to everyone else they are just
known as PROD. Both systems will have PROD has their *LOCAL in their
WRKRDBDIRE. Each system will also have an alias for PROD. PROD1 will have
an alias of PROD1 pointing to PROD. PROD2 will have an alias of PROD2
pointing to PROD. This is outlined in a few IBM technotes. One that comes
to mind is configuring BRMS in a high availability environment.

So the fix is to change the stored procedure to do a EXECUTE IMMEDIATE

to make it dynamic and forego the SQL package. I had issues with that
because EXECUTE IMMEDIATE with a variable was challenging. So I went raw,
stopped using three part naming and used CONNECT TO instead.

There used to be a big push for non dynamic SQL to enhance performance.

Now they are finding situations, like the above, where dynamic SQL is a
better choice. And some cases dynamic may perform better, but that's a
discussion for another day.

Rob Berendt

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com