|
I have not used QM before.
I'll get the manual and read up on that. This does sound like a good solution.
What is the 3 part naming? I think I know, but have not used it on IBM _i.
Thanks
Darryl
Sent from my iPad
On Jul 28, 2015, at 12:57 PM, rob@xxxxxxxxx wrote:
Vern, I ran it just fine with RUNSQLSTM.
Vern, remember he was trying to insert locally from a remote database. I
am under the impression that using the relational database parameter of
STRQMQRY has the statement run there. If you read the help on that
parameter the OUTFILE parameter gets really picky when you specify a
remote database.
Best to be on a version of the OS that will be supported in more than two
months and just use the three part syntax.
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: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 07/28/2015 11:42 AM
Subject: Re: Using CONNECT to a remote system with RUNSQLSTM
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Hi Darryl
It CAN be done with SQLRPGLE, not with RUNSQLSTM, apparently.
But easiest of all is to use STRQMQRY - that command DOES have the RDB
parameter, meaning you can tell it to run the statement on another
machine - exactly what you want - and you can use the OUTPUT parameter
to have the results on your local machine.
You will need to create a QMQRY - easy enough - STRQM will get you there
- if you use that, when you get into it, if the "Query creation mode"
says PROMPT, press F19 to toggle it to SQL - then you enter an SQL
statement directly, with the same prompting (perhaps unfortunately) as
STRSQL.
You don't need to do the CREATE TABLE, either - the OUTPUT(*FILE) will
take care of you.
You can also put in substitution variables, if you need to run it with
different selections - you put in &VARNAME in the statement, then use
the SETVAR parameter of STRQMQRY - all things you can do in a CL.
HTH
Vern
On 7/28/2015 10:10 AM, Darryl Freinkel wrote:different systems, sorry not LPARs.
Rob
I am copying data from the v5.4 system to the 7.1 system. They are on
The machines are side by side.the 7.1 system. I do everything using DDM and the SBMRMTCMD.
I am using CLLE, SQL scripts and/or SQLRPGLE programs to bring data to
I am only working on the 7.1 system, the local system, to pull the data.I am using DDM files to copy the data. The one file is massive over 65
million records but I only need about 150k records.
Creating a DDM file from the local to the remote system and then usingCPYF takes too long ( more than 3 hours) and does error out with a buffer
overflow. It's an old problem you would think IBM had resolved. There are
v6.1 PTFs for it.
I used STRSQL to test extracting the data with SQL.SQLRPGLE program.
- connect to remote
- create table as (select...where strode = '24') with data
- connect rest.
In STRSQL it works perfectly, but will not work in RUNSQLSTM or a
As the process needs to be repeatable and run in a batch process, I needa work around to the problem.
Thankssaying
Darryl.
Sent from my iPad
On Jul 28, 2015, at 9:13 AM, rob@xxxxxxxxx wrote:
Darryl,
I'm having a tough time understanding your last statement. Are you
lparsthat you can't use something like
create table rob.scrapme as (
SELECT * FROM gdihq.rob.brms WHERE LIBRARY = '*IFS')
with data
because of either of two reasons:
1 - This is all done locally on the 2 LPARs.
2 - There is no code other than native IBM_i code here.
I have done this between two lpars on the same machine. And between
modifyof machines in two different cities. So, what you are saying doesn't
compute with me. Or, were you trying to say that you're using a small
data set and it's local so efficiency is not a concern? That, maybe I
could understand. Coding for some efficiency, as long as you don't get
too extreme, isn't a bad habit to get into. What you code for a small
local sample seems to be the same code that someone will copy and
WHERfor a large dataset between different platforms between different
continents. I'm not saying you need to seriously bit twiddle but
definitely cover this big bang for the buck stuff.
Looking at your second statement: "There is no code other than native
IBM_i code here."
I suppose that leaves out any RPG solutions since you have to buy a
compiler for that.
Then maybe using the ibm command RUNSQL is an option?
RUNSQL SQL('create table rob.scrapme as (SELECT * FROM gdihq.rob.brms
againstE LIBRARY = ''*IFS'') WITH DATA') COMMIT(*NONE) NAMING(*SQL)
or putting the statement into a source member and using RUNSQLSTM
programming?that source member?
Or by "native" did you limit yourself from any imbedded SQL
DevelopmentIf that is the case, are you limited from any imbedded SQL programming
because you do not have "5770ST1-IBM DB2 Query Manager and SQL
different,Kit for i"? If you do not have that product many people use SQL-CLI or
sql call level interface. Which, at first glance, seems really
andunless you have done some sql within other languages.
Or by "native" do you mean some philosophical thing that if it couldn't
have been coded on a S/36 or earlier then that is just too new fangled
decisionwon't be accepted in your shop?
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@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 07/28/2015 08:36 AM
Subject: Re: Using CONNECT to a remote system with RUNSQLSTM
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
This is all done locally on the 2 LPARs. There is no code other than
native IBM_i code here.
Darryl.
Sent from my iPad
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
<midrange-l@xxxxxxxxxxxx>about anything.before
</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
making a decisionODBC
</snip>
Nope. Proved that with a communications trace.
Perhaps you are basing your decision on experiences with Microsoft's
connections. They seem to want to bring the whole table down beforedoing
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
copiedDate: 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
which(with selection criterion) to a DDM file on the production system
whatpoints to a file on our testing system. I do this to get data fromkey
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
is the selection of the data is on the production system copying to
usingit thinks is another production system file. If the data is keyed
speedsFROMRCD/TORCD in the copy command it will avoid indexes and often
secondarythings up in a large copy.
If you think too much data is heading from the primary to the
(withcould you add another step? Main input file copied to a plain copy
theselection criterion) on the same system, then _that_ file copied to
getDDM file. That should avoid any problems with all the data trying to
decisionto 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
iSeriesabout anything. I don't think CPYF has that issue.Zealand
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
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
doingto
the local iSeries. The remote file has over 50 million records.It
I tried to copy using a DDM file with CPYF. It runs but takes forever.
seems that it is copying the whole file to the local system before
athe selection.resulting
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
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
http://archive.midrange.com/midrange-l.moment to review the archives at
is
######################################################################
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)
privilegedintended only for the person or entity
to which it is addressed and may contain confidential and/or
listmaterial.list
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
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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
--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.
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.