Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: UDF/trigger error

    Hi All:

    I am running DB2 UDB V7.2 with FP 7 on Windows 2000. I have created a
    very simple, trivial, UDF with a trigger. When the trigger is
    activated via an update, I get the following error:

    An error occurred in a triggered SQL statement in trigger

    "VAP.TESTUDF". Information returned for the error includes SQLCODE
    "-4301",

    SQLSTATE "58004" and message tokens "-4301". SQLSTATE=09000

    I have done the following:

    db2 update dbm cfg using JDK11_PATH c:\progra~1\sqllib\java\java12\jdk
    db2 update dbm cfg using JAVA_HEAP_SZ 4096
    db2set DB2_USE_JDK12=1
    db2stop
    db2start




    The first error that appears in my db2diag.log (diaglevel=4) is the
    following:

    Java interpreter is running.

    Data TitleQLCA PID:1596 TID:2260 Node:000
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -487 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQLERIAR
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:


    Data TitleB2RA PID:1596 TID:2260 Node:000
    5351 4c44 4232 5241 2c00 0000 0501 0100 SQLDB2RA,.......
    0e00 0200 8800 0000 0900 0080 fcfd 8201 ....... ...
    1400 0000 0400 0080 1eff 8201 .........


    2004-02-10-14.35.52.654000 InstanceB2 Node:000
    PID:1596(db2syscs.exe) TID:2260 Appid:*LOCAL.DB2.040210193436
    BSU Java support sqlejAttach Probe:50 Database:MEH

    Failed in JNI RegisterNatives. Index:
    2000 0000 ...

    Can anybody give me a clue as to what the problem might be. Please advise if more info would be useful.


    Thanks in advance.

    Marv Libson

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: UDF/trigger error

    SQL0487N Routine "<routine-name>" (specific name
    "<specific-name>") attempted to execute an SQL
    statement.

    Explanation: The program used to implement the body of a routine
    is not allowed to execute SQL statements. This routine
    "<routine-name>" (specific name "<specific-name>") contains SQL
    statements.

    User Response: Remove any SQL statements then recompile the
    program. Investigate the level of SQL allowed as specified on
    the statement defining the routine.

    sqlcode: -487

    sqlstate: 38001
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2004
    Posts
    10
    Thank you for responding Sathyaram, but I still don't see the error of my ways. My UDF is trivail:

    import COM.ibm.db2.app.*;

    import java.io.*;

    /**
    * This is a example of using FtpBean.
    * It connects to a ftp server. It put a binary
    * file named pclotid in the AS400 trigger
    * directory which serves as the trigger for
    * sending the wafer data to the AS400.
    */

    public class testudf extends UDF
    {
    private static PrintWriter out;

    public static void testudf(String pclot) throws java.lang.Exception
    {
    out = new PrintWriter(new FileWriter("e:\\javaexs\\pdtftp.out",true),true);

    out.println(pclot);


    }
    }

    The UDF is registered as:

    create function testudf (varchar(10))
    returns integer
    language java
    parameter style java
    not fenced not null call
    no sql
    external action
    external name 'javaUDFs.testudf'

    The trigger is created with:

    create trigger vap.testudf
    after update of tool_type on vap.equipment

    for each row mode db2sql

    values (testudf ('0123456789'))

    I can't see how the error message you posted is relevant, i.e., SQL commands present. Please elaborate. Thanks.

    MarvL

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Your db2diag.log file gave an SQLCODE -487 which is the one I posted ...

    Are you able to call your UDF outside the trigger ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2004
    Posts
    10
    Sathyaram:

    Thank you for responding again. To answer your question, no.

    db2 values(testudf('0123456789'))

    returns:

    SQL4301N Jave interpreter startup or communication failed, reason code "-4301" SQLSTATE=58004.

    I've tried everything I could come up with from searching the forums without success. Could it be a classpath problem? I've noticed in some of the forums, folks have sqlj in their classpath and I don't. Is it required? Any ideas?

    Thanks.

  6. #6
    Join Date
    Feb 2004
    Posts
    10
    Here is what is in my db2diag.log from the
    db2 values(testudf('0123456789'))
    command.

    2004-02-11-13.38.05.045000 InstanceB2 Node:000
    PID:1172(db2syscs.exe) TID:2364 Appid:*LOCAL.DB2.040211170147
    BSU Java support sqlejCallUDF Probe:101 Database:MEH

    C:\Program Files\MQSeries\JavaJMS\Lib;.;C:\Program Files\SQLLIB\java\db2java.zip;C:\Program Files\SQLLIB\java\runtime.zip;C:\Program Files\SQLLIB\bin

    2004-02-11-13.38.05.055000 InstanceB2 Node:000
    PID:1172(db2syscs.exe) TID:2364 Appid:*LOCAL.DB2.040211170147
    BSU Java support sqlejAttach Probe:50 Database:MEH

    Failed in JNI RegisterNatives. Index:
    2000 0000 ...


    2004-02-11-13.38.05.065000 InstanceB2 Node:000
    PID:1172(db2syscs.exe) TID:2364 Appid:*LOCAL.DB2.040211170147
    BSU Java support sqlejAttach Probe:51 Database:MEH

    class index:
    0100 0000 ....


    2004-02-11-13.38.05.085000 InstanceB2 Node:000
    PID:1172(db2syscs.exe) TID:2364 Appid:*LOCAL.DB2.040211170147
    BSU Java support sqlejAttach Probe:52 Database:MEH

    function name:
    6765 7444 4270 726f 6369 64 getDBprocid


    2004-02-11-13.38.05.125000 InstanceB2 Node:000
    PID:1172(db2syscs.exe) TID:2364 Appid:*LOCAL.DB2.040211170147
    BSU Java support sqlejAttach Probe:53 Database:MEH

    function signature:
    2829 49 ()I


    2004-02-11-13.38.05.155000 InstanceB2 Node:000
    PID:1172(db2syscs.exe) TID:2364 Appid:*LOCAL.DB2.040211170147
    BSU Java support sqlejAttach Probe:54 Database:MEH

    function pointer:
    558b ec83 U


    2004-02-11-13.38.05.255000 InstanceB2 Node:000
    PID:1172(db2syscs.exe) TID:2364 Appid:*LOCAL.DB2.040211170147
    BSU Java support sqlejCallUDF_dll Probe:10 Database:MEH

    Error from attach. rc:33ef ffff 3

Posting Permissions

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