Source records equal to trailer records

dslearner · Post by **dslearner** » Fri Sep 23, 2005 2:26 am

HI,

This is the sequential source

Date,Cusip,Security,Speed,SpeedType,Yield,Duration,Moody,SP,MajorType
9/20/2005,79548KP77,SBM7 1999-C1 B,0,CPY,4.63,3.004,Aaa,,
9/20/2005,036234190,.GSR 2005-AR6 1A1,,,4.59,1.9,,,
TRAILER,2,Wed Sep 21 05:00:00 EDT 2005,,,,,,,

The following condition

If No.Source records = Trailer count then pass into another Target (sequential) file. If condition not satisfies then abort the job, it does not insert the records in the target file.

Please tell me any one know, How do I will implement this logic?

Thanks
Sri Hari

adarsh shrinagesh · Post by **adarsh shrinagesh** » Fri Sep 23, 2005 3:58 am

One option could be process the source file in the job control
Count the number of lines<excluding the first and last> and compare this with the count field of the trailer record. If they dont- call jobAbort.

There are also inbuilt routines that count the number of lines in datastage 7.5 which u cud use.

dslearner · Post by **dslearner** » Fri Sep 23, 2005 4:37 am

adarsh shrinagesh wrote:One option could be process the source file in the job control
Count the number of lines<excluding the first and last> and compare this with the count field of the trailer record. If they dont- call jobAbort.

There are also inbuilt routines that count the number of lines in datastage 7.5 which u cud use.

Hi Adarsh,

Iam not get the ur point,please explain clearly.

Thanks

adarsh shrinagesh · Post by **adarsh shrinagesh** » Fri Sep 23, 2005 5:29 am

dslearner wrote:
adarsh shrinagesh wrote:One option could be process the source file in the job control
Count the number of lines<excluding the first and last> and compare this with the count field of the trailer record. If they dont- call jobAbort.

There are also inbuilt routines that count the number of lines in datastage 7.5 which u cud use.

Hi Adarsh,

Iam not get the ur point,please explain clearly.

Thanks

U could either create a routine<which wud then be called in the before stage subroutine> or do some preprocessing in the Job Control ... where u could read the sequential file and count the number of rows and compare it with the value of the Trailer record's 2nd field.

Either ways u cud call DSLogFatal to abort the job if the condition fails(ie no of records counted<> no of records mentioned in the trailer record>

pnchowdary · Post by **pnchowdary** » Fri Sep 23, 2005 8:52 am

Hi dslearner,

Its always a good practice not to abort a job. Instead you can use two jobs, first job to check your condition and second job to actually process the data. In a job sequence, you can run the first job and depending upon the result, either run the second job or not.

ray.wurlod · Post by **ray.wurlod** » Fri Sep 23, 2005 5:19 pm

You can pre-process in UNIX (perhaps through a before-job subroutine).
The number of detail lines in the file is given by expr `wc -l $file` - 2 while the number from the trailer line is given by tail -1 $file | cut -d',' -f2,2

rleishman · Post by **rleishman** » Sat Sep 24, 2005 5:37 am

We are doing the exact same thing at our site. Incoming files have headers and trailers; if they are missing or contain incorrect numbers then we assume that the extract or transfer mechanism by which they arrived has failed and we do not process them. Whether you abort or skip depends on how important the file is and its dependencies.

We dealt with it as Ray suggested: with a Unix script before the DS job processes the file.

I wrote a generic Perl script that I call "File Receiver". It will:
- handle delimited or fixed width files by supplying an optional delimiter
- verify the existence of a header if required by checking for a key word
- verify the existence of a trailer if required
- verify a detail record count from a given column/char range in the trailer
- strip DOS carriage returns and EOF markers
- read the nominated file and pass detail records to a nominated output file.

If dslearner or anyone else is interested I would be happy to provide it free of charge (and free of liability, support, and responsibility). It is vanilla Perl and should run on any Unix system. It's probably a bit chunky to post here though; send me a private message if you want it emailed, if many want it I will post it on the web and leave the URL in this thread.

kduke · Post by **kduke** » Sat Sep 24, 2005 6:57 am

If it is not too big then post it in this thread with alt-c code tags around it.

rleishman · Post by **rleishman** » Sat Sep 24, 2005 7:55 am

Whats "too big". I'm not at work at the moment, but I think it's a few hundred lines inc comments.

chulett · Post by **chulett** » Sat Sep 24, 2005 8:28 am

What ever became of our File Library?

ray.wurlod · Post by **ray.wurlod** » Sat Sep 24, 2005 4:58 pm

It's currently at ADN, but stay tuned...