How to join Header and Trailer records

us1aslam1us · Post by **us1aslam1us** » Tue Apr 11, 2006 9:44 pm

Hi

Can anyone please help me in understanding this issue:

I am having two sequential file one is having the Detail records and the other is having the trailer information like number of records processed in the detail record and the rundate.But i need to join these two file and pass on this to the vendor.initially i thought it is possible to merge these two file using a merge stage but i am not getting the complete records.
How can i proceed further..

Thanks
sam

balajisr · Post by **balajisr** » Tue Apr 11, 2006 9:51 pm

Use Unix Cat command to merge header and trailer. Use Execsh in after job subroutine.

us1aslam1us · Post by **us1aslam1us** » Wed Apr 12, 2006 12:30 am

balajisr wrote:Use Unix Cat command to merge header and trailer. Use Execsh in after job subroutine.

Can u Elaborate it little more.

Thanks
sam

ray.wurlod · Post by **ray.wurlod** » Wed Apr 12, 2006 12:48 am

The job finishes processing rows into the two files. Then you can invoke a UNIX command cat to append the contents of the trailer file to the detail file, or both into a third file. You can do this via an Execute Command activity in the job sequence that is controlling the job, since parallel jobs do not support before/after subroutines.

kumar_s · Post by **kumar_s** » Wed Apr 12, 2006 8:12 am

us1aslam1us wrote:
balajisr wrote:Use Unix Cat command to merge header and trailer. Use Execsh in after job subroutine.
Can u Elaborate it little more.

Thanks
sam

cat filename1 filename2 > filename3
cat is the keyword which should be entred in Command and the rest in Parameter.

DSguru2B · Post by **DSguru2B** » Wed Apr 12, 2006 8:45 am

Yup exactly what I was doing right now

us1aslam1us · Post by **us1aslam1us** » Wed Apr 12, 2006 11:14 am

cat filename1 filename2 > filename3
cat is the keyword which should be entred in Command and the rest in Parameter.[/quote]

Hi Kumar

In the PARAMETER how can i give file names? It is just showing me the job activity stages.

Thanks
sam

pneumalin · Post by **pneumalin** » Wed Apr 12, 2006 1:38 pm

Sam,
Follows the steps:
1. Click on Job Properties of your job
2. Select ExecSH from Before-job Subroutine or After-job Subroutine
3. type "cat filename1 filename2 > filename3 " in input value field.
Note: filename can be replace with your JobParamter including the absolute path of the file location. Ex.
"cat #HeaderPath#/header.txt #SourcePath#/body.txt > #TargetPath#/target.txt "

Let me know if this works for you!

NOTE: One thing to correct Ray's comment in earlier post. Parallel job DOES have the capacity to perform "ExecSH in Before-job Subroutine or After-job Subroutine".(Maybe Ray is talking about something else). The Execute Command for sequencer is another option to join Header and Trailer to your body file, and I prefer to use this option for Best practice consideration. Since the ExecSH is hidden in Job Properties of a job, sometime it is difficult for the support group to identify this action when invetigating the produciton problem, versus the Execute Command is clearly visualized in a sequencer.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 12, 2006 4:38 pm

What I was referring to is the ability to work with more than one partition. Your processing has been managed on more than one partition - did you collect all rows into a Sequential File stage running in sequential mode? Only in that case will executing cat via ExecSH make any sense.

richdhan · Post by **richdhan** » Thu Apr 13, 2006 2:40 am

Hi Sam,

If you intent to do this within DataStage use 2 column import stages one for detail and other for trailer. The outputs of the 2 column import stages will be 1 varchar column with a fixed width. Use the funnel to merge both these outputs and load it into sequential file.

HTH
--Rich

us1aslam1us · Post by **us1aslam1us** » Thu Apr 13, 2006 5:18 pm

I used the cat the command in the Execute_Command stage and it is working good.Thanks all for the Feedback.

Thanks
sam