Skip to Content
Author's profile photo Former Member

Error handling and job scheduling without using Control tables on database

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


Object Key

Internal ID of the job within the repository


Computer on which the job was executed.


Batch job or a real-time job.


Name of Job


Time when job execution started


Time when job execution completed.


Difference between start and end time.


Job status: While job is running S , Completed D and if there is a error in job then Status would be E.


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’);



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.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.