How to kick a job with the changes in database table

[akonda]

My Datastage job has to start running automatically based on database changes.

Example :

The default value is 'N' in table and When Flag is updated to 'Y' in the table, the datastage job need to start running automatically.

pls provide your suggestion on this scenario.

Thanks.

[DSguru2B]

What database are you using and what scheduling tool is at your disposal?

[akonda]

we are using oracle database and CRON scheduler.
Is it possible without using scheduler. ??

[DSguru2B]

Well without using a scheduler would be a sloppy solution that would require you to check for the status of the flag every x minutes.
I am not sure with Oracle, but with DB2 and ControlM/Control-O combo, you can have event driven tasks. I am currently implementing the exact same thing. Control-O recognizes the event, trigerrs ControlM which in turn triggers my jobs.
With Oracle, I wasnt too sure so I asked my Oracle DBA here, he said it is possible but he needs some time to dig up the info. As soon as he gets back to me, I can chime in on the specifics.
In the mean time, try searching in google, for event driven tasks.

[Sainath.Srinivasan]

You can write a trigger on the table on update, which can run DataStage job.

[DSguru2B]

My DBA says that in 10g there is a database feature where you can create a "Job" which can run host commands. So if you have Oracle 10g then you can have a trigger on that particular column which will execute this "job", in which you can execute OS level commands.
Prior to 10g, there is no easy way of doing it.

[Sreenivasulu]

As Sainath said you can use 'on update triggers' in oracle.. This is generally used for audit or reconcilation purposes but here you can very well use it to trigger jobs

This has been there in oracle since a long time

Regards
Sreeni

[akonda]

Yes we are using Oracle 10g.
Could you please provide me the navigation and the code to create a job in the trigger to fulfill this requirement.

Appreciated your help .Thanks.

[akonda]

Can somebody tell me that how to call a datastage job into a database TRIGGER please.?


Thanks

[chulett]

You're not going to want to trigger the job directly from there unless you're willing to simply launch it without waiting for it to complete or being able to monitor its success or failure.

Just how tightly coupled do these processes need to be?

[Nagaraj]

I guess you cannot call a DS job in a DB trigger all you can do is write a shell script where in you can connect to DB query based on the result run your job, and schedule this script on cron or on control-M,

i have no idea about Control-o.

Thanks

[vinnz]

Yes we are using Oracle 10g.
Could you please provide me the navigation and the code to create a job in the trigger to fulfill this requirement.

Appreciated your help .Thanks.

A simple google search for "oracle 10g event based job" yielded some good starting points ..for example http://www.oracle.com/technology/pub/ar ... html#event
Hope that helps.

If you manage to resolve this, please post your solution if possible for the benefit of others.

[Nagaraj]

The below mentioned article is for the Jobs i.e the PL/SQL programs created in Database. Not suitable for DataStage Jobs.
It would be more complex if we take this as the starting point to solve the above query.

[ray.wurlod]

As I see it you have two choices - to create triggers in the database that eventually invoke dsjob to run the DataStage job, or to have a DataStage job that polls the table in question as part of a sequence, perhaps.

[changming]

If a change is detected, then kick off datastage job.