Job Control Table - Part 5 - Sequencing Jobs

One of the things missing from Data Services is the ability to chain jobs together so that as one job finishes the next one starts. While you could put the functionality of each job into a workflow and then create a large job that executes the whole thing, what if you don't want that?

Often it's safer to have a few little jobs, that way if one little job fails the whole process is not brought down, just that one job. Also, we might have a job that loads all the dimensions, and many jobs for the various fact tables. Another scenario could be if you need to load an ODS first, and then build data marts on that ODS data.

Well as the DS scheduler doesn't give us the option of creating job dependencies, I have created my own method, using the SYS_AUDIT_JOB table as my guide.

As seen in the previous posts on the job control table, we can always tell by looking at the SYS_AUDIT_JOB table whether or not a job has run on a particular day. We have a start time, and end time, and we have a job status of STARTED, COMPLETE or FAILED. So we can use this information to create functions that will allow us to make a job wait until a previous job has completed.

The main function I have created is called WaitForJobDependency. This stops a job from executing until its prior jobs has completed. I call it in my Initialise script like this:


#Wait for prerequisite jobs to complete
WaitForJobDependency('JOB_ODS',$G_Override_Dependency );
WaitForJobDependency('JOB_DIMENSIONS',$G_Override_Dependency );

So the above is tell my job to wait for 2 jobs to finish before it runs. The global variable is there in case I want to override the wait, and make my job run immediately.

The function looks likes this:

#First check if the override value is set, if its been left off them assume its false. If true jump to the else part of the if statement
$P_Override = nvl($P_Override,'FALSE');

if ($P_Override = 'FALSE')
begin
#If the status of the previous job is not COMPLETE, FAILED or ERRORS then wait another 5 minutes and check again. The job will continue checking indefinitely until one of the job statuses are met.
while ( PreRequisiteJobStatus($P_PreRequisiteJob) != 'COMPLETE'
   and PreRequisiteJobStatus($P_PreRequisiteJob) != 'FAILED'
and PreRequisiteJobStatus($P_PreRequisiteJob) != 'ERRORS')
begin
print('Dependency job ' || $P_PreRequisiteJob || ' status: ' || PreRequisiteJobStatus($P_PreRequisiteJob));
print('Waiting for job ' || $P_PreRequisiteJob || ' to complete.');
sleep(300000);
end

#If the job status of the previous job was FAILED or ERRORS then it failed, and this job will fail too to prevent you further corrupting that data by running your job of of incomplete data.
if (PreRequisiteJobStatus($P_PreRequisiteJob) = 'FAILED' OR PreRequisiteJobStatus($P_PreRequisiteJob) = 'ERRORS')
begin
print('Job dependency ' || $P_PreRequisiteJob || ' failed  or contained errors.');
print('Aborting current job.');
raise_exception( 'Job dependency failed');
end

#If the previous job came back as complete, then you are good to start your current job.
if (PreRequisiteJobStatus($P_PreRequisiteJob) = 'COMPLETE')
begin
print('Dependency job ' || $P_PreRequisiteJob || ' status: ' || PreRequisiteJobStatus($P_PreRequisiteJob));
print ('Starting current job.');
end
end
else

#This just lets the user now that they put the override dependency into effect and the job will start immediately without checking for any previous jobs.
begin
print ('Dependency Override in effect. Will begin job immediately.');
end

Return 1;



You May have noticed i referenced a function called PreRequisiteJobStatus above. This a fairly simple function that just returns the status of the prerequisite job like this:


$L_Status = nvl(sql('DATAMART_SQL_TARGET_DS','
Select STATUS from dbo.sys_audit_job where JOB_NAME = {$P_PreRequisiteJob}
                                             and cast(JOB_END_DATETIME as DATE) = cast(GETDATE()as DATE)
and AUDIT_ID = (Select Max(AUDIT_ID) from sys_audit_job where JOB_NAME = {$P_PreRequisiteJob})
'), 'WAITING');
Return $L_Status;


Now all you need to do is schedule all your jobs to start around the same time, and these functions will make sure that they run in the correct order.