Java UDF problem

[Abram Friesen]

Hi all,

I'm a newbie at DB2 and trying to create a simple java UDF. When I
call my function, I'm receiving SQL4306N. Could someone please tell me
what I'm doing wrong here?

Here is my java code for a UDF that simply returns the integer value
you pass it:

import java.sql.*;

class ACFTestFunc extends COM.ibm.db2.app.UDF {
public void testIt(int inValue, int result) throws Exception {
set(2,inValue);
}
}


I compile and copy the class file into the sqllib/function directory.
Here is the CREATE FUNCTION statement:

db2 => CREATE FUNCTION testIt(INTEGER) RETURNS INTEGER
EXTERNAL NAME 'ACFTestFunc!testIt'
not fenced
language java
parameter style db2general
no sql
no external action
dbinfo;

I get the " The SQL command completed successfully." message. I follow
that with a GRANT EXECUTE ON FUNCTION:

db2 => GRANT EXECUTE ON FUNCTION testIt(INTEGER) TO PUBLIC WITH GRANT
OPTION;
Again I receive the " The SQL command completed successfully."
message. Everything seems to have gone well up until this point. Then
I call the function:

db2 => connect to sample;
db2 => select testit(1) from employee;

and I receive SQL4306N :

SQL4306N Java stored procedure or user-defined function "DB2.TESTIT",
specific name "SQL040810174255700" could not call Java method
"testIt",
signature "(II)V". SQLSTATE=42724
What am I doing wrong?
Thanks in advance,

Abram Friesen

[Rhino]

"Abram Friesen" <ab***@oax.com> wrote in message
news:e4*************************@posting.google.co m...

Hi all,

I'm a newbie at DB2 and trying to create a simple java UDF. When I
call my function, I'm receiving SQL4306N. Could someone please tell me
what I'm doing wrong here?

Here is my java code for a UDF that simply returns the integer value
you pass it:

import java.sql.*;

class ACFTestFunc extends COM.ibm.db2.app.UDF {
public void testIt(int inValue, int result) throws Exception {
set(2,inValue);
}
}


I compile and copy the class file into the sqllib/function directory.
Here is the CREATE FUNCTION statement:

db2 => CREATE FUNCTION testIt(INTEGER) RETURNS INTEGER
EXTERNAL NAME 'ACFTestFunc!testIt'
not fenced
language java
parameter style db2general
no sql
no external action
dbinfo;

I get the " The SQL command completed successfully." message. I follow
that with a GRANT EXECUTE ON FUNCTION:

db2 => GRANT EXECUTE ON FUNCTION testIt(INTEGER) TO PUBLIC WITH GRANT
OPTION;
Again I receive the " The SQL command completed successfully."
message. Everything seems to have gone well up until this point. Then
I call the function:

db2 => connect to sample;
db2 => select testit(1) from employee;

and I receive SQL4306N :

SQL4306N Java stored procedure or user-defined function "DB2.TESTIT",
specific name "SQL040810174255700" could not call Java method
"testIt",
signature "(II)V". SQLSTATE=42724
What am I doing wrong?

SQL4306N generally indicates a discrepancy between the description of the
UDF in your CREATE FUNCTION statement and the method signature of the
function in your Java code. However, in your case, I don't see any such
discrepancy. (I'm assuming you've cut and pasted the CREATE FUNCTION and
Java code in EXACTLY as they are on your system; these things are very
case-sensitive.)

If you're on DB2 V8.1 on Windows, Unix, or Linux, why don't you try creating
the UDF in the Development Center, which is apparently the replacement for
the Stored Procedure Builder and which can create UDFs? If the build in the
Development Center works there, it should prove that there is nothing wrong
with your Java source code. (I'm still on DB2 V7.2 so I haven't actually
seen the Development Center yet and can't try it myself yet. I'm hand-coding
my UDFs until I can upgrade to V8).

If that doesn't help resolve the issue, I can post a simple UDF (and its
CREATE FUNCTION statement) that works for me. You could try the same code
and see if it works for you. Then maybe we could figure out what subtle
difference is messing yours up.

Rhino

[Knut Stolze]

Abram Friesen wrote:

Hi all,

I'm a newbie at DB2 and trying to create a simple java UDF. When I
call my function, I'm receiving SQL4306N. Could someone please tell me
what I'm doing wrong here?

Here is my java code for a UDF that simply returns the integer value
you pass it:

import java.sql.*;

class ACFTestFunc extends COM.ibm.db2.app.UDF {
public void testIt(int inValue, int result) throws Exception {
set(2,inValue);
}
}


I compile and copy the class file into the sqllib/function directory.
Here is the CREATE FUNCTION statement:

db2 => CREATE FUNCTION testIt(INTEGER) RETURNS INTEGER
EXTERNAL NAME 'ACFTestFunc!testIt'
not fenced
language java
parameter style db2general
no sql
no external action
dbinfo;

I get the " The SQL command completed successfully." message. I follow
that with a GRANT EXECUTE ON FUNCTION:

db2 => GRANT EXECUTE ON FUNCTION testIt(INTEGER) TO PUBLIC WITH GRANT
OPTION;
Again I receive the " The SQL command completed successfully."
message. Everything seems to have gone well up until this point. Then
I call the function:

db2 => connect to sample;
db2 => select testit(1) from employee;

and I receive SQL4306N :

SQL4306N Java stored procedure or user-defined function "DB2.TESTIT",
specific name "SQL040810174255700" could not call Java method
"testIt",
signature "(II)V". SQLSTATE=42724
What am I doing wrong?

I just tried your UDF and it did work quite fine for me on AIX using DB2
V8.1.6.

Which version of DB2 are you using on which platform?
Do you have anything in the db2diag.log that might be relevant for the
error?
Which Java version have you installed?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Philip Sherman]

Did you check that all of the setup things have been done for the
instance? Do a "get dbm cfg" command from a DB2 environment command
prompt and check if the JDK_PATH variable has been properly set.

Phil Sherman
Abram Friesen wrote:

Hi all,

I'm a newbie at DB2 and trying to create a simple java UDF. When I
call my function, I'm receiving SQL4306N. Could someone please tell me
what I'm doing wrong here?

[Abram Friesen]

Hi again,

Actually, I restarted the database manager having reset the DIAGLEVEL
to 4, and I was able to call my java udf with no problem. I still
don't understand what happened, but I'm able to move on. Thanks for
the responses!

Abram