I'm not using SQL against a DDMF...use SQL against the source file...this process is running on the source machine, not the target. So the SQL will work against the source file.
I'll be more clear.
Use SQL to create an SQL cursor against the source file on the source machine.
Read the cursor and write the selected row to the DDMF using the HLL program's IO functions (not SQL).
When the row is written to the local DDMF, the data will go to the target machine's file.
Steve Needles
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Friday, August 11, 2017 8:38 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: How can I speed up the copying of data using DDM and field selection.
Steve,
One, if you try to use SQL against a DDM file, you will get SQL7011 DSPMSGD RANGE(SQL7011) MSGF(QSQLMSG)
Two, if you could use sql against this DDM file it would probably still suck the data down to the target machine, do the row selection, and still have the crappy performance of CPYF with selection parameters. Doing RPG I/O would also have this issue. Remember he wants <1% of a table with millions of rows.
The goal is to cut down the number of I/O's coming across the comm line.
Until sql statements run on IBM i have some sort of "openquery" technique to have the sorting/selection to be ran on the remote system to only pipe down the desired rows you're limited to the other techniques presented, such as:
- subsetting the data on the source to a temp file and they having the remote system download that
- using sql on the target to call a stored procedure on the source which only returns the desired rows
- creating an index or view which only returns the desired rows and querying 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: Steve Needles <Stephen.Needles@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 08/11/2017 09:25 AM
Subject: RE: How can I speed up the copying of data using DDM and
field selection.
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Create a DDMF on the source machine to the target file.
Write a program to run on source machine to read the source table, perform
selection...this could be done with SQL as it is all local and will not
require the RDB entry. Write the selected row to the DDMF that points to
the target machine's file.
This seems like it should get the desired subset of data from the source
machine to the target machine.
Steve Needles
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of a4g
atl
Sent: Thursday, August 10, 2017 8:44 AM
To: midrange-l@xxxxxxxxxxxx
Subject: How can I speed up the copying of data using DDM and field
selection.
We do not have a relational database entry for our production system so I
cannot use SQL to copy data from the production system.
I am using DDM and CPYF with field testing. The problem is I am trying to
copy 100k records from a file that has over 800 million records. CPYF does
not do the selection on the source system and instead appears to copy all
800 million records to the test system where it checks the values and
either writes the records or discards them. This takes forever to copy.
I have read rights to the file on the source system.
Is there another way to speed up the copy?
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.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD
CONFIDENTIAL: The information contained in this email communication is
confidential information intended only for the use of the addressee.
Unauthorized use, disclosure or copying of this communication is strictly
prohibited and may be unlawful. If you have received this communication in
error, please notify us immediately by return email and destroy all copies
of this communication, including all attachments.
--
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.
Please contact support@xxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD
--
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
CONFIDENTIAL: The information contained in this email communication is confidential information intended only for the use of the addressee. Unauthorized use, disclosure or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by return email and destroy all copies of this communication, including all attachments.
midrange.com
