Re: Use SQL to move data from prod400 to dev400, OS v7r2.

On 13-Oct-2015 15:16 -0600, JRusling wrote:

I'd like to use SQL to refresh data for a file that resides on both
our dev and prod box. (I want to move data from prod to dev box)

I've added RDB entries to both systems so they are cognizant of each
other. (using wrkrdbdire, well, addrdbdire)

In interactive SQL, I can use the-
connect to prod user myname using 'mypassword'
to connect FROM the dev to the prod box.

I successfully connect to prod, from dev and can perform select
stmts over prod data, from the dev session.

But, when connected to prod, I can't seem to reference the dev
(local) box.

for instance, I tried 3 part naming like below

insert into dev/jrpers/log
select * from prod/jrpers/log

this yields:

Statement references objects in multiple databases.

The three-part naming creates an implicit connection [to the first-named entity], essentially performing the CONNECT TO implicitly. Thus do *not* first CONNECT TO PROD, before performing that statement.

While connected locally to DEV, test the result from omitting the three-part naming on the specification for the *LOCAL file; i.e. try:

insert into jrpers/log
select * from prod/jrpers/log

That request makes more conspicuous that the local table-reference on the INSERT INTO is the target [i.e. on DEV as *LOCAL], and that there is just the one implicit Remote Connection to PROD. The originally composed statement should work fine too... again, just need to *not* be connected to PROD when directing to get the data implicitly from PROD.

It's sort of like - while connected to prod, from dev, access to the
dev data files is unavailable to me. I've got to be missing some
piece to the puzzle.

Indeed that is the origin... "while connected to PROD, from DEV". The solution to the puzzle is to not make the explicit connection to PROD. Make the request while connected to DEV.

Any help from anyone who has done this successfully would be
appreciated? I've searched a lot for wrkrdbdire and addrdbdire and
it's got me this far.

The problem is that the connection to PROD confuses the SQL; to the SQL, the request does not appear the same as what the composer of the statement sees as a logical equivalent request.

John Rusling
Senior Developer/Analyst
Quadion LLC
Minnesota Rubber& Plastics
952.927.1419
<br />
The information in this email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this
email by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, distribution or any action taken
or omitted to be taken in reliance on it, is prohibited and may be
unlawful.

FWiW: Please include the three successive characters of dash dash space on their own line [i.e. followed by <crlf>, or <lf> on *nix] as a signature marker. By doing so, all of that above [mostly superfluous] signature text will not be included in the archives and will [in many email\news clients] be automatically pruned when the responder uses the client's reply-to feature; all of the data will still be included in the copies of the email received individually.