Professional Web Applications Themes

invoke external JavaUDF from DB2 Trigger - IBM DB2

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.*; import java.io.*; 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 ...

  1. #1

    Default invoke external JavaUDF from DB2 Trigger

    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.*;
    import java.io.*;

    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
    madhu Guest

  2. #2

    Default Re: invoke external JavaUDF from DB2 Trigger

    Hi again,

    I was trying this under "owner" login. later I tried with "db2admin"
    login. It worked fine and i am able to invoke the javaUDF and got
    returned value.

    But when i modified the code(UDFjsrv.java), recompiled it and put the
    classfile under "C:Program Files\IBM\SQLLIB\FUNCTION" (overrited) and
    run the trigger again, it is executing old class file only. new
    changes are not got executed. I think it is taking from old class
    only. but I deleted the old one and created new class file. there is
    no other class file in the system with same name.Is DB2 searching
    somwwhere else?
    I even tried to create new JavaUDF with different function names and
    same code. that is also not working. i followed the same procedure.
    looks crazy and silly.

    pls help me out.

    thanks
    madhu


    [email]emadhusudhanrediffmail.com[/email] (madhu) wrote in message news:<61962a37.0307010006.37ab9d97posting.google. com>...
    > 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.*;
    > import java.io.*;
    >
    > 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
    madhu Guest

  3. #3

    Default Re: invoke external JavaUDF from DB2 Trigger

    Did you copy the file over to sqllib/function?
    See what happens if you recycle (stop/start) DB2 (refresh the library?)

    Cheers
    Serge

    --
    Serge Rielau
    DB2 UDB SQL Compiler Development
    IBM Software Lab, Toronto

    Visit DB2 Developer Domain at
    [url]http://www7b.software.ibm.com/dmdd/[/url]


    Serge Rielau Guest

Similar Threads

  1. Invoke from unmanaged code
    By Mircea Pleteriu in forum ASP.NET Web Services
    Replies: 1
    Last Post: February 24th, 04:23 AM
  2. Can Perl invoke DUN on Win XP ?
    By Sunish Kapoor in forum PERL Beginners
    Replies: 0
    Last Post: September 24th, 01:18 AM
  3. Invoke DUN on Win XP
    By Sunish Kapoor in forum PERL Beginners
    Replies: 0
    Last Post: August 15th, 08:05 AM
  4. how to invoke a method in a DLL
    By Lloyd Dupont in forum ASP.NET General
    Replies: 3
    Last Post: July 13th, 03:57 PM
  5. Cannot invoke web service
    By Velickovski Zoran in forum ASP.NET Web Services
    Replies: 0
    Last Post: July 9th, 11:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139