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

Show us the definition of the RPG program. Particularly the `*ENTRY PLIST` and the definition of the parameters on it. — jmarkmurphy, Jul 22 '20 at 15:10
Hi @jmarkmurphy, The program I call is a CLP program and not an RPG...sorry. I updated my initial question with another example and the simple CLP code. Can you tell me what I'm doing wrong? — Badozvora, Jul 23 '20 at 09:53
what does your stored procedure definition look like? The one on IBM i. — jmarkmurphy, Jul 27 '20 at 12:44
On IBM I've only the CLP described above and it's directly called by a Sql Server query. The above code is the complete CLP code. Do you need something more? — Badozvora, Jul 28 '20 at 07:22
Yes, you need to create a stored procedure definition on DB2. — jmarkmurphy, Jul 28 '20 at 13:14

score 0 · Answer 1 · answered Jul 28 '20 at 13:22

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.

Call AS400 RPG from SQL Server returning parameter

1 Answers1