db2 - Can front end can use procedure inside of a service program?

Question

We have a requirement where the front end team what to use the procedures inside of a service program. Can we achieve this with stored procedure calling the procedure inside of a service program.

Front end ---Calling---> Stored procedure ----calling---> procedure inside of a service program

Can this be done?

Answer 1

Yes you can point an external stored procedure to a procedure in a service program.

Key options are the the EXTERNAL NAME, LANGUAGE and PARAMETER STYLE clauses.

LANGUAGE RPGLE
EXTERNAL NAME 'MYLIB/MYSRVPGM(MyReallyCoolProcedure)'

note that you can have mixed-case procedure names in RPG IV. SO be sure to use the name as shown via DSPSRVPGM.

Now lets talk about PARAMETER STYLE, if you're trying to re-use an existing procedure quickly then you'll likely want to use PARAMETER STYLE GENERAL. However, that implies that the stored proc can not and will not be called on null input.

If you want the stored procedure to be CALLED ON NULL INPUT, then you'll need to use a parameter style of SQL or GENERAL WITH NULLS. The problem is that both of those mean that some extra parameters are passed implicitly to the RPG program or procedure. Meaning the RPG code must be designed to accept them.

PARAMETER STYLE SQL provides the best interface.

Specifies that in addition to the parameters on the CALL statement, several additional parameters are passed to the (external) procedure. The parameters are defined to be in the following order:

• The first n parameters are the parameters that are specified on the CREATE PROCEDURE statement.
• n parameters for indicator variables for the parameters.
• A CHAR(5) output parameter for SQLSTATE. The SQLSTATE returned indicates the success or failure of the procedure. The SQLSTATE returned is assigned by the external program. The user may set the SQLSTATE to any valid value in the external program to return an error or warning from the procedure.
• A VARCHAR(517) input parameter for the fully qualified procedure name.
• A VARCHAR(128) input parameter for the specific name.
• A VARCHAR(1000) output parameter for the message text.

The following additional parameter may be passed as the last parameter:

• A parameter for the dbinfo structure, if DBINFO was specified on the CREATE PROCEDURE statement.

My preferred practice is to create a new RPGLE wrapper, that accepts the parameter list needed for PARAMETER STYLE SQL and calls the existing RPGLE prcoedure.

This allows me to accept some NULL parameters if needed and allows me better control over what SQLSTATE is passed back if any errors occur.

Another good reference is the IBM Redbook, External Procedures, Triggers, and User-Defined Functions on IBM DB2 for i