calling BODS job from other BODS job

dataservices job design
1

Hi,

I’ve 3 BODS jobs which are dependent on one another. 2nd job should run after 1st one is completed. similarly 3rd one should start after 2nd one is completed.

I know this can’t be done through BODS scheduling. is there any other way around ?

Thanks

learndi_2011 (BOB member since 2011-03-03)

2

Put the 3 jobs as workflows instead and link them sequentially in a single job.

nithya_raj (BOB member since 2007-02-03)

3

this is applicable if we have 2-3 jobs.

If we have around 10-15 jobs, we can’t put all those into a single job.So, i am looking for a generalized solution to this kind of issue.

Thanks

learndi_2011 (BOB member since 2011-03-03)

4

One approach would be…

Maintain an audit log table, record execution status for each job.
Update the audit log table with appropriate flag on successful execution or failure.

In the beginning of the each job check status of the dependent job.
If the dependent job’s execution was successful then run the job else raise error, update audit log table, send email alert to user and exit.

And schedule the jobs with appropriate time interval.

kdspvp (BOB member since 2007-10-15)

5

seems to be a good option.

But the problem is, we need to schedule the 2nd job at a particular time and let it check the status of 1st job. If the 1st job is completed in 5mins itself (which normally takes 1 hr), then the 2nd job still runs at schedule time only. instead it should start immediately to save the cycle time.

learndi_2011 (BOB member since 2011-03-03)

6

Hmmn, I think you can call another job using cmd.exe on successful execution of current job.

kdspvp (BOB member since 2007-10-15)

7

Yeah…thats what i am thinking.

but the 1st job status will be ‘Running’ until all the child jobs are finished.

learndi_2011 (BOB member since 2011-03-03)

8

Yes, thats correct.

kdspvp (BOB member since 2007-10-15)

9

I think all the options are exhausted for you. :frowning:

But by any chance you are using a 3rd Party Scheduling Tools, then they would make your life lot simpler.
So in your case are you using the DI native scheduler? One other thing you could do by adding to KDSPVP’s idea. You could have one datetime constant in your job/or getting that datetime constant even from a control table and checking whether the system time is greater than that time. By that way you could use sleep option or even abort the job.

But remember by these ways things get too complex when the failure occurs :twisted:

ganeshxp :us: (BOB member since 2008-07-17)

10

We used to schedule the jobs through IBM Tivoli in my previous project. the dependency used to take care of it automatically.

But in the current project,we don’t have any 3rd party tools :hb:

learndi_2011 (BOB member since 2011-03-03)

11

Well
If I have to implement solution to this issue, I’ll use web-service using WSDL of your job server http://:/DataServices/servlet/webservices?ver=2.1&wsdlxml

It has function Run_Batch_Job, which can be invoked in DF using Query. To check status I’ll use Get_BatchJob_RunIDs function.

soulsurfer :belarus: (BOB member since 2010-04-06)

12

Hi,

there’s a fairly simple way to achieve this.

Create an admin database that contains run times/success records etc for your jobs, so you can query this and know what job is running/has completed/errored etc.

Now, have a job that periodically queries this admin db, either in a while loop or a schedule that repeats regularly, whatever suits.

that scheduled/looping job can start job 1, then next time runs it can query the admin db and decide whether to start job 2, or not.

That way, if job 1 runs for 1 minute or 1 hour job 2 will start shortly afterwards.

You can create as many jobs as you like in the admin db and it will just fire them one after the other.

HTH.

wil992 (BOB member since 2011-06-22)

13

At this point, the best solution seems to be to create each job as a workflow and run them one after another in a master job.

The first response in the post is the easiest to implement and gives you everything you need, regardless if there are 3 jobs or 15.

Seems like other options bring in a large level of extra effort/complications.

djordan :ireland: (BOB member since 2006-03-01)

14

I guess i depends how much flexibility is equired by the schedule.

I got the impression from the OP that the amount of data each job processes changes quite a lot from run to run, so some flexibility in the order in which things execute would be useful.

If you chain the workflows together they will run in sequence, one at a time, and this could take a while. By executing them as soon as soon as their dependent jobs have completed you could save a lot of time on the overall process.

I think it’s a bit mich to describe setting up a table with a job name and current status as “a large level of extra effort/complications”, but you’re correct in that it is more complex and if there are no restircions in terms of batch windows or availability of source data then that would be the way to go.

