Java Stored Procedure doesn't work on DB2/400 V6R1 - It Used to work on V5R4

The stored procedure definition is:

CREATE PROCEDURE MYSCHEMA.LOAD_ETL ( ) 
    LANGUAGE JAVA 
    SPECIFIC MYSCHEMA.LOAD_ETL
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'ETL!loadETL' 
    PARAMETER STYLE JAVA ;

With System i Navigator I granted Read and Execute permissions. The Java class file is copied and compiled inside de AS/400 on /QIBM/UserData/OS400/SQLLib/Function/ETL.CLASS.

When I try to execute the stored procedure with call MYSCHEMA.LOAD_ETL(), I get the error:

[SQL4303] Java stored procedure or user-defined function name, specific name spec-name could not be identified from external name string.

Explanation: The CREATE PROCEDURE or CREATE FUNCTION statement that declared this stored procedure or user-defined function had a badly formatted EXTERNAL NAME clause. The external name must be formatted as follows: "package.subpackage.class!method".

So, I refactored the class to com.mycompany.ETL. And copied and recompiled it inside the AS/400 to /QIBM/UserData/OS400/SQLLib/Function/com/mycompany/ETL.class.

And I recreate the stored procedure:

CREATE PROCEDURE MYSCHEMA.LOAD_ETL ( ) 
    LANGUAGE JAVA 
    SPECIFIC MYSCHEMA.LOAD_ETL
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'com.mycompany.ETL!loadETL' 
    PARAMETER STYLE JAVA ;

However It still doesn't work.

Now the error is:

SQL0551. Authorization-ID does not have the privilege to perform operation operation on object name.

With System i Navigator I granted Read and Execute permissions to the Java class file on /QIBM/UserData/OS400/SQLLib/Function/ETL.CLASS.

I get

CPFA0A9 (Object not found): /QIBM/UserData/OS400开发者_开发知识库/SQLLib/Function/com.mycompany.ETL.class

So I packed it into a jar file and copied to /QIBM/UserData/OS400/SQLLib/Function/etl.jar.

I recreated the stored procedure one more time but it still doesn't work.

Then I recreate -again- the stored procedure as:

CREATE PROCEDURE MYSCHEMA.LOAD_ETL ( ) 
    LANGUAGE JAVA 
    SPECIFIC MYSCHEMA.LOAD_ETL
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'com/mycompany/ETL!loadETL' 
    PARAMETER STYLE JAVA ;

Once again, It still doesn't work.

Thanks in advance for help.

This worked for me: don't specifiy a package for Java file, put its .class into Function folder and in the procedure set this EXTERNAL NAME: 'ETL.loadETL'.

Here's what I've done to get it working:

• The method must be public and static.
• Pay attention to Class and Method names and case.

• Create the SP like this:

   CREATE PROCEDURE LIBNAME/PROCNAME()
   PARAMETER STYLE JAVA READS SQL DATA LANGUAGE JAVA EXTERNAL NAME 
   'MyClassName.methodName'
  

(I'm using "system" naming, use "." instead of "/" if it's not your case).

Note that this example is related to an (almost) empty method, in real life you'll need IN (and possibly OUT) parameters.

• Build the Jar file
• Open it and get the .class file
• Extract it into "/QIBM/UserData/OS400/SQLLib/Function"
• There you (should) go ;-)