Trigger DS job from a table

[kennyapril]

Hello everyone,

My requirement is the DS job needs to be triggered when ever a record is inserted in a table. Once the job is triggered it will process transformation for that inserted records.

Please suggest how to trigger the job when ever a new record in inserted in a table.

Thank you

[asorrell]

You neglected to give any information regarding your database type, which is important because this is really more of a database question.

The only way I know of to trigger anything based on the addition of a record is through an actual database trigger on the table. I'm going to limit my response to Oracle as an example, but you need to check what your particular database is capable of.

In Oracle you'd have to setup the trigger to execute a PL/SQL command that uses EXTERNAL PROCEDURE, JAVA, or DBMS_SCHEDULER to execute a local script. That local script would need to use an authenticated ssh session to run the dsjob command on the remote system with the appropriate arguments.

Please note, if your DBA is a database purist they may strenuously resist putting a trigger like this in place, as it isn't a normal / sanctioned methodology.

There are also some considerations you'd need to cover - for example what do you do when 5,000 rows are inserted? The trigger will be executed 5,000 times, which will probably crash and burn when it tries to execute the DataStage job 5,000 times in under a second...

There are also security issues that could prevent adding a pre-authenticated ssh capability between the systems.

Better alternative
A better method would be to have the trigger create a notification file on a file system the DataStage system can see. Then create a job sequence with a Wait For File stage that will remove the notification file and kick off the job. You can have that run in a loop so it finishes then immediately goes back to wait on the file. There's still some logistics with scheduling / running / restarting, but its a cleaner method.

With that method you aren't really kicking off the job directly (there will be a short time lag until DataStage "sees" the file), but it resolves the "scheduled it too fast" problem as well as the ssh authentication problem.

[chulett]

Perhaps "the DS job" that needs to be triggered should be deployed as a web service?

[stuartjvnorton]

Time to revisit the "requirement".

[kennyapril]

Thank you very much for the information.

I will work on implementing the better alternative. Will ask the DBA if the trigger can create a file on DS server and then once DSjob sees the file it will trigger the job.

Please let me know if I understood it properly.

@Chulett we do not have ISD in our environment, How big is that thing to get it from IBM....please suggest , we may use it for some purpose if needed.

Thanks again!

[chulett]

What 8.x release are you on? I thought the WISD stuff was all included now.

[asorrell]

Still a chargeable add-on. It now ships with the product and its the company's responsibility to remove it if they don't own it.

... or get dinged and have to pay for it later during an audit!

[kennyapril]

We are on 8.5 but we do not use ISD for any purpose.

Thank you!