× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



There was a discussion on the java list earlier this week regarding this
problem (which I am also having). 
I was unable to resolve the UDF issue; however, with some advice from some
good folks on the list I was able to get stored procedures to work.

The thread can be read at
http://archive.midrange.com/java400-l/200404/msg00001.html

The basic steps I took to fix the stored procedure were (these are
specifically for V5R2):

ADDLNK OBJ('/qibm/ProdData/OS400/Java400/ext/runtime.zip')
NEWLNK('/qibm/userdata/Java400/ext/runtime.zip')

Install PTFs 
SI10765 
SI10767 

I think that you only need to extend UDF if you are using parameter style
DB2GENERAL.

This is my procedure:

CREATE PROCEDURE lbucmusr.test1 (IN systemid varchar(4) ccsid 37,     
  out site varchar(50) ccsid 37 )                                     
  LANGUAGE JAVA PARAMETER STYLE JAVA NOT FENCED                       
  specific lbucmusr.test1                                             
 EXTERNAL NAME 'storedprocs.test';                                    

public class storedprocs { //open class

        public static void test(String ina, String[] b) throws Exception
        {
                b[0] = "Hi " + ina;
        }

}



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bramley, Stu
Sent: Wednesday, April 07, 2004 5:46 AM
To: 'MIDRANGE-L@xxxxxxxxxxxx'
Subject: Getting Java SQL UDFs working

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
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.