invoke external JavaUDF from DB2 Trigger

[emadhu]

Hi,

I am trying to invoke external JavaUDF method from a DB2 trigger.

trying one basic program. this is what i did,

1.create UDFjsrv.java
*********************************************
import java.lang.*; // for String class
import java.io.*; // for ...Stream classes

public class UDFjsrv
{

public static double scalarUDF()
throws Exception
{
double outNewSalary = 100 * 1.20;
return outNewSalary;
}
}
*************************************************

2.compiled and put the class file under ibm\sqllib\funtion directory.

3.registered the function under DB2.
*******************************
CREATE FUNCTION scalarUDF()
RETURNS DOUBLE
EXTERNAL NAME 'UDFjsrv!scalarUDF'
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT VARIANT
FENCED
CALLED ON NULL INPUT
NO SQL
EXTERNAL ACTION@
*********************************

4.then creating one DB2 trigger (after insert)
**************************************
CREATE TRIGGER OWNER.NOPARAMTRG AFTER INSERT ON OWNER.EMPLOYEE REFERENCING NEW AS new NEW_TABLE AS NEW_TABLE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
update employee set salary=scalarUDF() where name=new.name;
END
********************************************

5.then when i am trying to insert a record into the database table it is giving me an error,
***********************************************************
db2 => insert into employee(name, salary) values('vishal', 12)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0723N An error occurred in a triggered SQL statement in trigger
"OWNER.NOPARAMTRG". Information returned for the error includes SQLCODE
"-4306", SQLSTATE "42724" and message tokens
"OWNER.SCALARUDF|SQL030630211921300|scalar". SQLSTATE=09000
db2 =>
************************************************************

i checked the SQL error code(4306), it is aying something about wrong argments or datatype mismatch. but i feel my code is correct.

my classpath variables are set for runtime.zip,sqlj.zip,db2java.zip.


please help me out.

thanks
madhu