0

In my application, I need a Java process to run by a trigger-- whenever a change has been made to field dateLastChanged of TrackTable, that process should fire.

I followed through these steps for this:

1.) compiled the following code:

public class UDFClass {
public static int udf(String coCode, String tNo) {
    int result = 1;
    String command = "<the java command here>"; // line-K
    try {
        Runtime.getRuntime().exec(command);
    } catch (IOException e) {
        result = 0;
    }
    return result; 
}
}

2.) put UDFClass.class under sqllib/function directory of the DB2 installation

3.) ran the following function on db2. It ran successfully:

create or replace function theresUpdate(cocode varchar(4), tnumber varchar(20))
returns integer 
external name 'UDFClass.udf'
language Java 
parameter style Java
not deterministic 
no sql 
external action

4) successfully ran the following trigger on DB2:

create or replace trigger notify
after update of dateLastChanged 
or insert 
on TrackTable
REFERENCING new as newRow
for each row 
not secured 
begin
update performanceParams set thevalue = theresUpdate(newRow.cocode, newRow.tnumber) where thekey = 'theDate';
end

To test, i update TrackTable as follows:

update  TrackTable  set dateLastChanged =  dateLastChanged + 10 where tNumber = ‘21123’

this update query runs successfully without the trigger in (4) above. However, with this trigger, i get the following error:

An error occurred in a triggered SQL statement in trigger "LTR.NOTIFY".  Information returned for the error includes SQLCODE "-4301", SQLSTATE "58004" and message tokens "1".. SQLCODE=-723, SQLSTATE=09000, DRIVER=4.18.60

This page indicates that it’s a Java related error. However, the command in line-K of the Java code in (1) is running all fine when I invoke it from the Linux command line.

I tried some other variations of the function in (3)-- deterministic rather than not deterministic, not fenced.

I’m using DB2 version 10.5.

Please help!

mustaccio
  • 18,234
  • 16
  • 48
  • 57
user6762070
  • 55
  • 7
  • Yeah, it's a Java-related error, but the error says there were setup problems. It's possible that the problem isn't the command you're trying to run, but just trying to run the UDF itself. What happens if you remove line-K entirely? Oh, and for sure your UDF is `not deterministic`, given you're running a command. – Clockwork-Muse Sep 08 '16 at 01:30
  • Did you try invoking the function by itself, something like `db2 values theresupdate()`? What shows in `db2diag.log` when it fails? – mustaccio Sep 12 '16 at 19:41

0 Answers0