wil992 (BOB member since 2011-06-22)

15

I know it’s a bit late, but I’m interested to know why you say linking 10 - 15 workflows in a single job would be a problem. This would be the simplest and most effective solution to your problem. There are many other solutions but why add complexity and effort. I live by the ‘keep it simple’ principal :slight_smile:

ClintL :south_africa: (BOB member since 2011-01-06)

16

There are 3 main issues imho - and again it does come down to the flexibilty required, I agree entirely that the simplest solution that meets the requirements is the one to go for.

First of all, the wf’s within a job can only run in 1 given order, so wf2 will always have to wait for wf1, even if there’s no specific reason for this.*

Secondly, if the job fails, none of the target tables after the failure point will be loaded, whereas with multiple smaller jobs they could continue and leave only afftected job(s) needing to be recovered.

Thirdly, folowing a failure, it’s easier to recover jobs that haven’t yet run than it is to restart a job half way through its run, unless you have included restart logic in the job design, which makes the big job more complex and to a certain extent defeats the purpose.

*yes, you can nest multiple wf’s within another wf to make them run in parallel, but even so, that structure/running order is set in stone and can’t be changed at run time.

wil992 (BOB member since 2011-06-22)

17

Adding to wil992,
maintaining a huge chain of jobs as a DI Administrator would be something very challenging one. By dumping too many items to one job you have to crack your head and read the Trace Log (provided you don’t have suitable exception handing. Even if we have e-mail sending policies, every DI Administrator would definitely have to go look into the entire Trace Log of the job. Everyone who do this will agree to me :expressionless: And remember if this pops in your mailbox by 3:00 AM. Then that would be a wonderful day for you :reallymad: [Writing this after many sleepless nights])

ganeshxp :us: (BOB member since 2008-07-17)

18

Ok thanks, I see your problem. The suggestion I would have for you then is a variation of the ‘control table’ solution proposed by kdspvp
, but which adds the flexibility of changing the sequence without touching code and also allows each job to start as soon as the dependant has completed, as follows:

  • Create a control table with Job_Id (1…nnn), Job_Name, Dependant_Id, Status
  • Set Dependant_Id to 0 for whichever job runs first
  • Set Dependant_Id accordingly for the other jobs
  • Status: 0 =Not Completed, 1=Completed
  • Create a reusable workflow containing a while loop with a suitable sleep period that you can place at the start of each job.
  • Create a script before the while loop to update the status for the current job to ‘0’ (use job_name function to match to record in control table)
  • Create a script inside the while loop to first read the dependant_id and then read the status of the job with that Job_Id, then the sleep function. The while loop ends when the status of the dependant is ‘1’ while loop can end and the current job continues processing.
  • Have one more reusable workflow at the end to set the status to ‘1’ when the job ends, and also in a try-catch for each job so that if the job fails it will still set the status to ‘1’ so the next job can continue.

Hope this is what you’re looking for.

ClintL :south_africa: (BOB member since 2011-01-06)

19

clint, yes, that’s a pretty good suggestion, quick question though.

If my understanidng is correct, all of you jobs will start at once (started by an admin console schedule?) and then pause while they wait for the job on which they are dependent to complete, identified by its status being set to 1?

Once this happens they’ll jump out of the loop and start processing?

If I’ve understood that correctly, you’d need to move the resetting of the status codes out of the jobs themselves, as every job before it starts will have a status of 1, meaning its child jobs are able to start if they happen to be executed first by the scheduler.

I’d have a kind of ‘reset’ job scheduled to run 5 minutes before the main batch that set the status of all the jobs to 0.

I’d also be tempted to have more than 2 status codes; adding some in for jobs that are currenlty running and jobs that have errored.

Also, if a parent job errors, you might want the child job to stop, as you may not want, for example, a warehouse load job to run if the job that stages its data from a source file has failed. That way, at the end of the batch, all jobs would have stopped even if there have been errors.

There’s no doubt plentyof other enhancement you could do to make it a pretty effective solution. :+1:

wil992 (BOB member since 2011-06-22)

20

Yes, you’ve understood it correctly. Apologies, I meant to address the ‘initial status’ issue but had to rush to a meeting, and forgot :cry:

A reset job to set the status to ‘0’, scheduled 5 min before the others is a good solution to that problem.

And yes, lots of scope for enhancement according to your needs.

Glad I could help.

ClintL :south_africa: (BOB member since 2011-01-06)