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

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