MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » August 2013

Re: Replacing carriage returns in a data file



fixed

On 15 Aug 2013 00:54, Peter Connell wrote:

The SQL technique instead of CPYFRMIMPF sounds very interesting.

I am not sure how loading a stream file into a program via SQL or writing a STMF using SQL could qualify as "instead of CPYFRMIMPF". Rather, those techniques of moving data between a stream file and a LOB column [as file reference] could replace using STMF blocked or record I/O; in effect, defer to the SQL to effect a multi-block read of the STMF into the LOB field. For the CPYFRMIMPF feature, the required parsing of the data would remain for the user program to perform. And although a much closer replacement for CPYFRMSTMF, the parsing of the Line-End [EOR] is still an issue. Simply having all records as the data stored in a field, is far from having the stream-records being prepared as effective database records which can be inserted or queried. The CPYFRMxxx utilities likely are often still going to be preferable to writing the code, if the chosen utility provides what is necessary without any coding.

Short of having a UDTF [specific or generic; the latter is fantasy, except within very explicitly defined limitations\boundaries] that effects essentially what CPYFRMSTMF or CPYFRMIMPF could effect, copying the stream data into a database file using those CL command invocations or similar [e.g. RYO import] *prior* to accessing the data via the SQL might often remain preferable. Because a UDTF is not update capable, the results would have to be copied to another file anyhow, if a permanent copy of the massaged data were required.

Reminds me of the BULK IMPORT feature available on SQL Server.

The DB2 could similarly add to the available SQL statements to effect import and export. Presumably providing those, awaits their inclusion in the standards; i.e. until then, the features are provided via the CL [copy component] utilities CPYFRMIMPF and CPYTOIMPF, such that they are not in conflict with whatever the standards eventually establish.

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.

The import\export copy utilities use SQL to interface to the database file as input [FROMFILE] or output [TOFILE], using the SQL CLI. Originally the use of the SQL CLI was the non-server mode, which was very finicky; effectively only one utilization per job, and the first utilization was the arbiter for the environment. That and other features implemented using the SQL [e.g. BRMS] for some of their work were /corrected/ to utilize the server-mode of the CLI, for which there should be fewer [¿no?] impacts between them.

The CPYFRMIMPF likely still uses a block-read of the stream file data using the C run-time function fread(); i.e. its use of the SQL is unlikely to be loading the STMF as a LOB File Reference. And of course the implementation of the LOB data loading by the SQL _could be_ effectively the same implementation [i.e. fread()], and thus using the SQL to load that data into a LOB column may be little more than added overhead, as compared with loading the data into a non-column program buffer using the fopen() and fread() directly.

The CPYFRMIMPF generates a [hopefully multi-row] SQL INSERT into the ToFile. The CPYTOIMPF generates a SQL SELECT of data cast to character string form FROM the FromFile.

Do you have an example that demonstrates this.

Given the above, I think "this" might need to be clarified. The given example shows a LOB File Reference declarative being used both to read the file data into the implicit LOB column as target of the REPLACE scalar function and to write the file using the SET per the Host Variable being SET is the LOB File Reference which is established with the /overwrite/ option.

FWiW: You may want to read Birgitta's article; there is a section entitled "External UDTF reading data from a comma-separated file" that shows that the parsing of the data for either just the EOR or both the EOR and effective field-data, is still required:
http://www.ibm.com/developerworks/ibmi/library/i-power-of-udtf/

Regards, Chuck

Birgitta Hauser on Thursday, 15 August 2013 7:36 p.m. wrote:

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).






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact