SQL4306N thrown in call of Java UDF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jefftyzzer - 03 Apr 2008 01:53 GMT

Friends:

(DB2 LUW 8.1 FP 10, 64-bit)

I have a fairly small Java program that I want to wrap a UDF around.
I'm able to call the Java program from a little caller class fine, but
am getting an SQL4306N error when calling (it via) the UDF.

1.) The Java class:

public class levEditDist {
public int led (String s, String t) {
//Do stuff
return a cell of a two-dimensional array of integers;
}
}

2.) The UDF declaration:

CREATE FUNCTION JTYZZER.LED (s VARCHAR(128), t VARCHAR(128))
RETURNS INTEGER
SPECIFIC LED
LANGUAGE java
PARAMETER STYLE java
NO SQL
FENCED THREADSAFE
DETERMINISTIC
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
EXTERNAL NAME 'levEditDist.led';

3.) The call:

SELECT jtyzzer.LED('Jeff', 'John') LED FROM sysibm.sysdummy1;

4.) The error:

SQL4306N Java stored procedure or user-defined function
"JTYZZER.LED",
specific name "LED" could not call Java method "led", signature
"(Ljava/lang/String;Ljava/lang/String;)I". SQLSTATE=42724

SQL4306N Java stored procedure or user-defined function
"JTYZZER.LED", specific name "LED" could not call Java method "led",
signature "(Ljava/lang/String;Ljava/lang/String;)I ".

5.) FWIW, here's some (relevant? ;-) content from the db2diag.log
file:

2008-04-02-17.47.34.185370-420 I574555A399 LEVEL: Warning
PID : 4604112 TID : 3085 PROC : db2fmp (Java)
0
INSTANCE: instdv04 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:
130
MESSAGE : JNI GetMethodID failed. class:
DATA #1 : Hexdump, 11 bytes
0x0000000110065C60 : 6C65 7645 6469 7444 6973 74
levEditDist

2008-04-02-17.47.34.185662-420 I574955A391 LEVEL: Warning
PID : 4604112 TID : 3085 PROC : db2fmp (Java)
0
INSTANCE: instdv04 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:
140
MESSAGE : JNI GetMethodID failed. method:
DATA #1 : Hexdump, 3 bytes
0x0000000110065C6C : 6C65 64 led

2008-04-02-17.47.34.185826-420 I575347A561 LEVEL: Warning
PID : 4604112 TID : 3085 PROC : db2fmp (Java)
0
INSTANCE: instdv04 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:
150
MESSAGE : JNI GetMethodID failed. signature:
DATA #1 : Hexdump, 39 bytes
0x0000000110065CC0 : 284C 6A61 7661 2F6C 616E 672F 5374 7269 (Ljava/
lang/Stri
0x0000000110065CD0 : 6E67 3B4C 6A61 7661 2F6C 616E 672F 5374
ng;Ljava/lang/St
0x0000000110065CE0 : 7269 6E67 3B29 49
ring;)I

2008-04-02-17.47.34.186549-420 E575909A374 LEVEL: Warning
PID : 4604112 TID : 3085 PROC : db2fmp (Java)
0
INSTANCE: instdv04 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
MESSAGE : ADM10000W A Java exception has been caught. The Java stack
traceback has been written to the db2diag.log.

2008-04-02-17.47.34.186834-420 I576284A384 LEVEL: Warning
PID : 4604112 TID : 3085 PROC : db2fmp (Java)
0
INSTANCE: instdv04 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
MESSAGE : java.lang.NoSuchMethodError: led
DATA #1 : Hexdump, 4 bytes
0x00000001133AA660 : 0000 0000 ....

2008-04-02-17.47.34.187046-420 I576669A391 LEVEL: Warning
PID : 4604112 TID : 3085 PROC : db2fmp (Java)
0
INSTANCE: instdv04 NODE : 000
FUNCTION: DB2 UDB, routine_infrastructure, sqlerJavaCallRoutine, probe:
30
MESSAGE : Error from DB2ER CallUDF. RC:
DATA #1 : Hexdump, 4 bytes
0x00000001133AAA44 : FFFF EF2E ....

6.) javap output:

/usr/java14_64/bin/javap levEditDist
Compiled from "levEditDist.java"
public class levEditDist extends java.lang.Object{
public levEditDist();
public int led(java.lang.String,java.lang.String);
}

Some final details: I compiled the program using the same (version of)
Java as the JDK_PATH specifies. I also tried creating the UDF both
with and without putting the class in a JAR file. I also added the
location of the .class and .jar files to my CLASSPATH.

Any (pertinent) thoughts?

Thanks as always,

--Jeff

Reply to this Message

Knut Stolze - 03 Apr 2008 10:15 GMT

> Friends:
>
[quoted text clipped - 123 lines]
>
> Any (pertinent) thoughts?

I haven't looked at it in detail, but there is a pretty nice article summing
up the different typical issues with Java UDFs and how to fix them:

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0510law/index.html

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

jefftyzzer - 03 Apr 2008 17:18 GMT

> > Friends:
>
[quoted text clipped - 133 lines]
> DB2 z/OS Utilities Development
> IBM Germany

Thanks, Knut. I had found that article--and read it thoroughly, and am
afraid it didn't yield any fruit.

I appreciate the recommendation, though.

--Jeff

Reply to this Message

aka - 03 Apr 2008 22:15 GMT

>> > Friends:
>>
[quoted text clipped - 141 lines]
>
> --Jeff

Hi Jeff,

I see some problems in your java code:

>> > public class levEditDist {

should be: public class levEditDist extends UDF {

>> > public int led (String s, String t) {

The out parameter is not the return value of the method, but another
(reference) parameter into the method:

public void led (String s, String t, int[] i) {

>> > //Do stuff
>> > return a cell of a two-dimensional array of integers;

return nothing, but assign a value to output parameter: i[0] = some_value;

>> > }
>> > }

Good luck,
aka.

Reply to this Message

Knut Stolze - 04 Apr 2008 08:57 GMT

>>> > Friends:
>>>
[quoted text clipped - 156 lines]
>
> return nothing, but assign a value to output parameter: i[0] = some_value;

No, the code is (mostly) fine as written. What you are saying refers to
PARAMETER STYLE GENERAL, but Jeff is using UDF with PARAMETER STYLE JAVA.
Jeff has to have use a "return" statement and not an additional parameter
for the return value, of course.

The actual problem with the code is that method "led" is not "static". Just
add this keyword and the Java entry point for the UDF will be found:

public class levEditDist {
public static int led (String s, String t) {
return 10;
}
}

(You can also add the "throws Exception" clause if you want to.)

$ db2 terminate ; db2stop ; db2start ; db2 connect to test; db2 "values
jtyzzer.led('abc', 'def')"
DB20000I The TERMINATE command completed successfully.
04/04/2008 09:54:43 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
04/04/2008 09:54:44 0 0 SQL1063N DB2START processing was
successful.
SQL1063N DB2START processing was successful.

Database Connection Information

Database server = DB2/LINUXX8664 9.5.0
SQL authorization ID = STOLZE
Local database alias = TEST

1
-----------
10

1 record(s) selected.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

jefftyzzer - 04 Apr 2008 17:48 GMT

> >>> > Friends:
>
[quoted text clipped - 199 lines]
> DB2 z/OS Utilities Development
> IBM Germany

Thank you Knut, and thanks to "aka" as well.

While I went ahead and made the led method a class method, I wonder if
the actual problem may all along have been that I need to stop and
restart the database after creating the .class file? I'm not able to
do that to the database I'm working with now (it's shared), but will
scrounge around and find one that I can stop and start at-will and see
if that was the actual problem. The docs seem to be a bit ambiguous on
whether or not this is necessary for UDFs vis a vis stored procedures.

Can anyone tell me why this step might be necessary? Mind you,
the .class file does not live in the .../sqllib/function directory.

Again, I appreciate everyone's assistance.

Regards,

--Jeff

Reply to this Message

jefftyzzer - 04 Apr 2008 20:32 GMT

> > >>> > Friends:
>
[quoted text clipped - 218 lines]
>
> --Jeff

Got it to work. The key was simply to call CALL SQLJ.REFRESH_CLASSES()
before calling the UDF. *Sigh*.

--Jeff

Reply to this Message

Knut Stolze - 07 Apr 2008 12:23 GMT

> Can anyone tell me why this step might be necessary? Mind you,
> the .class file does not live in the .../sqllib/function directory.

Here is my knowledge about non-Java external UDFs: DB2 loads the respective
library that implements the UDF into memory. Each library is kept in
memory for a certain amount of time after its last usage (e.g. 10 minutes
or so). A simple LRU mechanism keeps track of this and throws out the
library. (That helps to improve performance for repeated calls into the
library.) Furthermore, DB2 keeps the last 5 (or so) libraries in memory,
regardless of their last usage. Therefore, replacing the file on disk
doesn't help if you don't tell DB2 that it has to load a new version of the
lib. For Java, you have REFRESH_CLASSES, for C/C++ UDFs you have to
restart DB2. Since this is only done in development systems and during
migration, there is typically not an issue.

Another approach I favor when writing C/C++ UDFs is to write a small main
function stub that calls my UDF directly, w/o DB2 in the picture. That
also makes debugging much easier...

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

jefftyzzer - 07 Apr 2008 21:22 GMT

> > Can anyone tell me why this step might be necessary? Mind you,
> > the .class file does not live in the .../sqllib/function directory.
[quoted text clipped - 19 lines]
> DB2 z/OS Utilities Development
> IBM Germany

Thanks for that detailed explanation, Knut--much appreciated!

--Jeff

Reply to this Message

aka - 04 Apr 2008 22:02 GMT

>> return nothing, but assign a value to output parameter: i[0] =
>> some_value;
[quoted text clipped - 3 lines]
> Jeff has to have use a "return" statement and not an additional parameter
> for the return value, of course.

yes, I'm sorry that I missed it...

Knut, you're hero!

> The actual problem with the code is that method "led" is not "static".
> Just
[quoted text clipped - 29 lines]
>
> 1 record(s) selected.

Reply to this Message