Getting Java SQL UDFs working

["Bramley, Stu" <stu.bramley@xxxxxxxxxxxxx>]

I am trying to get Java SQL UDFs (and then Java Stored Procedures) - but I
am having problems!

I have cobbled a 'HelloWorld' function together that I am trying to call.
The Java source code for the function is as follows:

------

import COM.ibm.db2.app.UDF;

public class Hello extends UDF {
        
        public static String HelloWorld() {
                return "HelloWorld";
        }
        
} 

------

I have compiled this source into the folder
/QIBM/UserData/OS400/SQLLIB/Function. 

To register the function with SQL on the iSeries I have run the following
SQL statement : 

CREATE FUNCTION LIB/HELLO() 
RETURNS VARCHAR(32)
FENCED
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'Hello.HelloWorld'

However when I try and call the function from an interactive SQL session
using syntax:

SELECT HELLO() FROM FILE

I get the following error: 

"Query cannot be run.  See lower level messages"

When I look at the lower level messages I have the following in my job log: 

"User-defined function error on member MEMBER"

Closer examination of the error message shows:

------

Message ID . . . . . . :   CPF503E       Severity . . . . . . . :   30

Message type . . . . . :   Diagnostic

Date sent  . . . . . . :   07/04/04      Time sent  . . . . . . :   10:31:58

 

Message . . . . :   User-defined function error on member MEMBER.           
Cause . . . . . :   An error occurred while invoking user-defined function

  HELLO in library LIB. The error occurred while invoking the associated 
  external program or service program oWorld in library Hello!Hell, program

  entry point or external name . The error occurred on member MEMBER file   
  FILE in library LIB. The error code is 1. The error codes and     
  their meanings follow:

    1 -- The external program or service program returned SQLSTATE 42724.
The  
  text message returned from the program is:

   LIB.HELLOHELLOHelloWorld .                                      
    2 -- The external program failed before it completed.

    3 -- The database timed out waiting for the program to return. The
timeout 
  value used by the database was 0 minutes and 30 seconds.

    4 -- The external program no longer exists or is not found.

    5 -- One of the input parameters of the function had a data mapping
error.
    For an external program, the program entry point displayed will be *N.

Recovery  . . . :   For error codes 1 and 2, determine the cause of the
error 
  from either the SQLSTATE or a previously listed message.

    For error code 3, either increase the timeout limit using the QAQQINI
file
  setting or determine why the external program or service program did not

  return in the time allotted.

    For error code 4, ensure the program or service program exists for the

  duration of the query.

    For error code 5, determine the cause of the data mapping error.

    Try the request again.

    Refer to the DB2 UDB for iSeries SQL Programming topic in the
Information
  Center book, http://www.iseries.ibm.com/infocenter for more information on

  user-defined functions.

Possible choices for replying to message . . . . . . . . . . . . . . . :

  C -- The request is canceled.

  I -- The request is ignored.


-------

I have tried changing the import statement in the Java code to be
com.ibm.db2.app.UDF rather than COM.ibm.db2.app.UDF...
I have tried changing my CLASSPATH environment variable to include
'/QIBM/UserData/OS400/SQLLIB/Function'..
I have also tried variations on the CREATE FUNCTION statement such as using
'!' instead of '.', changing the PARAMETER STYLE to DB2GENERAL, omitting
FENCED... so far to no avail. 
I have looked at the SQLJ.INSTALL_JAR stored procedure but I don't think I
need to run that as my Java isn't in a .jar file, it's in a .class file. 

The Java code works fine when called from a test harness class... 

Any help appreciated!
Cheers,
Stu

Stuart Bramley 
Senior Technical Developer 
Skandia Life::GroupIT 
Southampton 
t: 023 80 72 64 29 e: stu.bramley@xxxxxxxxxxxxx 
If you are not the intended recipient, please notify the sender by return email 
and then delete the message from your computer. The Skandia UK Group reserves 
the right to monitor e-mail communications through its networks.

No contract may be concluded on behalf of the Skandia UK Group by email.

Skandia Life Assurance (Holdings) Limited Skandia Life Assurance Company 
Limited Skandia MultiFUNDS Limited, Skandia Investment Management Limited.

Registered Nos: 1606702, 1363932, 1680071, 4227837, England
Registered Office: Skandia House, Portland Terrace, Southampton SO14
7EJ, United Kingdom

Royal Skandia Life Assurance Limited Registered No : 24916 Isle of Man
Registered Office: Skandia House, King Edward Road, Onchan, Isle of Man IM99 
1NU, British Isles

Skandia Life Assurance Company Limited, Skandia MultiFUNDS Limited, Skandia 
Investment Management Limited and Royal Skandia Life Assurance Limited are 
authorised and regulated by the Financial Services Authority for UK
investment business.

Internet: www.skandia.co.uk