How to wait until all the data on a link is processed?

yabhinav · Post by **yabhinav** » Tue May 18, 2010 12:58 am

Hi,

I have a job which reads data from a file. The file has data in the following format

Header
Detail
Detail
Detail
Detail
Trailer

The job design is simple. I have a sequential file stage followed by a transformer which separates the header, detail and trailer.

The header link will go and insert into a table and likewise for the detail
Once the trailer message comes it will kick off a function that will update the data inserted by the detail flow.

The problem is that, before the last detail message is inserted, the trailer is fetched and the function is not updating the last detail message.

I want to know if there is any way for the job to wait until the detail messages are processed and then fetch the trailer?

ShaneMuir · Post by **ShaneMuir** » Tue May 18, 2010 1:53 am

What target stage are you using?

You could try putting a reject link from the detail insert stage and placing that as a referential lookup into the msg trailer link. This should, in theory, make the flow from the msg trailer link wait until all the inserts into the table have finished, then continue on from the lookup to use the msg trailer link to do what ever it has to do.

Once again only a theory, not sure if it will actually work.

Or you could just do it in two different jobs.

yabhinav · Post by **yabhinav** » Tue May 18, 2010 2:35 am

Thanks for the suggestion Shane, but we are using a custom stage called UOW.

So i will not be able to have reject links on it.

I can't use two different jobs because I need to pass some values coming in from the trailer to the function.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue May 18, 2010 2:41 am

Not fully sure what you need.

Did you try running that stage(s) / job in a sequential mode or in 1 node config ?

yabhinav · Post by **yabhinav** » Tue May 18, 2010 2:53 am

Sainath.Srinivasan wrote:Not fully sure what you need.

Did you try running that stage(s) / job in a sequential mode or in 1 node config ? ...

Yes Sainath, the job is running on a single node.

And my problem is even before my detail record is insert into the database my trailer record is calling a function which will updated a column on the inserted details. Effectively missing out on updating the last detail column. I just want my trailer to wait till all the detail message are laoded

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue May 18, 2010 3:28 am

That may be because of pipeline parallelism.

Include a dummy lookup stage just before writing the target trailer to lookup some dummy value from the detail stream. This may cause the necessary delay.

Btw, does the trailer calculation function happen in the database ?

yabhinav · Post by **yabhinav** » Tue May 18, 2010 3:38 am

Thanks Sainath, forcefully creating a delay should work, for that I just added a Sleep 10 secs in the function.

I was just wondering if there was any way to handle it in Datastage.

I'm calling the function in a Lookup so ya the calculation will happen on the database

battaliou · Post by **battaliou** » Tue May 18, 2010 4:06 am

Use 2 jobs. Alternatively create an "After job function". If you dont want to use 2 jobs, you'll have to sort/aggregate your data on the trailer link - not necessary.

ShaneMuir · Post by **ShaneMuir** » Tue May 18, 2010 4:24 am

yabhinav wrote: ... we are using a custom stage called UOW.

So i will not be able to have reject links on it.

I can't use two different jobs because I need to pass some values coming in from the trailer to the function.

I take it this means that your custom stage takes all the input streams for each file segment (header, detail, trailer) then updates using the function within the stage?

If that is the case, we can't really comment without knowing what the stage actually does.

yabhinav · Post by **yabhinav** » Tue May 18, 2010 11:44 pm

I understand Shane. Anyways thanks for your help.
We came up with a work around for the problem.

ray.wurlod · Post by **ray.wurlod** » Tue May 18, 2010 11:50 pm

Care to share?

yabhinav · Post by **yabhinav** » Mon May 31, 2010 3:16 am

ray.wurlod wrote:Care to share?
...

Sorry, I was away for a while.

We read Mq messages from a custom stage, so we added extra parameters to the stage to call a C function.
This C function will check if the incoming record is a trailer or not.
If it is, it will hold the trailer till all the messages in the queue are flushed i.e all the messages are processed and EOW has been received for each of them. After this the trailer will be processed