Call AS400 RPG from SQL Server returning parameter

Question

In my SQL Server instance I have a linked server to AS400.

I want to exec a CLP program that takes 2 parameters, a numeric(8,0) as input and an alphanumeric(3) as output.

These are SQL commands that I use to do it:

DECLARE @Ret varchar(3)
DECLARE @Date varchar(8)
SET @Date = '20200721'
SET @Ret = '   '

EXEC ('CALL IASP01.WUTL.WUTL46(''' + @Date + ''', ''?'')', @Ret) AT AS400 

SELECT @Ret

The command is executed without errors, but no results are returned.

The second parameter is a varchar(3) param because AS400 expects an alphanumeric parameter to return result but it's always empty.

Can anyone help me?

Thanks in advance

UPDATE:

I also tried to create a simple CLP that accepts only 1 parameter and modifies its value. Following is the simple CLP code:

PGM        PARM(&DATA)                                  
            DCL        VAR(&DATA) TYPE(*DEC) LEN(8 0)                
            CHGVAR     VAR(&DATA) VALUE(20200722)                                                                       
            ENDPGM          

It's a very simple program that accepts a numeric(8,0) parameter and modifies it's value.

And here's the Sql Server code that I use to execute DB2 RPG:

declare @P1 numeric(8, 0) select @P1 = 00000000

exec ('CALL .<RPG_Name>(''?'')', @P1 OUTPUT) AT AS400 select @P1

The execution succeeded without errors. After this call, the @P1 variable contains the initial value and not the modified value, so I'm not able to get the "return" value in Sql Server after this call. How can I do it?

Or how can I return a value from CLP to Sql Server?

Thanks

Answer 1

Try creating a stored procedure definition in DB2. I am surprised it works at all without that, but maybe DB2 is smart enough to call the program on a call with default parameter definitions. Unfortunately the default parameter definitions are Input only.

You can find documentation in the Knowledge Base. But it goes something like this:

CREATE PROCEDURE SIMPLE_CLP
  (INOUT DATA DECIMAL(8, 0))
  LANGUAGE CL
  PARAMETER STYLE GENERAL
  DETERMINISTIC
  NO SQL
  EXTERNAL NAME 'SIMPLE_CLP';

Then you should be able to use INOUT parameters.