iSeries - Call SQL stored procedure from CL program

Question

How can I run a stored procedure from a CL program? RUNSQLSTM requires a source member, but I just want to build a command so users can pass in variables easily, so this won't work.

Solution 1

You can call the system program QZDFMDB2 and pass it one parameter with the SQL string to execute. In this case the SQL string is the call to your stored procedure:

  CALL PGM(QZDFMDB2) PARM('CALL PROCEDURE (''XYZ'', ''ABC'')')

To substitute in your values use a variable for the PARM:

  DCL VAR(&CALL) TYPE(*CHAR) LEN(200) 

  CHGVAR VAR(&CALL) 
  VALUE('CALL PROCEDURE (''' *CAT &PARM1 *TCAT ''', ''' *CAT &PARM2 *TCAT ''')')

  CALL PGM(QZDFMDB2) PARM(&CALL)

Solution 2

There may be 2 options:

In the CL program create a temporary source member. Construct the SQL statement with the supplied parameters to execute the stored procedure and write this source code to the temporary member. Then add a call to RUNSQLSTM with the generated member as parameter.

A second option is not to create a CL program but use RPG/COBOL. By using embedded SQL the stored procedure can be executed with the parameters. Their values should be passed from the command to the program.

Solution 3

At one time, there was a command that someone wrote that I had used. I can't find that reference anymore, but I have two other options:

1. This article uses REXX
2. This article uses RPG

They both include the source you need to get the command working.

Comments

[Admin]

I think your second idea is best for me - thanks. Would be nice to be able to call a procedure directly though!

[Admin]

To indicate the location or the library of the procedure, here's a sample: CALL PGM(QZDFMDB2) PARM('CALL LIBRARY.PROCEDURE (''XYZ'', ''ABC'')')