0

I have a trigger on one of the DB2 tables. What i need is, every time that trigger runs, it should invoke a Linux shell script.

How to do this-- same as any other process? If my trigger was to invoke a Java process instead of a shell script, putting the bytecode (.class file) of that process to ..SQLLIB/function and defining a function for it would do the job.

Is this much different for Linux script, any subtleties? I don't have Linux for another few days but deployment is right around the corner and nervous at it just the same.

TIA.

Improve this question

2 Answers 2

Reset to default
2

You cannot invoke a shell script from SQL, including from a trigger. What you can do is create a Java UDF the way you described, then use Runtime.exec() to invoke the script.

Note that your approach, apart from introducing security risks, can affect data consistency (a transaction can be rolled back, but you cannot "unrun" the script) and concurrency (a transaction with all its acquired locks will have to wait until your script returns).

A better approach would be to use an asynchronous process to decouple the external action from the database transaction. As an example, your trigger might insert a record into a log table, and the external process would read that log table on schedule, perform the required action when a new record is found, then delete the processed record.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Co-sign. I can't stress how much I would avoid having the database start forking external processes. So much better to have a daemon running that polls for events or uses a message queueing solution. DB2 includes functions that can write directly to Websphere MQ (or whatever it's called now).
1

There is an good article about Making Operating System Calls from SQL which includes sample code.

Improve this answer

4 Comments

i'll see whether the one i crafted just now runs. thx for the useful source. have you done a thing like this on Linux Redhat before? DB2 is 10.5.
Not using Java but using it to call an ETL process when data gets inserted into a table - yes
did you ever get around the location ..SQLLIB/function? that's the one spoiling it for me. if the resource didn't have to be sitting in that directory, a lot of things would be easier for me
Well you need a location which DB2 can access - and sqllib/function is the usual path for thse things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.