Re: Using CONNECT to a remote system with RUNSQLSTM

On Jul 28, 2015, at 7:43 AM, rob@xxxxxxxxx wrote:

<snip>
I haven't done much SQL across systems like this - but SQL seems more
likely to me to what to absorb the entire table before making a decision
about anything.
</snip>

all in fun...
<snip>
I haven't done much SQL across systems like this
</snip>
apparently :-)

<snip>
but SQL seems more likely to me to what to absorb the entire table before
making a decision
</snip>

Nope. Proved that with a communications trace.

Perhaps you are basing your decision on experiences with Microsoft's ODBC
connections. They seem to want to bring the whole table down before doing
the selection. See this recent thread:
http://archive.midrange.com/midrange-l/201507/msg00124.html
on how to work around that.

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: Jim Wiant <Jim.Wiant@xxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 07/27/2015 09:55 PM
Subject: RE: Using CONNECT to a remote system with RUNSQLSTM
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I do something very similar: a files on our production system is copied
(with selection criterion) to a DDM file on the production system which
points to a file on our testing system. I do this to get data from
production without having to get a lock on the file. I don't have near
that amount of records but it seems to run pretty fast - perhaps the key
is the selection of the data is on the production system copying to what
it thinks is another production system file. If the data is keyed using
FROMRCD/TORCD in the copy command it will avoid indexes and often speeds
things up in a large copy.

If you think too much data is heading from the primary to the secondary
could you add another step? Main input file copied to a plain copy (with
selection criterion) on the same system, then _that_ file copied to the
DDM file. That should avoid any problems with all the data trying to get
to the target.

I haven't done much SQL across systems like this - but SQL seems more
likely to me to what to absorb the entire table before making a decision
about anything. I don't think CPYF has that issue.


James P. Wiant
Test System Administrator

FOODSTUFFS
NORTH ISLAND LIMITED

DD: 09 621 0774 | M: 027 463 4159| P: 09 621 0600
DX Box CX 15021 or PO Box 27480 Mount Roskill, Auckland 1440, New Zealand

Fast is fine. Accuracy is everything
Earp, Wyatt

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Darryl Freinkel
Sent: Tuesday, 28 July 2015 13:37
To: Midrange Systems Technical Discussion
Subject: Using CONNECT to a remote system with RUNSQLSTM

I am trying to copy a subset of records from a file on a remote iSeries to
the local iSeries. The remote file has over 50 million records.

I tried to copy using a DDM file with CPYF. It runs but takes forever. It
seems that it is copying the whole file to the local system before doing
the selection.

I added a entry in WRKRDBDIRE, used STRSQL to connect to run command
CREATE TABLE ... with data. It ran is about 30 seconds.

I then tried to run the same SQL SCRIPT with RUNSQLSTM and found the
CONNECT is invalid in that environment.

The local system is at V7.1. The remote system is V5R4.

Does anyone have any work arounds to this issue?

My alternative may be to use RMTCMDs with CPYF and then copy the resulting
file to the local system with DDM or FTP.

TIA.

--
Darryl Freinkel
--
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 message was scanned for compliance with Foodstuffs North Island
Limited email policies
######################################################################
This message has been sent from Foodstuffs North Island Limited
(Foodstuffs).

The information contained in this message (including its attachments) is
intended only for the person or entity
to which it is addressed and may contain confidential and/or privileged
material.
If you received this message in error, please contact the sender
immediately by return email and delete this message and your reply.
You must not use, disclose, distribute, print or copy any part of this
message.

The views and opinions expressed in this message may be those of the
individual sender and not necessarily those of Foodstuffs,
in which case the views are not given or endorsed by Foodstuffs.

Please note that this communication does not designate an
information system for the purposes of the Electronic
Transactions Act 2002.
--
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.