3

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.

Improve this question

5 Answers 5

Reset to default
5

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)
Improve this answer
1
  • To indicate the location or the library of the procedure, here's a sample: CALL PGM(QZDFMDB2) PARM('CALL LIBRARY.PROCEDURE (''XYZ'', ''ABC'')')
    – Chel P.
    Commented Oct 17, 2017 at 2:54
3

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.

Improve this answer
1
  • I think your second idea is best for me - thanks. Would be nice to be able to call a procedure directly though!
    – main_menu
    Commented Oct 29, 2008 at 16:29
2

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.

Improve this answer
2

One way to call a StoredProcedure from CLLE is: RUNSQL SQL('call MYPRCSQL')

Improve this answer
2
  • Yeah, the relatively new RUNSQL command sounds like exactly what the asker was looking for. After all, it's basically like RUNSQLSTM except the SQL statement is inline rather than in a separate member. It simply wasn't available back in 2008, which is why there are a variety of other approaches mentioned in the other answers.
    – John Y
    Commented Nov 4, 2023 at 5:12
  • I'm currently having an issue with RUNSQL wherein I call my procedure, but for some reason I get an error saying RUNSQL doesn't support recursion. Which is strange.
    – Rsp8
    Commented Jan 15 at 14:58
0

QCMDEXC might be the command you are looking for.

Improve this answer