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.

user6401178
  • 183
  • 2
  • 11

2 Answers2

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.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • 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). – Ian Bjorhovde Aug 24 '16 at 23:03
1

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

MichaelTiefenbacher
  • 3,805
  • 2
  • 11
  • 17
  • 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. – user6401178 Aug 24 '16 at 21:00
  • Not using Java but using it to call an ETL process when data gets inserted into a table - yes – MichaelTiefenbacher Aug 24 '16 at 21:07
  • 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 – user6401178 Aug 24 '16 at 21:24
  • Well you need a location which DB2 can access - and sqllib/function is the usual path for thse things. – MichaelTiefenbacher Aug 25 '16 at 05:22