Skip to Content

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:


E1.jpg

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:

E2.jpg

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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply