DSXchange

greetings.

below is my routine code which gives me previous year based on SYSDATE.
When Iam testing the Routine its working fine. But when I use it in a Sequence job with expression = "OK (Conditional)", the jobs after this Routine Activity are NOT being called.

If I change the Routine Activity Expression to "Unconditional"...all the jobs are being called and finished OK.

I guess, my rotine activity is returning 'previous year' correctly, but its not finishing 'OK'. Its finishing with warnings ( I guess).

Please through some light on it.

------------------------------------------------------------

**************************
* Set datastage system functions

$include DSINCLUDE JOBCONTROL.H
Equate RoutineName To "GetPrevYear"
Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
**************************************
Ans = Oconv(@date,'DY') - 1
----------------------------------------------------------------------

In a Job sequence, any routine you use, fails if the Returnvalue (Ans) is not equal to zero.

Hence, your routine is failing when you define the output condition as OK(since the output is a date) and the subsequent Jobs are not running.

What exactly are doing in your Job?If you can explain your design, may be we could help.

You are computing the previous year in the routine. You can do the same in a set user variables stage(instead of the routine stage) by defining a new variable and calling your routine there. That way subsequent jobs wil run.

Kris, thanks a lot for the quick reply.

My design is as below...

1) Get the Previous Year on your SYSDATE ( it returns 2005 now)
2) Pass this value ( 2005 in this case) to all my next Job Activities.
3) my undetlying jobs will use this value and pulls the data relevent to that particular year ( 2005 in this case)

kris007 wrote:In a Job sequence, any routine you use, fails if the Returnvalue (Ans) is not equal to zero.

Hence, your routine is failing when you define the output condition as OK(since the output is a date) and the subsequent Jobs are not running.

What exactly are doing in your Job?If you can explain your design, may be we could help.

sud wrote:You are computing the previous year in the routine. You can do the same in a set user variables stage(instead of the routine stage) by defining a new variable and calling your routine there. That way subsequent jobs wil run.

Pardon my ignorance, could you please elaborate it?

You can do what sud has suggested.
Alternatively,

What you can do is, Don't use the routine activity in the Jobsequence.Instead, in the Job activity stage, go to the parameter where you want to pass this date and then right click inthat expression box. It is similar to the Derivations in the Transformer Stage. Call that routine in there.

Hmm..Ok. I re-read your post and here's what I suggest. Since, you are using this date in more than one Job in your Jobsequence, sud's approach would be more sensible.Execute the routine in the Uservariables stage and then pass it to all the Jobs where you need to use this data.

IHTH

Or just stick with an 'Unconditional' trigger.

chulett wrote:Or just stick with an 'Unconditional' trigger.

Thank you all.
Just want to make sure I understand it correctly.
Routine Activity fails unless untill the Ans = 0. When ever we use routine activity in Sequence jobs we use 'Unconditional' trigger.

anu123 wrote: When ever we use routine activity in Sequence jobs we use 'Unconditional' trigger.

That depends on what you are doing in your routine.
In your Case, as Craig has mentioned, Just pass the an unconditional trigger and you will be fine. In Job sequences, a Routine(activity) fails if the Ans is not equal to zero and you have defined an OK trigger.

anu123 wrote:Routine Activity fails unless untill the Ans = 0. When ever we use routine activity in Sequence jobs we use 'Unconditional' trigger.

As Kris noted, you need to understand the behaviour of routines, how triggers decide if they 'failed' or not and use them accordingly based on each case.

If you enable the 'Automatically handle activities that fail' then the Sequence job will look to see if you have handled the failure activity and, if not, it will attempt to handle it for you. And as noted, Routines that do not return a zero are considered to have failed. However, that doesn't mean you can't explicitly handle any so-called errors yourself. You could run both an 'OK' trigger and an 'Otherwise' trigger into a Sequencer set to 'Any' to disable the automatic handling that your Job Activities are leveraging. It would always take the Otherwise link, but let's just keep that between us.

You could also use 'custom' triggers where you explicitly check the returned value. One such scenario might be where you are returning a count from a routine, you may have three valid responses from the routine:
Each trigger branches to different sections of your workflow.

You could also know you will always be returning what the Sequence will consider a bad status from a routine that will never fail and so stick with an single Unconditional trigger.

All food for thought.

Anu,

There must be something else worng ... I dont see anything wrong in routine and keeping trigger as condtional = "Executed ok" ...

Thanks,
Anupam

What's wrong is that Ans must return zero if an OK trigger is expected to fire.

ray.wurlod wrote:What's wrong is that Ans must return zero if an OK trigger is expected to fire. ...

I think Routine_Activity.$Jobstatus="Excuted ok" thats return 0 thats ok

But here is Routine_Activity.$Returnvalue should be equal to 0 is that you mean to say

here i worked with keeping trigger as condtional = "Executed ok" for routine for above example and working fine for me

Routine_Activity.$Jobstatus and Routine_Activity.$Returnvalue both are different

Thanks,
Anupam

Hi Anu123,
Instead of using routine activity use uservariable activity and call your routine there.It works fine.
Vijay

sb_akarmarkar wrote:Routine_Activity.$Jobstatus and Routine_Activity.$Returnvalue both are different

Yes, of course... but Routines do not have a $JobStatus - jobs do.

'Executed Ok' means 'returned a zero'. For jobs it is returned as their status, for routines as their answer.