Parse Header, Trailer and Detail using CFF

rhettbutlernc · Post by **rhettbutlernc** » Mon May 30, 2005 7:21 pm

How can I parse header, detail and trailer of the incoming file using the CFF stage if no 'ID field'( a unique number to indicate Hdr, detail and Trlr) is provided in the input file.

I can get the header by specifying the 'Start Rec #' and 'End Rec #'
in the Selection criteria as 0 and 1 but how can I get the body and trailer with out calculating the total no of records.

Thanks in advance!

ray.wurlod · Post by **ray.wurlod** » Mon May 30, 2005 7:32 pm

That one's easy: you can't. With no way of determining the record type from within the data, and no a priori knowledge of the number of lines in the file, you're stuck.
An easy mechanism, however, is to have a little routine that captures the number of lines (be exeuting wc -l command, for example) and returns this value. The return value can be used (in a job sequence) as the value of a job parameter for your ETL job.
For the header line, @INROWNUM = 1. For the trailer line, @INROWNUM = #MaxLine#. For a detail line, @INROWNUM <> 1 And @INROWNUM <> #MaxLine#.

talk2shaanc · Post by **talk2shaanc** » Mon May 30, 2005 10:19 pm

If i dont have any use of details in my header and trailer in my design, then i would avoid counting the no of lines in the file, if your file size is huge it would take time.

Another way could be, remove the Tail of the file by using TAIL command of unix. In CFF stage give start rec=1 and End rec=max no(999999999999). Then read the file, which would extract only detail record.

ray.wurlod · Post by **ray.wurlod** » Tue May 31, 2005 12:00 am

... and how do you think the tail command works out which are the final lines in the file?

talk2shaanc · Post by **talk2shaanc** » Tue May 31, 2005 1:52 am

ray.wurlod wrote:... and how do you think the tail command works out which are the final lines in the file?

Definately it does not reach tail or end of file, by reading through all the lines

and keeping the count. if I remember exactly, It looks for End-Of-File Mark in the last few bytes and reads according to the option given with the tail command. I dont remember the exact detail now, will post it if i get one.

dsxdev · Post by **dsxdev** » Tue May 31, 2005 5:14 am

Hi rhettbutlernc,
If you are not really interested in Header and trailer record then just give schema of Detail record.
All teh records which do not match this schema would be just rejected with out bothering you.
I you want to capture these Header and Trailer records add a reject link to the Sequential file Stage these Header and Trailer records are written to this file.
which you can process when you need.

If you want to read all three type of records then I guess rays option is better.

Regards
dsxdev

rhettbutlernc · Post by **rhettbutlernc** » Tue May 31, 2005 6:03 pm

Thanks for all your suggestions...

This is how I have implemented this...(I forgot to mention that I get the total no of records in the header)

I have initially parsed the header based on the Start rec# and End record # (0& 1) and stored the 'NumberOfRecs' into a hash file. The hash file is then used to constrain the incoming records using

@INROWNUM >1 or @INROWNUM < (lnkRecCnt.NumberOfRecs + 1)

Initially I am verifying the no of rows in the header with the total no of records in the incoming file using the function 'DSGetLinkInfo'.

Your comments most welcome...