User Status in Check Pointed Job Sequence

[visvacfirvin]

Hi,

I've two job sequence like below. Both have check point option set.

Code: Select all

Seq 1:
                          ------ Job Termination ( Failure)
                         |
     Job Activity --- 
                         |
                          ------ Setting User Status as 0 using routine.

Seq 2:
Job activity has seq1 as the job.
                          ------ Setting User Status as 0 using routine
                         |
     Job Activity --- 
                         |
                          ------ Setting User Status as 1007 using routine.

The constraint for Success link is Userstatus =0 and failure link is Otherwise.

Seq2 is the master sequence calling Seq1. When the job in Seq 1 fails, it triggers Job Termination activity. This causes the master seq to fail with user status as 1007. Once the error is rectified and restarted, the first job completes successfully but the master seq still returns userstatus as 1007 instead of 0.

Can you please clarify on where i'm making mistake. This is simple prototype of our job. We're facing similar issue in one of our big job.

Thanks,
Firvin

[kandyshandy]

Your code says that Seq 2 is the master sequence which internally triggers Seq 1. So Seq 2 is the master sequence. But your explanation tells that Seq 1 is the master sequence. Anyways, that should not stop us from assisting you. Can you enable "Do not Checkpoint" for the routine stage and try again?

[chulett]

That seems a little... over-engineered to me. Why all the extra user status shenanigans? You should be able to do this using 'regular' job status checks.

[visvacfirvin]

Thanks kandyshandy. Yea Seq 2 is the master seq. updated the post.

@Chulet, for handshake between different technologies like Java/SAS/Unix scripts we had to define our custom error codes. Is there anything wrong with the design or datastage is supposed to work like this?

[chulett]

Not quite sure what 'work like this' means. Are you certain that your triggers are firing like you think they should? Meaning, in Seq2 whether or not Seq1 finishes correctly it sounds like it may always be taking the Otherwise link. Have you added any 'logging text' to each stage to know which path it actually takes? Are you confident that your user status routine works correctly in all circumstances?

[visvacfirvin]

Chulett, I actually meant if the UserStatus will never get reset in case of checkpointed sequence. I guess similar question was asked once and you adviced to use jobstatus instead of userstatus.

kandyshandy, I did try that. Its not working.

Not sure how to get updated value of userstatus.

[chulett]

Recompiling a job 'resets' it, otherwise it retains the value last put there until another takes its place. And you didn't really answer or respond to any of my questions.

[kandyshandy]

So, can you check whether the routine successfully updates the user status? Even after enabling "Don't check point" for routine stage, if it does not update the user status, i doubt the routine.

Check in the log to see whether the routine gets executed after Seq1 completes successfully.

[visvacfirvin]

If the first seq completes successfully, everything works fine and routine returns userstatus as 0. Issue is only when it is restarted after failure.