RE: Using STRSQL against a DDM file

An better alternative for the below scenario is to create a View on the
remote database with the selection criteria defined, and create a DDM
file over this View.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeffrey Carey
Sent: 23 June 2011 22:16
To: Midrange Systems Technical Discussion
Subject: RE: Using STRSQL against a DDM file

The basic problem that the OP mentioned still remains.

As mentioned, you could CPYF from a DDM and then SQL against that.

Alternatively (especially if that remote file is large), you could
connect to the remote database, CREATE TABLE <name> AS (SELECT <your
selection criteria>) WITH DATA

This will put the results of your query into a table, albeit still on
the remote system (even if you specify library QTEMP). Then you could
build a DDM over that remote file and CPYF to the local system (or use
other methods of shipping the file over).

The advantage here is that you may only care about a bit of that
original remote table for your local query (this month's records, for
instance). Also, it will use SQE instead of CQE for the SQL part (but
not the DDM part if you are going that route).

It would probably be very useful to connect to different tables in
different databases in one SQL statement on i - especially when you
start dealing with IASP, where each IASP is a different database. CAAC
or LUG requirement anyone?









Jeff Carey, MSIT, MBA


SXC Health Solutions, Inc.



jeffrey.carey@xxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark Murphy/STAR
BASE Consulting Inc.
Sent: Wednesday, June 22, 2011 09:30 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Using STRSQL against a DDM file

Kinda but not really.

SQL allows using multiple connections within a single unit of work, just
not distributed requests. Same as DDM. Use SET CONNECTION to switch
connections.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx

-----midrange-l-bounces@xxxxxxxxxxxx wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: Charles Wilt
Sent by: midrange-l-bounces@xxxxxxxxxxxx
Date: 06/21/2011 10:01AM
Subject: Re: Using STRSQL against a DDM file

Nope you're right, you can only connect to one DB at a time...

It's a limitation of DB2 for i which no other RDBMS including DB2 for
LUW has...


Charles

On Tue, Jun 21, 2011 at 9:35 AM, Alan Shore <ashore@xxxxxxxx> wrote:

Thanks for your reply Vern
What you are saying is in fact what I am finding out
The only problem (unless I am wrong - which no longer surprises me)

with using remote SQL is that I won't be able to use files on the local
system as well as the DDM file.

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg

Sent: Tuesday, June 21, 2011 9:21 AM
To: Midrange Systems Technical Discussion
Subject: Re: Using STRSQL against a DDM file

It's not allowed, Alan - I'm sure you will see a message to that

effect in STRSQL.

Instead, find out what the file is that the DDM file points to and use

remote SQL to get to it - that would include adding an entry to the
relational database list - WRKRDBDIRE is the command to start with, I
believe.

HTH
Vern

On 6/21/2011 7:53 AM, Alan Shore wrote:

Morning all
On one system (system A), I have a DDM file connecting to another
system (system B) I have to pull data from this DDM file on system A,
but when attempting this in STRSQL, I get the message File not table,

view, or physical file.

Which is true
I have attempted to use an OVRDBF with the same result I have used
CPYF and copied this DDM file to another file and was able to use

this CPYF file, but I was wondering if anyone had found a way to use SQL
against a DDM file?

As always, all responses MUCH appreciated

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,

visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take

a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.