I have the following requirement and I started trying DFSORT's PARSE statement:
The input is a CSV file (LRECL=80, LRECFM=FB). Each record will be inserted in a DB2 table. Each comma-separated field is the value of a column of the target DB2 table. Records that start with a semicolon are comments and so must be omitted/ignored.
So the object is to transform each CSV record into an SQL INSERT statement.
Sample CSV input:
Those 4 first records would be at a later stage transformed into something similar to the following:
INSERT INTO MyTableName VALUES(010,'<ReqRef>','N', 'C', 8);
INSERT INTO MyTableName VALUES(015,'<Id>','Y', 'N', 4);
INSERT INTO MyTableName VALUES(020,'</ReqRef>','N', 'C', 9);
Where the first field in the SQL statement is a sequence number (incremented by 5), and the last field is the length of the XML tag in the input CSV file.
Using DFSORT's PARSE function, I've almost managed to parse the input, but I can't find a way to calculate the length.
This is my question: How can I calculate with DFSORT the exact length of a parsed field? Scrutinizing DFSORT manual I haven't come up with a "length" function. Any hint or suggestion would be highly appreciated.
Note: I know it's possible to LOAD a table using CSV syntax. I'm not interested in this. What interests me is the feasibility of calculating the length of a parsed field.
This is my approach using DFSORT statements:
* Ignore comments in input
* Parse input fields
OUTFIL PARSE=(%01=(ENDBEFR=C',',PAIR=QUOTE,FIXLEN=80), * XML Tag, max 80 bytes
%02=(ENDBEFR=C',',FIXLEN=1), * Mandatory: (Y)/(N)
%03=(ENDBEFR=C',',FIXLEN=1)), * TagType: (I)nternal/(C)trl/(N)ormal
BUILD=(SEQNUM,3,ZD,START=5,INCR=5, * Writes Sequence number
%01, * writes XML tag
%02, * writes Y or N (mandatory)
%03, * writes I C or N (tagtype)
????? ) * How to calculate exact LENGTH OF %01 ??????