Discussion:
Can you suggest how I can connect to another database in SQL reading the remote file and updating a local file
(too old to reply)
Darryl Freinkel
2014-10-17 15:19:21 UTC
Permalink
Raw Message
I need to be able to read a remote table and update a local table.



I have set up the remote database entries and have used connect. What I
cannot find documentation on is how to use 2 files on different machines.



When I connect to system B, all the tables are in system B.



Is there a way to do a select from system B and insert into system A?
Example:



Insert into fileA [on system A] (select * from FileA [on system B] )



TIA



Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
r***@public.gmane.org
2014-10-17 15:43:37 UTC
Permalink
Raw Message
This is one of those times that the version of your OS and probably
certain PTF information is critical.

What's new for IBM i 7.2
Support for INSERT and CREATE TABLE AS to run SELECT from a remote RDB

Prior to this one might read the remote table with an SQL cursor, and
write locally using native language features like RPG's write operation.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Darryl Freinkel <dhfreinkel-***@public.gmane.org>
To: midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Date: 10/17/2014 11:19 AM
Subject: Can you suggest how I can connect to another database in
SQL reading the remote file and updating a local file
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



I need to be able to read a remote table and update a local table.



I have set up the remote database entries and have used connect. What I
cannot find documentation on is how to use 2 files on different machines.



When I connect to system B, all the tables are in system B.



Is there a way to do a select from system B and insert into system A?
Example:



Insert into fileA [on system A] (select * from FileA [on system B] )



TIA



Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Glenn Gundermann
2014-10-17 15:51:17 UTC
Permalink
Raw Message
Hi Darryl,

This link will take you to an article from Michael Sansoterra, which
was published in the Four Hundred Guru on July 25, 2012. It refers to
DB2 for i 7.1.

http://www.itjungle.com/fhg/fhg072512-printer02.html

Hope this helps.

Yours truly,

Glenn Gundermann
Email: glenn.gundermann-***@public.gmane.org
Cell: (416) 317-3144
Post by Darryl Freinkel
I need to be able to read a remote table and update a local table.
I have set up the remote database entries and have used connect. What I
cannot find documentation on is how to use 2 files on different machines.
When I connect to system B, all the tables are in system B.
Is there a way to do a select from system B and insert into system A?
Insert into fileA [on system A] (select * from FileA [on system B] )
TIA
Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
James H. H. Lampert
2014-10-17 15:55:46 UTC
Permalink
Raw Message
This is the first I've even noticed this thread, so I may be reinventing
the wheel here, but have you considered JDBC? There is interface code
available for calling JDBC from ILE RPG (it's called JDBCR4, and it's by
no less than Scott Klement), and I can say from experience that it seems
to work rather well.

