Error handling and job scheduling without using Control tables on database

[Former Member]

In BODS, we sometimes face requirements like executing Job1 upon successful completion of which, Job2 should be triggered and so on for Job3.

(In data warehousing, we may have jobs that load dimension tables and some jobs those are loading fact tables. So we need to schedule jobs such that all the dimension jobs should get completed successfully, on the basis of which fact loading jobs can be started)

In order to fulfil such requirements, we have 2 approaches.

6.1 Using control tables on database side:

We can create a control tables which will maintain the status of jobs running and same are updated with the BODS jobs.

6.2  Using BODS internal tables:

BODS internally maintaining Metadata tables in which we can find job status whether it is running,failed or successfully completed.

AL_HISTORY table is the internal table maintain by BODS, having information related to jobs that are running.

Table structure is as follows:

Column Name	Description
Object Key	Internal ID of the job within the repository
INST_MACHINE	Computer on which the job was executed.
TYPE	Batch job or a real-time job.
SERVICE	Name of Job
START_TIME	Time when job execution started
END_TIME	Time when job execution completed.
EXECUTION_TIME	Difference between start and end time.
STATUS	Job status: While job is running S , Completed D and if there is a error in job then Status would be E.
HAS_ERROR	0- If there is no error & 4 - If there is an error.

This metadata table of BODS consists of all the job status whether they are running or completed or terminated because of some error.

I have created a test job and I will show how to get job status from AL_HISTORY table.

The job name is: J_ERROR_HANDLING.

When I run the job and trigger a query such as

SELECT * FROM al_history K
WHERE upper(k.service) = 'J_ERROR_HANDLING');

, my result set is as follows:

If I wish to get status for latest instance then following query needs to be triggered:

SELECT * FROM al_history i
WHERE i.object_key IN (
SELECT MAX(k.object_key) FROM al_history k
WHERE upper(k.service) = 'J_ERROR_HANDLING');

Result:

So by using AL_HISTORY table, we can easily get latest instant of any job running and using that status we can schedule other dependent jobs.