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