--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
r***@public.gmane.org
2014-10-17 16:53:50 UTC
Permalink
Raw Message
Does jdbc allow stuff like
insert into mylocallib/mylocalfile
from (select ... from RemoteSystem/RmtLib/RmtFile)
?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "James H. H. Lampert" <jamesl-6/ELSmrcqeUu8xhjR5IN5AC/***@public.gmane.org>
To: Midrange Systems Technical Discussion <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Date: 10/17/2014 11:56 AM
Subject: Re: Can you suggest how I can connect to another database
in SQL reading the remote file and updating a local file
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



This is the first I've even noticed this thread, so I may be reinventing
the wheel here, but have you considered JDBC? There is interface code
available for calling JDBC from ILE RPG (it's called JDBCR4, and it's by
no less than Scott Klement), and I can say from experience that it seems
to work rather well.

--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John Yeung
2014-10-17 17:25:43 UTC
Permalink
Raw Message
Post by r***@public.gmane.org
Does jdbc allow stuff like
insert into mylocallib/mylocalfile
from (select ... from RemoteSystem/RmtLib/RmtFile)
?
I would think that it doesn't, but James implied that the way to use
it would be through Scott's RPG wrapper; so in effect the same
Post by r***@public.gmane.org
Prior to this [enhancement allowing mixing local and remote
databases in the same SQL statement] one might read the
remote table with an SQL cursor, and write locally using native
language features like RPG's write operation.
John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-17 17:39:52 UTC
Permalink
Raw Message
No but you can do something like so:

i5Conn = DriverManager.getConnection(i5url, properties);
sqlConn = DriverManager.getConnection(sqlurl, "myuser", "mypass");
i5Conn.setAutoCommit(false);

Statement stmt = sqlConn.createStatement();
ResultSet rs = stmt.executeQuery("select flda, flb, fldc from mytbl");
PreparedStatement ps = i5Conn.prepareStatement("INSERT INTO MYLIB.MYTBL
VALUES(?,?,?)");

int nbrRecs = 0;
while (rs.next()) {
ps.setString(1,rs.getString(1));
ps.setInt(2,rs.getInt(2));
ps.setString(3,rs.getString(3));
ps.addBatch();
nbrRecs += 1;
if (nbrRecs >= 5000) {
ps.executeBatch();
i5Conn.commit();
nbrRecs = 0;
}
}
Post by r***@public.gmane.org
Does jdbc allow stuff like
insert into mylocallib/mylocalfile
from (select ... from RemoteSystem/RmtLib/RmtFile)
?
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
Date: 10/17/2014 11:56 AM
Subject: Re: Can you suggest how I can connect to another database
in SQL reading the remote file and updating a local file
This is the first I've even noticed this thread, so I may be reinventing
the wheel here, but have you considered JDBC? There is interface code
available for calling JDBC from ILE RPG (it's called JDBCR4, and it's by
no less than Scott Klement), and I can say from experience that it seems
to work rather well.
--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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 subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-17 18:10:20 UTC
Permalink
Raw Message
I need to do this in a SQL script run from the AS/400. I can use the
iNavigator Script editor, but how do you do it.

Judging from the comments, there is not a way to do this. I do not want to
write any code in RPG. If I did do it in RPG, I could simply use DDM. SQL
is so much quicker.

Thanks
Post by r***@public.gmane.org
Does jdbc allow stuff like
insert into mylocallib/mylocalfile
from (select ... from RemoteSystem/RmtLib/RmtFile)
?
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
Date: 10/17/2014 11:56 AM
Subject: Re: Can you suggest how I can connect to another database
in SQL reading the remote file and updating a local file
This is the first I've even noticed this thread, so I may be reinventing
the wheel here, but have you considered JDBC? There is interface code
available for calling JDBC from ILE RPG (it's called JDBCR4, and it's by
no less than Scott Klement), and I can say from experience that it seems
to work rather well.
--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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 subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
r***@public.gmane.org
2014-10-17 18:33:59 UTC
Permalink
Raw Message
Well, since you are running it from "the AS/400" then I'd tend to agree
that you can't do this.. If, however, you had a newer system that
supported 7.1 or 7.2, and was reasonably current on PTF's then you should
be able to bury that script into a source file and use RUNSQLSTM. Or use
the RUNSQL command to execute it.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Darryl Freinkel <dhfreinkel-***@public.gmane.org>
To: Midrange Systems Technical Discussion <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Date: 10/17/2014 02:10 PM
Subject: Re: Can you suggest how I can connect to another database
in SQL reading the remote file and updating a local file
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



I need to do this in a SQL script run from the AS/400. I can use the
iNavigator Script editor, but how do you do it.

Judging from the comments, there is not a way to do this. I do not want to
write any code in RPG. If I did do it in RPG, I could simply use DDM. SQL
is so much quicker.

Thanks
Post by r***@public.gmane.org
Does jdbc allow stuff like
insert into mylocallib/mylocalfile
from (select ... from RemoteSystem/RmtLib/RmtFile)
?
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
Date: 10/17/2014 11:56 AM
Subject: Re: Can you suggest how I can connect to another database
in SQL reading the remote file and updating a local file
This is the first I've even noticed this thread, so I may be reinventing
the wheel here, but have you considered JDBC? There is interface code
available for calling JDBC from ILE RPG (it's called JDBCR4, and it's by
no less than Scott Klement), and I can say from experience that it seems
to work rather well.
--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
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 subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
John Yeung
2014-10-17 18:37:37 UTC
Permalink
Raw Message
Post by Darryl Freinkel
Judging from the comments, there is not a way to do this. I do not want to
write any code in RPG. If I did do it in RPG, I could simply use DDM. SQL
is so much quicker.
Actually, Glenn and Vern both mentioned ways to do it without RPG.
I'll repeat Glenn's link:

http://www.itjungle.com/fhg/fhg072512-printer02.html

Some key points from the article: (1) Uses new(ish) three-part naming
convention. (2) Relies on DRDA (so if this isn't set up, you have
some configuring to do first). (3) On older systems, you can use
QMQRY to copy the data over.

I can't tell if any of their suggestions are "quick enough" for you.

John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-17 19:00:04 UTC
Permalink
Raw Message
I did review the article. I am still on V5R4. I have used the Technic to
copy the remote file to the local system. The problem is, the file is big
with 66 million records and the record size in larger than 2K. It's just
too bulky to do this way.

Using the INSERT and SELECT with a NOT EXISTS is much quicker.

Thanks
Post by John Yeung
Post by Darryl Freinkel
Judging from the comments, there is not a way to do this. I do not want
to
Post by Darryl Freinkel
write any code in RPG. If I did do it in RPG, I could simply use DDM. SQL
is so much quicker.
Actually, Glenn and Vern both mentioned ways to do it without RPG.
http://www.itjungle.com/fhg/fhg072512-printer02.html
Some key points from the article: (1) Uses new(ish) three-part naming
convention. (2) Relies on DRDA (so if this isn't set up, you have
some configuring to do first). (3) On older systems, you can use
QMQRY to copy the data over.
I can't tell if any of their suggestions are "quick enough" for you.
John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-17 16:08:01 UTC
Permalink
Raw Message
Darryl

As of 7.1 there is new support for three -part names - it doesn't let
you JOIN tables from different machines, so far as I can tell - here's a
bit from the 7.1 Reference

An implicit CONNECT operation may occur when using a three-part object
name or an alias that is defined to reference a three-part name of a
table or view. In the these cases, an implicit CONNECT operation is only
allowed if all the objects referenced in the SQL statement refer to the
same relational database. The only exceptions are:
– The target table of an INSERT statement may be in one relational
database and the tables referenced in the select-statement of the INSERT
may be in another relational database.
– The new table for a CREATE TABLE or DECLARE GLOBAL TEMPORARY TABLE
statement may be in one relational database and the tables referenced in
the select-statement may be in another relational database.

And this bit -

Creating a table using a remote select-statement: The select-statement
for an as-result-table can refer to tables on a different server than
where the table is being created. This can be done by using a three-part
object name or an alias that is defined to reference a three-part name
of a table or view. The select-statement cannot be for a materialized
query table and the result cannot contain a column that has a field
procedure defined.

There's lots more in the 7.1 SQL Reference - but I'm not sure if this
fits your needs - usually we have had to copy something to our local
system, them join the results locally.

IBM does have a product the federates data from disparate systems -
makes them all look like DB2 - that applies to anything, such as Oracle,
SQL Server, probably IBM i - I assume you are talking about the latrer.

Anyway, HTH
Vern
Post by Darryl Freinkel
I need to be able to read a remote table and update a local table.
I have set up the remote database entries and have used connect. What I
cannot find documentation on is how to use 2 files on different machines.
When I connect to system B, all the tables are in system B.
Is there a way to do a select from system B and insert into system A?
Insert into fileA [on system A] (select * from FileA [on system B] )
TIA
Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
D*B
2014-10-17 19:06:52 UTC
Permalink
Raw Message
<Darryl>
I need to be able to read a remote table and update a local table.



I have set up the remote database entries and have used connect. What I
cannot find documentation on is how to use 2 files on different machines.



When I connect to system B, all the tables are in system B.



Is there a way to do a select from system B and insert into system A?
Example:



Insert into fileA [on system A] (select * from FileA [on system B] )



TIA



Darryl Freinkel
</Darryl>

Hi,

lots of nonsens in some answers...
if you've managed to get an DRDA connection, workarounds like JDBCR$ don't
help (they wouldn't help anyway). Three part alias doesn't help too, you
can't use two diffrent databases in one statement. Copy would be possible,
even with QMQRY (outfile parameter), but not update with select from other
database. RPG or COBOL would be an option, read remoten set connection to
local and update local and so on. IBM infosphere federation server (renamed
again from websFear FS) would do this for lots of money for software and an
additional non AS400 Server.

D*B
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
r***@public.gmane.org
2014-10-17 19:34:26 UTC
Permalink
Raw Message
Deiter,

So are you saying IBM and others are lying when they say you can do

INSERT INTO QTEMP/ITEM_MASTER
SELECT *
FROM MyRemoteSystem/DEV/ITEM_MASTER IM

where MyRemoteSystem is an entry from WRKRDBDIRE pointing to another IBM
i,
once you are at certain levels of the OS?

Or are you saying that we just didn't understand what the OP was asking?

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "D*B" <dieter.bender-***@public.gmane.org>
To: "Midrange Systems Technical Discussion" <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Date: 10/17/2014 03:07 PM
Subject: Re: Can you suggest how I can connect to another database
in SQL reading the remote file and updating a local file
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



<Darryl>
I need to be able to read a remote table and update a local table.



I have set up the remote database entries and have used connect. What I
cannot find documentation on is how to use 2 files on different machines.



When I connect to system B, all the tables are in system B.



Is there a way to do a select from system B and insert into system A?
Example:



Insert into fileA [on system A] (select * from FileA [on system B] )



TIA



Darryl Freinkel
</Darryl>

Hi,

lots of nonsens in some answers...
if you've managed to get an DRDA connection, workarounds like JDBCR$ don't

help (they wouldn't help anyway). Three part alias doesn't help too, you
can't use two diffrent databases in one statement. Copy would be possible,

even with QMQRY (outfile parameter), but not update with select from
other
database. RPG or COBOL would be an option, read remoten set connection to
local and update local and so on. IBM infosphere federation server
(renamed
again from websFear FS) would do this for lots of money for software and
an
additional non AS400 Server.

D*B
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
r***@public.gmane.org
2014-10-17 19:38:40 UTC
Permalink
Raw Message
Because, it sure 'seems' to work:
create table rob/fluff as (
select * from gdihq/erplxf/rco) with data
Table FLUFF created in ROB.

select * from rob/fluff
SELECT statement run complete.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: rob-***@public.gmane.org
To: Midrange Systems Technical Discussion <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Date: 10/17/2014 03:34 PM
Subject: Re: Can you suggest how I can connect to another database
in SQL reading the remote file and updating a local file
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



Deiter,

So are you saying IBM and others are lying when they say you can do

INSERT INTO QTEMP/ITEM_MASTER
SELECT *
FROM MyRemoteSystem/DEV/ITEM_MASTER IM

where MyRemoteSystem is an entry from WRKRDBDIRE pointing to another IBM
i,
once you are at certain levels of the OS?

Or are you saying that we just didn't understand what the OP was asking?

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "D*B" <dieter.bender-***@public.gmane.org>
To: "Midrange Systems Technical Discussion" <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Date: 10/17/2014 03:07 PM
Subject: Re: Can you suggest how I can connect to another database
in SQL reading the remote file and updating a local file
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



<Darryl>
I need to be able to read a remote table and update a local table.



I have set up the remote database entries and have used connect. What I
cannot find documentation on is how to use 2 files on different machines.



When I connect to system B, all the tables are in system B.



Is there a way to do a select from system B and insert into system A?
Example:



Insert into fileA [on system A] (select * from FileA [on system B] )



TIA



Darryl Freinkel
</Darryl>

Hi,

lots of nonsens in some answers...
if you've managed to get an DRDA connection, workarounds like JDBCR$ don't


help (they wouldn't help anyway). Three part alias doesn't help too, you
can't use two diffrent databases in one statement. Copy would be possible,


even with QMQRY (outfile parameter), but not update with select from
other
database. RPG or COBOL would be an option, read remoten set connection to
local and update local and so on. IBM infosphere federation server
(renamed
again from websFear FS) would do this for lots of money for software and
an
additional non AS400 Server.

D*B
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
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-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-17 19:46:15 UTC
Permalink
Raw Message
I have to assume that Dieter didn't read the 7.1 documentation that I
copied in my other post - it clearly says that we CAN do exactly this.

I believe that what we cannot do is JOIN tables from 2 different systems
- that is still a limitation and would require the federation stuff.

This is very cool stuff, IMHO.

Vern
Post by r***@public.gmane.org
Deiter,
So are you saying IBM and others are lying when they say you can do
INSERT INTO QTEMP/ITEM_MASTER
SELECT *
FROM MyRemoteSystem/DEV/ITEM_MASTER IM
where MyRemoteSystem is an entry from WRKRDBDIRE pointing to another IBM
i,
once you are at certain levels of the OS?
Or are you saying that we just didn't understand what the OP was asking?
Rob Berendt
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Darryl Freinkel
2014-10-17 14:26:00 UTC
Permalink
Raw Message
I need to be able to read a remote table and update a local table.



I have set up the remote database entries and have used connect. What I
cannot find documentation on is how to use 2 files on different machines.



When I connect to system B, all the tables are in system B.



Is there a way to do a select from system B and insert into system A?
Example:



Insert into fileA [on system A] (select * from FileA [on system B] )



TIA



Darryl Freinkel

Assignment 400 Group, Inc.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
D*B
2014-10-18 06:09:56 UTC
Permalink
Raw Message
Rab,

three part alias is nothing great or new (it's only another mode of writing
for a feature, existing from the beginning of DB2/400). You could redirect
the output of a select statement to a local outfile with interactive SQL and
QMQRY and this is working for local files and remote files in the RDBDIR.
With ArdGate you could do this with files of any JDBC capable database. It
only works in one direction (to local) and you can't join or update tables
this way.

Dieter

PS: @InfoSphere Federation Server: this product is not running on AS/400, i
Series, System i, Power i or how you would call OS/400 and it never did.
It's running on AIX, HP-UX, Linux, Solaris, Windows. It's very expensive and
installation and configuration will take much more time and it will be by
far easier to use a connect by ArdGate to MS SQL Server with a linked table
on SQL Server pointing to a table in DB2/400.

To clearify again: The requirement of the OP (updating a local table with
data from a remote table) is very basic for embedded SQL, but it's not
possible with a single SQL statement. The modified requiremen (insert local
select remote) is possible for all releases with QMQRY (STRQMQRY
<QueryName> OUTPUT(*OUTFILE) OUTFILE(MYLIB/MYFILE) RDB(MYRDB). If you are on
V7R1 you could use the brandnew very great feature three part alias, to do
the same thing.

D*B
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-18 17:29:59 UTC
Permalink
Raw Message
Ever the naysayer, Dieter!

I do not believe anyone is saying this is something could not be done
using several steps. Chuck gave us a set of steps using things like
QMQRY, etc. What the 3-part naming does is let us skip the CONNECT TO -
it does an explicit connection, according to the documentation.

So it is, IMO, a significant addition to our arsenal of tools. Scott
Forstie has some articles out that give good examples of the use here -
and benefits. Anyone interested can simply google for three-part naming
iseries sql - the article I looked at is on iprodeveloper.com

Regards
Vern
Post by D*B
Rab,
three part alias is nothing great or new (it's only another mode of
writing for a feature, existing from the beginning of DB2/400). You
could redirect the output of a select statement to a local outfile
with interactive SQL and QMQRY and this is working for local files and
remote files in the RDBDIR.
-snip-
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
CRPence
2014-10-18 14:09:39 UTC
Permalink
Raw Message
Post by Darryl Freinkel
I need to be able to read a remote table and update a local table.
In a followup reply, v5r4 was revealed as the VRM; of course in that
case, what features [e.g. three-part naming] have been made available on
newer releases are probably moot.?
Post by Darryl Freinkel
I have set up the remote database entries and have used connect. What
I cannot find documentation on is how to use 2 files on different
machines.
When I connect to system B, all the tables are in system B.
In a program, the data that previously had been obtained using a
FETCH from the remote connection [e.g. after CONNECT TO] would be the
source of data used for the INSERT performed after a SET CONNECTION to
the local system. The query will have run on and the data fetched from,
that remote system. The insert runs on the local system and the data is
local to the program. To get all of the data [best done in multi-row
fetches] and then perform the inserts [best done as multi-row inserts],
the SET CONNECTION would need to switch back-and-forth between the
remote [for FETCH] requests and local [for INSERT] requests, until the
end of data.
Post by Darryl Freinkel
Is there a way to do a
select from system B and insert into system A ?
Insert into fileA /* on system A */
( select * from FileA /* on system B */ )
Aside from writing an SQL program, the following are two more direct
ways; note that the latter requires an interactive session\request:

The Start QM Query (STRQMQRY) feature supports an Output File
(OUTFILE) parameter. Directing the output to the output file, functions
as an effective INSERT INTO to a local file; if the output file does not
exist, IIRC, the output database file as an SQL TABLE. The SELECT would
be coded for the *QMQRY object, and the request to obtain the selected
data from the remote database and /insert/ the data into the named
TABLE, would be something like:

STRQMQRY QMQRY(theQuery) OUTPUT(*OUTFILE) OUTFILE(outLib/tableName)
OUTMBR(*FIRST *ADD) RDB(theRDBname)

The Start SQL (STRSQL) feature similarly supports an Output File
feature; the capability is established via the F13=Services, from which
the first option is to set the output device and settings. After the
CONNECT to the remote database, when the query is run, the output from
that query would be directed to the named output file; in this case, the
target output file, if the file did not already exist, would IIRC, be
created as a non-SQL externally described database file.

Each of writing a program, the STRQMQRY, and the STRSQL, should be
available on v5r4. The STRQMQRY would be available [Create QM Query
(CRTQMQRY) from a source vs the interactive Start QM (STRQM) would be
used to define the SELECT query] even if the 57##ST1 product is not
installed, whereas the other two options would not be [directly]
available without that LPP being installed.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Vernon Hamberg
2014-10-18 17:22:13 UTC
Permalink
Raw Message
Chuck

Just to be complete, as of 7.1, we can specify a remote DB in a 3-part
naming in an INSERT or a CREATE TABLE - your replies, of course, are for
those on an earlier version of the OS.

So we can do something like CREATE TABLE LOCALLIB.LOCALTABLE as (SELECT
* FROM RDBENTRY.REMOTELIB.REMOTETBL) -- *SQL syntax here.

Vern
Post by CRPence
Post by Darryl Freinkel
I need to be able to read a remote table and update a local table.
In a followup reply, v5r4 was revealed as the VRM; of course in that
case, what features [e.g. three-part naming] have been made available
on newer releases are probably moot.?
Post by Darryl Freinkel
I have set up the remote database entries and have used connect. What
I cannot find documentation on is how to use 2 files on different
machines.
When I connect to system B, all the tables are in system B.
In a program, the data that previously had been obtained using a
FETCH from the remote connection [e.g. after CONNECT TO] would be the
source of data used for the INSERT performed after a SET CONNECTION to
the local system. The query will have run on and the data fetched
from, that remote system. The insert runs on the local system and the
data is local to the program. To get all of the data [best done in
multi-row fetches] and then perform the inserts [best done as
multi-row inserts], the SET CONNECTION would need to switch
back-and-forth between the remote [for FETCH] requests and local [for
INSERT] requests, until the end of data.
Post by Darryl Freinkel
Is there a way to do a
select from system B and insert into system A ?
Insert into fileA /* on system A */
( select * from FileA /* on system B */ )
Aside from writing an SQL program, the following are two more direct
The Start QM Query (STRQMQRY) feature supports an Output File
(OUTFILE) parameter. Directing the output to the output file,
functions as an effective INSERT INTO to a local file; if the output
file does not exist, IIRC, the output database file as an SQL TABLE.
The SELECT would be coded for the *QMQRY object, and the request to
obtain the selected data from the remote database and /insert/ the
STRQMQRY QMQRY(theQuery) OUTPUT(*OUTFILE)
OUTFILE(outLib/tableName) OUTMBR(*FIRST *ADD) RDB(theRDBname)
The Start SQL (STRSQL) feature similarly supports an Output File
feature; the capability is established via the F13=Services, from
which the first option is to set the output device and settings. After
the CONNECT to the remote database, when the query is run, the output
from that query would be directed to the named output file; in this
case, the target output file, if the file did not already exist, would
IIRC, be created as a non-SQL externally described database file.
Each of writing a program, the STRQMQRY, and the STRSQL, should be
available on v5r4. The STRQMQRY would be available [Create QM Query
(CRTQMQRY) from a source vs the interactive Start QM (STRQM) would be
used to define the SELECT query] even if the 57##ST1 product is not
installed, whereas the other two options would not be [directly]
available without that LPP being installed.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Loading...