× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Pretty sure that won't work...

Charles

On Thu, Jul 16, 2020 at 1:36 PM Peter Dow <petercdow@xxxxxxxxx> wrote:

Thanks Charles. That sounds simpler than the federated system. If the
UDTF is an RPGLE program reading from a DDM file, maybe I wouldn't need
to copy to a local table.


On 7/16/2020 7:00 AM, Charles Wilt wrote:
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


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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.