RE: Replacing carriage returns in a data file

Birgitta,
The SQL technique instead of CPYFRMIMPF sounds very interesting. Reminds me of the BULK IMPORT feature available on SQL Server.
I can understand that this may be possible since observing that CPYFRMIMPF can to throw SQL errors indicating there is some sort resource contention because of previous use of the SQL environment prior using CPYFRMIMPF in the same job. That implies that CPYFRMIMPF is using SQL under the covers.

Do you have an example that demonstrates this.

Regards, Peter

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Thursday, 15 August 2013 7:36 p.m.
To: 'Midrange Systems Technical Discussion'
Subject: 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