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

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

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

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

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.

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ïÿÿ