Calling RPG program from SQL with parameter

Question

In DB2 SQL is present since 7.1 the function SQL QCMDEXC() to call external program.

I want to use it like this:

SELECT    
    MYFIELD,
    CASE 
       WHEN QSYS2.QCMDEXC('CALL MYPROGRAM(**MYFIELD**)' ) = 1 THEN 'Successfull'
       ELSE 'Error'
    END AS "Cmd status"
FROM 
    MYFILE;

Is there a way to do it?

Answer 1

QCMDEXC has been around a long time...

IBM simplified it's use by providing a stored procedure version around 7.1 I think.

However, in order to use it as you are trying to do, you'd need the scalar function version which came out for 7.4 and 7.3.

Assuming the function is available, then all you need is

SELECT    
    MYFIELD,
    CASE 
       WHEN QSYS2.QCMDEXC('CALL MYPROGRAM parm(''' concat MYFIELD concat ''')' ) = 1 THEN 'Successfull'
       ELSE 'Error'
    END AS "Cmd status"
FROM 
    MYFILE;

The above assumes MYFIELD is a character string. If you're trying to pass a numeric value. If MYFIELD is numeric, then you'd need to convert it to a string via char() and make sure that MYPROGRAM accepts a packed (15,5) value as the command interpreter passes a numeric constant.

However if you're on 7.4+ and have the Dec 2022 PTF's that enhance the CALL command's parameter passing; you'll be able to specify how to pass the numeric value.

'CALL MYPROGRAM parm(' concat char(MYFIELD) concat ' (*INT 4))'