AW: Replacing carriage returns in a data file

If embedded SQL would be an option for replacing characters in a stream
file, you may try the following example:

D CLOBFileFrom S SQLType(CLOB_File)
D CLOBFileTo S SQLType(CLOB_File)
/Free
CLOBFileFrom_Name = '/home/Hauser/MyFile.txt';
CLOBFileFrom_NL = %Len(%Trim(CLOBFileFrom_Name));
CLOBFileFrom_FO = SQFRD; //Read Only

CLOBFileTo = CLOBFileFrom;
CLOBFileTo_FO = SQFOVR; //Replace if exists

Exec SQL Set :CLOBFileTo = Replace(:CLOBFileFrom, x'0D25', '');
If SQLCODE < *Zeros;
//Handle SQL Errors
EndIf;
Return;
/End-Free

BTW file reference variables (--> SQLType(CLOB_File), SQLType(DBCLOB_File),
SQLType(BLOB_FILE) and all the SQLType(XML_?LOB_FILE)s) can be managed with
SQL functions like any character variable.
In this way the streamfile could easily be written directly into a database
table directly with embedded SQL (without CPYFRMxxx command).

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan Shore
Gesendet: Wednesday, 14.8 2013 22:52
An: Midrange Systems Technical Discussion
Betreff: 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