RE: Replacing carriage returns in a data file

This day just keeps getting better and better (sarcasm switch off)
I have now realized that the file (with the carriage return) is created using the command
CPYFRMSTMF
And the carriage return is in the stream file
This then causes the one record to be split into 2 (or more) in the table.
I have to replace the carriage return in the ifs prior to the CPYFRMSTMF command
I cannot find any examples of using SQL on an ifs file.


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: Alan Shore
Sent: Wednesday, August 14, 2013 4:20 PM
To: Midrange Systems Technical Discussion
Subject: RE: Replacing carriage returns in a data file

Thanks Luis
Let me have a test using what you supplied MUCH appreciated

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Wednesday, August 14, 2013 4:13 PM
To: Midrange Systems Technical Discussion
Subject: Re: Replacing carriage returns in a data file

Alan,

You could try executing the following SQL statement:

update @test/alan_cr set alan_cr = replace(alan_cr, X'0D', '\')

IIRC, the hex for a CR is X'0D', so this would place a '\' in its place (or whatever character you need). You could call this inside a CL using the QSH
DB2 instruction.

HTH,
Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries