Page 1 of 2

Triggering a Link

Posted: Mon Mar 06, 2006 5:10 pm
by chowdary
People,

Is there any way that i can tell the link to start processing rows afte the particular link got completed in a server job (not in the Sequnce job),

Please throw some suggestions,

Thanks
Chowdary

Posted: Mon Mar 06, 2006 5:14 pm
by ray.wurlod
What link, where?
There is insufficient information in your question.

Posted: Mon Mar 06, 2006 5:23 pm
by rasi
Chowdary

If I understood you correctly, it's datastage Architecture. You can never control the way it works. It's already designed in such a way that as soon as its required link is finished it will start processing its next step. Are you trying to control the stages inside your jobs to run when you want ?....

Posted: Mon Mar 06, 2006 6:16 pm
by chowdary
People,

Sorry for confusing you all people,
My Job is like this

OCI1(TableA)
|
|
|
\/
Transformer-----Link1---------->XML File
|
|
Link2
|
|
\/
OCI2(Table B)



Once the Data is dupmed into XML file from Table A, i need to Update or Insert a row in Table B which will have Job name and JobStartTimeStamp based on job Id (job id is a key in table B,each job will have auniq id).

So i want Link2 should start processing after Link1 is done.

Can i do that?

Please thorow suggestions.

Thanks
Chowdary

Posted: Mon Mar 06, 2006 7:53 pm
by sanjay
Hi

U can use sequencer .


Thanks
Sanjay

chowdary wrote:People,

Sorry for confusing you all people,
My Job is like this

OCI1(TableA)
|
|
|
\/
Transformer-----Link1---------->XML File
|
|
Link2
|
|
\/
OCI2(Table B)



Once the Data is dupmed into XML file from Table A, i need to Update or Insert a row in Table B which will have Job name and JobStartTimeStamp based on job Id (job id is a key in table B,each job will have auniq id).

So i want Link2 should start processing after Link1 is done.

Can i do that?

Please thorow suggestions.

Thanks
Chowdary

Posted: Mon Mar 06, 2006 8:54 pm
by kwwilliams
Whaty Sanjay is saying is that you would have to break that up into two different jobs if you have that requirement. But looking at your job setup I don't see why that would be a requirement. Why exactly does the XML file need to be populated at the same time instead of simultaneously?

Posted: Mon Mar 06, 2006 10:24 pm
by chowdary
Williams,

I would need to update the time when in the job ran in the Table B, when the job runs next time the time in the Table B is used while to select the Delta data from table A , that is why i need to put in the same job instead of sequencer as i need the DSJ.JOBSTARTTIMESTAMP.

so please suggest me some ideas

Thanks
kwwilliams wrote:Whaty Sanjay is saying is that you would have to break that up into two different jobs if you have that requirement. But looking at your job setup I don't see why that would be a requirement. Why exactly does the XML file need to be populated at the same time instead of simultaneously?

Posted: Mon Mar 06, 2006 11:37 pm
by rasi
Chowdary

What you can do is use a Aggregator Stage and use the Last function to update a record when the the last record is read. In your existing design in place of Link2 add the Aggregator stage which will solve your problem

Posted: Mon Mar 06, 2006 11:39 pm
by tcj
Use a seq stage between the transformer and the OCI table.

Pass record information you want to insert/update to the seq stage then have a third link go from the seq stage to the OCI2(table B) stage. This will be the second part of the job. It will only run once the XML file has been build.

I have used this method many times to update audit information in oracle tables after the main part of the job has completed.

HTH

Tim

Posted: Tue Mar 07, 2006 6:17 am
by kwwilliams
In the release that I am in I would just use the Macro DSJOBSTARTTIMESTAMP to populate a field in table B. That seems to be totally unrelated to the file. But my perception is that I don't understand your question fully. What is the purpose of the XML file and how is it related to the table?

Posted: Tue Mar 07, 2006 6:26 am
by chulett
I would do the same - there's absolutely no reason to force a wait until the end of the job to record the time the job started.

Posted: Tue Mar 07, 2006 8:42 am
by chowdary
People,

Thanks for all you inputs, I am able to update the Audit table if job reads rows form the source table, but now i am facing another problem, if zero rows are processed the Audit table is not getting updated,

I want to update the time of the job ran, even if zero rows are processed.

how can i do that?

Thanks

Posted: Tue Mar 07, 2006 11:40 am
by ray.wurlod
You can't trigger that from within the job while rows are being processed. You need to detect, afterwards, that 0 rows were processed, and update the audit table with that information. This may require another small job (to update the audit table) and a routine to detect the link row count.

Posted: Tue Mar 07, 2006 11:54 am
by chulett
Ah... but you could. :wink: Drop a second Transformer in there and hook the OCI stage to that one instead. Add a dummy stage variable, the macro in the derivation and a constraint on the output link of @OUTROWNUM=1 and you'll always get one row down it, even if the 'main' job processes zero rows.

Posted: Tue Mar 07, 2006 2:47 pm
by chowdary
Chulett,

Thanks for the input, i tried that one, it works but it has small problem, problem is for some reason the upper stream which pulls data from Table A and sumps into XML file failed in some 1000 th row, by that time the Audit table is geeting updated and it is not getting roll back.

i don't know how to get that one rolled back

chulett wrote:Ah... but you could. :wink: Drop a second Transformer in there and hook the OCI stage to that one instead. Add a dummy stage variable, the macro in the derivation and a constraint on the output link of @OUTROWNUM=1 and you'll always get one row down it, even if the 'main' job processes zero rows.