External stored procedure on IBM i

stored-procedures ibm-midrange rpgle
Eugene M picture Eugene M · Oct 14, 2009 · Viewed 10.7k times · Source

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

Tracy Probst picture Tracy Probst · Oct 15, 2009

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.

Related questions

How to call a stored procedure in IBM System i Access for Windows GUI Tool

I would like to test a DB2 stored procedure running on an AS400 system. I have the IBM System i Access for Windows installed and can run SQL commands against the DB2 database. My question is: What is the syntax …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More