You can do what I think you are describing with a UDTF. The article at
http://www.mcpressonline.com/tips-techniques/sql/techtip-using-sql-with-
ifs-text-files-part-2.html has a UDTF that reads ifs files. Given a text
file like this:
ABC 123 A
DEF 456 I
GHI 789 A
JKL 012 I
A Table like this:
create table kevin/udtftest
(key char(10),
value char(10))
This statement will populate selected data from that file.
insert into udtftest (key,value)
select substr(linetext,1,3) as key,
substr(linetext,7,3) as value
from table(readifsfr('/kevin/udtfexample.txt',X'0D25')) as
text
where substr(linetext,13,1) = 'A'
Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550
-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Duane
Scott
Sent: Tuesday, August 30, 2016 12:41 PM
To: Rational Developer for IBM i / Websphere Development Studio Client
forSystem i & iSeries
Subject: Re: [WDSCI-L] Making the most, or over-thinking it - SQL for
uploads
John, you caught me. I'm often way too wordy, at least that's what I'm
told by everybody else.
I attempted to say everything relevant to my situation and my questions,
which basically are: Can it be done and where can I learn how to set it
up? "It" meaning " to use SQL to import data from a PC .txt file into
an IBM i data file", much the way Client Access Data transfer does, but
with the convenience of SQL in selection of which columns and rows.
I've been using SQL for a number of years, mostly interactively on the
IBM i and in the last few years within RPG as embedded SQL. I am a big
fan. I am also a big fan of doing things simply, meaning the less steps
the better. I figured (or hoped) that being able to use RDi's Data
Studio to import data would be a "one step" solution to the multi-step
solution of the Data Transfer method used for 30+ years on the IBM i.
The text files were just what I consider standard data files in text
format which I obtained from a trusted web site. Basic data in column
format with CR/LF at end of last column. Fortunately the two columns I
needed were not numeric or date, both which would have made SQL the
better choice for manipulation, but I digress.
While I considered putting the text files onto the IFS, my original
thought still stands, why not see if SQL can read the PC .txt file and
use the INSERT function along with a WHERE clause and Substring out the
necessary columns. That's what I did after using Data Transfer to get
the file into a DB2 database. The use of transferring the data to the
IFS is still an extra step in some cases and yet wouldn't have been had
I just downloaded the files directly to the IFS. Maybe next time I'll
do that, but that isn't an answer to my questions.
RDi is my tool of choice for development. With the added benefit of
creating SQL scripts, it's becoming a one-stop shop for me and I'd like
to make the most use of it. There may be tools available somewhere that
I haven't discovered or used yet (and I've probably only used a very
small portion) that would make this task easier. But my first thought
is to use tools that I know and are part of IBM's tool sets.
I'm familiar and have used CPYFRMIMPF, but again, not the answer I'm
looking for, which is beginning to look like I'm not going to like the
answer. And that's okay and why I worded my original email as I did. I
really wanted to know if it was a common thing or not. It appears not
to be common, which is a standard for me. I break ground on a lot of
things within my small group of knowledgeable friends and co-workers.
I want to find the .txt file on my network drive and read it using SQL.
If I can do that, I can possibly figure out how to insert the data I
want into my IBM i file. It's really that simple and yet seems to be so
complicated. Data Transfer has been doing PART of that for decades.
But, I vent.
Thanks for listening.
duane
-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of John
Yeung
Sent: Tuesday, August 30, 2016 12:47 PM
To: Rational Developer for IBM i / Websphere Development Studio Client
for System i & iSeries <wdsci-l@xxxxxxxxxxxx>
Subject: Re: [WDSCI-L] Making the most, or over-thinking it - SQL for
uploads
Duane, I am having trouble understanding your situation. You said:
importing data from PC to DB2 is done constantly
OK, that sounds completely normal and I'm following you so far.
using SQL is my FIRST thought over most of the tools that we have
access to, simply because of its convenience. At least perceived
convenience.
Things are already going off the rails here. Then you continue:
As to the method that I used to get the data this time, I used Access
Client Solutions Data Transfer. But doing so forced me to load the
entire record to a DB2 data file from which I used interactive SQL from
an IBM i ACS emulation session to INSERT my selected fields.
What kind of text files are we talking about? And are these text files
the "purest" source of this data, or are the text files just an
intermediate format used for data transfer? For example, is the REAL
data in another database, or an Excel file, or something else; and you
just copied that into a text file because you figured that it's easier
to get data from a text file into the i?
Until I get more details, the best my imagination can come up with is
very much in-line with what Vern is thinking. Just get your text file
into the IFS and take it from there.
I am not sure what is going on with your conversation with Buck. To me,
it seems like Buck misunderstood your original post, and thus brought up
lots of correct but irrelevant stuff, yet you seem to be good with his
response. So I am really clueless there.
When you say things like:
I'm not familiar with setting up the PC to interact.
and
I really just figured that I could access text files similar to
accessing them as done on IBM i.
These things make me wonder what RDi has to do with anything.
Finally,
I also figured that it was my own lack of experience and knowledge of
setting up the PC that caused the inability to just find the file and
open it up.
Um, "just find the file and open it up"? Find the file *from where* and
open it up *with what*? It almost sounds like you want to be able to
"find" the file from the i and open it up with whatever it is you use on
the i to work with stream files (such as an RPG program or the EDTF
command or CPYFRMSTMF or CPYFRMIMPF). There *are* ways to set up some
disk space that is directly accessible from both the i and a PC (so you
can just copy the data to that space from the PC side and pick it up
from the i side).
I think we could all use some more information about your situation.
John Y.
--
This is the Rational Developer for IBM i / Websphere Development Studio
Client for System i & iSeries (WDSCI-L) mailing list To post a message
email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit:
http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/wdsci-l.
________________________________
________________________________
CONFIDENTIALITY NOTICE: This electronic message transmission is
intended only for the person or entity to which it is addressed and may
contain information that is privileged, confidential or otherwise
protected from disclosure. If you have received this transmission, but
are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution or use of the contents of this
information is strictly prohibited. If you have received this e-mail in
error, please contact NALC Health Benefit Plan at 703-729-4677 and
delete and destroy the original message and all copies.
--
This is the Rational Developer for IBM i / Websphere Development Studio
Client for System i & iSeries (WDSCI-L) mailing list To post a message
email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit:
http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/wdsci-l.
midrange.com
