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

    Unanswered: db2 udf/trigger problem

    Can anyone tell me what I am doing wrong with the following UDF/trigger using DB2 7.2, java 1.3.

    I have a very simple java UDF as follows:

    import COM.ibm.db2.app.*;

    import java.io.*;

    /**
    */

    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);


    }
    }

    This function compiles just fine and works OK as standalone w appropriate modifications. I register the UDF successfully as follows:

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

    I create the trigger w/o any problem as follows:

    create trigger vap.testudf
    after update of tool_type on vap.equipment
    for each row mode db2sql
    values vap.testudf ('0123456789')

    Then when I update the table: update vap.equipment set tool_type=3 where tool_name='WaferMark'

    I get the following error:
    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

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

    SQLSTATE "42724" and message tokens

    "VAP.TESTUDF|SQL040203142900278|testudf|(Ljava/". SQLSTATE=09000

    I set the diaglevel to 4 and get the following error log:

    2004-02-03-14.47.40.382000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    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-03-14.47.40.392000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejReload Probe:14 Database:MEH

    loaderValidate: 0000 0000 ....


    2004-02-03-14.47.40.402000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejGetPaths Probe:11 Database:MEH

    C:\PROGRA~1\SQLLIB

    2004-02-03-14.47.40.412000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejGetPaths Probe:61 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-03-14.47.40.432000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejReload Probe:43 Database:MEH

    class loader path 433a 5c50 524f 4752 417e 315c 5351 4c4c C:\PROGRA~1\SQLL
    4942 5c66 756e 6374 696f 6e3b 433a 5c50 IB\function;C:\P
    726f 6772 616d 2046 696c 6573 5c4d 5153 rogram Files\MQS
    6572 6965 735c 4a61 7661 4a4d 535c 4c69 eries\JavaJMS\Li
    623b 2e3b 433a 5c50 726f 6772 616d 2046 b;.;C:\Program F
    696c 6573 5c53 514c 4c49 425c 6a61 7661 iles\SQLLIB\java
    5c64 6232 6a61 7661 2e7a 6970 3b43 3a5c \db2java.zip;C:\
    5072 6f67 7261 6d20 4669 6c65 735c 5351 Program Files\SQ
    4c4c 4942 5c6a 6176 615c 7275 6e74 696d LLIB\java\runtim
    652e 7a69 703b 433a 5c50 726f 6772 616d e.zip;C:\Program
    2046 696c 6573 5c53 514c 4c49 425c 6269 Files\SQLLIB\bi
    6e n


    2004-02-03-14.47.40.572000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejCallUDF_dll Probe:60 Database:MEH

    JNI GetMethodID failed. class:
    7465 7374 7564 66 testudf


    2004-02-03-14.47.40.622000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejCallUDF_dll Probe:61 Database:MEH

    JNI GetMethodID failed. method:
    7465 7374 7564 66 testudf


    2004-02-03-14.47.40.652000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejCallUDF_dll Probe:62 Database:MEH

    JNI GetMethodID failed. signature:
    284c 6a61 7661 2f6c 616e 672f 5374 7269 (Ljava/lang/Stri
    6e67 3b29 49 ng;)I


    2004-02-03-14.47.40.712000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejCallUDF_dll Probe:160 Database:MEH
    DIA9999E An internal error occurred. Report the following error code :
    "FFFFEF2E".

    2004-02-03-14.47.40.732000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    BSU Java support sqlejCallUDF Probe:30 Database:MEH

    Error from DB2JEXT CallUDF. RC:
    2eef ffff .


    2004-02-03-14.47.40.793000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    global_services sqlzesmt Probe:10 Database:MEH

    SQLCA token number 3 was truncated. Full token is:
    5641 502e 5445 5354 5544 467c 5351 4c30 VAP.TESTUDF|SQL0
    3430 3230 3331 3432 3930 3032 3738 7c74 40203142900278|t
    6573 7475 6466 7c28 4c6a 6176 612f 6c61 estudf|(Ljava/la
    6e67 2f53 7472 696e 673b 2949 ng/String;)I


    2004-02-03-14.47.40.863000 Instance:DB2 Node:000
    PID:1732(db2syscs.exe) TID:2780 Appid:*LOCAL.DB2.040203161840
    base_sys_utilities sqleriar Probe:201 Database:MEH

    Error tokens : 5641 502e 5445 5354 5544 46ff 2d34 3330 VAP.TESTUDF-430
    36ff 3432 3732 34ff 5641 502e 5445 5354 642724VAP.TEST
    5544 467c 5351 4c30 3430 3230 3331 3432 UDF|SQL040203142
    3930 3032 3738 7c74 6573 7475 6466 7c28 900278|testudf|(
    4c6a 6176 612f Ljava/


    Data Title:SQLCA PID:1732 TID:2780 Node:000
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -723 sqlerrml: 70
    sqlerrmc: VAP.TESTUDF -4306 42724
    VAP.TESTUDF|SQL040203142900278|testudf|(Ljava/
    sqlerrp : SQLRIUXX~
    sqlerrd : (1) 0xFFFF877E (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate: 00000


    Data Title:DB2RA PID:1732 TID:2780 Node:000
    5351 4c44 4232 5241 e000 0000 0501 0200 SQLDB2RA.......
    0a00 0a00 8800 0000 0900 0080 f0fb 5e01 ....... ..^.
    0000 0000 0400 0040 0000 0000 0b00 0000 .......@........
    0400 0040 bc00 6001 0200 0000 0300 0040 ...@.`........@
    c800 6001 0800 0000 0100 0040 cc00 6001 .`........@.`.
    0800 0000 0100 0040 d400 6001 0800 0000 .......@.`.....
    0100 0040 dc00 6001 0000 0000 0000 0000 ...@.`.........
    0000 0000 0000 0000 0000 0000 0000 0000 ................
    4000 0000 0100 0040 e400 6001 @......@.`.


    Data Title:SQLCA PID:2772 TID:2768 Node:000
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -723 sqlerrml: 70
    sqlerrmc: VAP.TESTUDF -4306 42724
    VAP.TESTUDF|SQL040203142900278|testudf|(Ljava/
    sqlerrp : SQLRIUXX~
    sqlerrd : (1) 0xFFFF877E (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate: 00000


    Data Title:DB2RA PID:2772 TID:2768 Node:000
    5351 4c44 4232 5241 e000 0000 0501 0200 SQLDB2RA.......
    0a00 0a00 8800 0000 0900 0080 5cfd 1200 ....... ..\..
    0000 0000 0400 0040 0000 0000 0b00 0000 .......@........
    0400 0040 20fa 1200 0200 0000 0300 0040 ...@ .........@
    44fd 1200 0800 0000 0100 0040 b041 ca00 D.........@A.
    0800 0000 0100 0040 ca41 ca00 0800 0000 .......@A.....
    0100 0040 b841 ca00 0000 0000 0000 0000 ...@A.........
    0000 0000 0000 0000 0000 0000 0000 0000 ................
    4000 0000 0100 0040 6894 4400 @......@hD.

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

    Re: db2 udf/trigger problem

    search for the sql msg sql0436n in www.groups.google.com

    there is atleast one post which is very informative
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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