Usage of Control job or Sequencer ?

steven · Post by **steven** » Wed Jul 30, 2003 8:17 am

Hi,

We're currently in the process of building the sequencers for our jobs and get to a point where we're questioning the usability of the GUI to create the jbo sequence versus writing the code in a job control.

Let's say I have 3 sequences (Load Staging, Load DW and Load Datamart). Each of them have a few jobs and the sequencers are dependant of each other because if the load of the staging area fails (ex. Oracle problem), the DW cannot be loaded right away. By default, if a job in the staging fails, the Load Staging sequence finishes with Status 0 (OK). So I'm loosing the handle to start the next load step. To start each of the underlying sequencers, I need a master sequence to start each sub sequencers.

When I create the job sequence (ex. Load Staging), I cannot modify the code which means that I cannot add handles at the job level to control the whole sequence. To do it, I would need to copy the generated code in a control job and add my own control... which means the sequencer is not longer useful (the GUI serves nothing) !!!!

Are most people on the field writing the control jobs or they are using the sequencers ?

Thanks,

Steve

kduke · Post by **kduke** » Wed Jul 30, 2003 8:47 am

Steven

Job sequences work fine. They are not programable within the sequence. You can only trap the return codes of steps and make decisions based on that. They are fairly simple in nature. A lot of people still use batch jobs because they have more control and create restartable processes.

One of the issues of a sequence is that it traps aborts and keeps running. If this is a subsequence then it needs to abort to notify the master sequence that there was a problem. There is a routine you need to write to abort the sequence. I can give you the code if that is your problem. You have to rethink how you do things within a sequence. You do not have the same amount of control as in a batch job. The problem with a batch job is that it is not visual. DataStage wants to create visual solutions. The more code you bury in routines or batch jobs then the more you have defeated a GUI tool like DataStage. The less visual your solution then more effort it takes a developer which follows after you to figure out what you did. Humans are visual people. We can quickly grasp visual solutions. This is the future of programming. Expect tools in the future to force you into a methodology by restricting your choices to a few visual choices.

If I have not answered your question then you need to explain exactly what your problem is.

Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

steven · Post by **steven** » Wed Jul 30, 2003 9:08 am

Kim,

I would like to use the job sequence for the good reasons you mentioned but I'm afraid that DataStage does not offer enough features in it to use it (ex. check box at the sequence property level to specify to abort if a job fails).

The routine you are talking about, do you have an example of it? How do you call it... I guess through a RoutineActivity? Do we need to add a RoutineActivity on every job? (ex. Job A runs and if OK, it triggers Job B and if Fails or Warning, call RoutineActivity to abort the sequence. Then have the same for Job B...)?

Steve
steve.nadeau@sqliaison.com

kduke · Post by **kduke** » Wed Jul 30, 2003 12:07 pm

Steven

Rick Schirm had the most elogant solution I have seen so far. He would create 2 results for each job ran. The first was a failed path. It went to a Failed sequence then to an email sequence then he called a ForceFail(JobName) routine. The only thing in the routine was 2 lines:

Call DSLogFail(JobName, 'Job Failed.')
Ans = ''

Next was an "OK" path. This was "Otherwise" in the Triggers window. So all the results were trapped. Nothing fell through the cracks. This path would run all the jobs in the order he wanted them to run.

Next he kept his sequences small to under 7 if possible. They get hard to read if you have more than 15. I have seen 64 in one sequence and guess what it was wrong. It used a hash file before it was built. That meant that the data was validated against old data. On a code file this may not be much of a problem because it does not change much and when it does it fixes itself the next time your run but it is still wrong. You find an error in 64 sequences. It will take you a while.

Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

steven · Post by **steven** » Wed Jul 30, 2003 1:45 pm

Kim,

Your solution is what I've tought but I wanted to have a second and third opinion because graphically, it's not pretty and code wise, it's heavy. But if it's the best we can do right now... we'll go that way expecting that DataStage will improve the Sequencer.

Thanks for your help.

Steve

kduke · Post by **kduke** » Wed Jul 30, 2003 2:23 pm

Steve

I don't think they will change it. It is very flexible. I do not think trapping errors and then forcing that sequence to fail so the previous sequence sees the failure is in any way difficult. It does become cluttered in the Designer because each job has 2 paths but all the error paths go to the same sequencer then email then force failure. I like this solution. There is a lot of power and control with this methodology.

Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

ray.wurlod · Post by **ray.wurlod** » Wed Jul 30, 2003 5:34 pm

I grew up writing my own Job Control code (from version 1), so tend to prefer that. It gives me total flexibility with what I want to do, for example a "busy wait".
I will use job sequences where my clients prefer that approach; sometimes directly for production, sometimes merely to prototype (then copy/paste the generated code into a new job).
As a matter of style I find that the variable names in code generated by job sequences to be ugly, and the use of jumps (GoTo statements) less than desirable. But that's just a personal opinion - it's definitely easier to generate that kind of code, but my brain and its >20 years experience with the language can do better.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

kduke · Post by **kduke** » Wed Jul 30, 2003 9:14 pm

Ray

You got that right. The code generated by a job sequence is the ugliest code I have ever seen. I have been using Universe since 1986 and the DS_JOBOBJECTS table is a mess. It is sad that they were not better Universe developers but DataStage works great. I love Universe. I think it is also sad that they are going to do away with Universe. It has always amazed me how bad or average developers can create good solutions with Universe. It is one of the most underrated databases ever.

Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

raju_chvr · Post by **raju_chvr** » Tue Oct 07, 2003 9:22 am

Can someone tell me how do you pass the job name to the routine that is being called in case job aborts.

I am using Rick Schirm solution as suggested by Kim Duke. I was not able to pass it as parmater. If I do that I am having compliation problems.

Thanks

PS: I got this, all I have to do is enclose the job name in quotes in the trigger paramter values

kduke · Post by **kduke** » Tue Oct 07, 2003 11:56 am

Raju

Your routine needs one argument named "JobName". Your sequence needs to call this routine like ForceFailure("MySequenceName"). Where "MySequenceName" is the current running job sequence. If you have a sequence which runs another sequence then this will let the original sequence know that the child sequence failed.

Kim.

Kim Duke
DsWebMon - Monitor DataStage over the web
www.Duke-Consulting.com

ray.wurlod · Post by **ray.wurlod** » Tue Oct 07, 2003 10:25 pm

The job that is called can always determine the name of its parent with a call to DSGetJobInfo(DSJ.ME, DSJ.JOBCONTROLLER).
There's probably also a DSMacro for this; I don't have access to DS this week.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518