Any help resolving this issue would be great. I want to create a routine that can check the status of a particular sequence and all the sequences and jobs that are part of that sequence and return a 0 if all ore OK or a 1 if ANY have failed or aborted.
In this case I want a routine that goes through all of the Sequences and Jobs below Sequence 1 and finds out if ANY have failed or aborted. Basically, I want to use this as a check to ensure that all my Dimension loads have been successful BEFORE moving on to load my Fact tables.
Thanks for the reply, I had searched the archives and hadn't found that post.
One additional question, this code seems to work well for what ever job or sequence you pass to it, however, I need to add a recursive aspect to it, I need it to cruise through all the jobs below the master and return the overall status of the sequence and all its dependancies. I don't need a return for EVERY sequence or job below the master, I just need to know if any of the jobs below the master sequence have failed.
Job sequences can easily trap failures. They are designed to do that. If you really want to program something then you need to know how several DataStage files work.
The first is DS_JOBS. It stores the job number. This is used in all the other files. Job number is stored in field 5.
DS_JOBOBJECTS is the next hash file. This file stores the job designs. The begining record is called the "ROOT" record. The key is "J\" : JobNumber : "\ROOT". Field 40 has a "2" in it if it is a job sequence. Field 31 has dependant jobs or routines. Field 32 has a "0" if it is a job and not a routine. These fields are multivalued.
I pull job sequences for DwNav because the Reporting Assistant does not. If you need the code then let me know. Parameters are also stored in the ROOT record fields 13 to 20. Be careful. If you write to these records then you can mess up your job designs. You will also need to recompile the job if you update these records before your changes take effect.
As Ray likes to say -- If you can't find two different way to solve this problem, you're not thinking hard enough...
Another solution is to utilize the Routine - "\sdk\Utility\UtilityAbortToLog" - there could be other Routines you could use instead of this one - within the Routine Activity Stage linked to each and every stage in the job sequencer.
Use either Kim's solution or mine (.$JobStatus = 1 Or .$JobStatus = 2 - I am not sure how to link to the original thread, but it's still active right now)
to send out a success link to the next job, and a reject link to this Routine Activity Stage.
Keep this practice for every Job Sequencer you utilizes, and everyone will know when someone deep inside nested job sequences aborted.
This is also true for 6.x for DataStage.
-T.J.
P.S. Is there a way to attach a picture on this?
Developer of DataStage Parallel Engine (Orchestrate).
