External stored procedure on IBM i

Question

I am trying to create an external stored procedure on an IBM i (V5R4), but I'm getting an error when I try to run it.

All I want to do is call an RPG program, without passing any parameters or worrying about returning any data. Sorry, I'm not an RPG programmer or an expert on IBM i, so I could be missing something very simple.

The SQL to create the procedure:

CREATE PROCEDURE SOMELIB.SOMEPROC ( ) 

    LANGUAGE RPGLE 

    NOT DETERMINISTIC 

    NO SQL
    EXTERNAL NAME 'OTHERLIB/SOMERG' 

    PARAMETER STYLE GENERAL; 

The error I get when executing CALL SOMELIB.SOMEPROC() is:

SQL State: 38501
Vendor Code: -443
Message: [CEE9901] Application error. RNX1216 unmonitored by BB1002RG at statement 2100000001, instruction X'0000'. Cause . . . . . : The application ended abnormally because an exception occurred and was not handled. The name of the program to which the unhandled exception is sent is SOMERG SOMERG . The program was stopped at the high-level language statement number(s) at the time the message was sent. If more than one statement number is shown, the program is an optimized ILE program. Optimization does not allow a single statement number to be determined. If *N is shown as a value, it means the real value was not available. Recovery . . . : See the low level messages previously listed to locate the cause of the exception. Correct any errors, and then try the request again.

Answer 1

Your procedure is calling the RPG program without the library list set. You can do one of two things:

1) Change the F-spec in the RPG program to qualify the library using the EXTFILE keyword.

2) Call a CL program from the stored procedure that adds the appropriate library to the library list making sure to allow for the fact that the library may already be there from a prior call. Then have the CL program call the RPG program.

Answer 2

(a little bit more rude solution) Identify the user that starts the Stored Procedure. Change the jobdescription of that user to have the correct library list.

But in my experience is the CL program the most pragmatic solution too.