I'm testing some functions of Db2 for z/OS, and right now I'm having troubles when I try to call a Stored Procedure through REXX.
Below is the code to create the Data Type that I need to return in my Stored Procedure
CREATE TYPE *******.ARRAY_TESTE AS VARCHAR(186) ARRAY[200];
This is the DDL that I used to create the Stored Procedure
CREATE PROCEDURE *******.TESTE_EMP2
(OUT EDLEVELARR MYVARRTYPE,
OUT LASTNAMEARR MYVARRTYPE)
VERSION MEDIAN_V1
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
DYNAMIC RESULT SETS 1
DISABLE DEBUG MODE
PARAMETER CCSID EBCDIC
QUALIFIER *******
PACKAGE OWNER ******
ASUTIME NO LIMIT
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS
NODEFER PREPARE
CURRENT DATA NO
DEGREE 1
DYNAMICRULES RUN
WITHOUT EXPLAIN
WITHOUT IMMEDIATE WRITE
ISOLATION LEVEL CS
WITHOUT KEEP DYNAMIC
OPTHINT ''
RELEASE AT COMMIT
REOPT NONE
VALIDATE RUN
ROUNDING DEC_ROUND_HALF_EVEN
DECIMAL(31)
BUSINESS_TIME SENSITIVE NO
SYSTEM_TIME SENSITIVE NO
ARCHIVE SENSITIVE NO
BEGIN
SELECT ARRAY_AGG(VARCHAR(EDLEVEL)), ARRAY_AGG(LASTNAME)
INTO EDLEVELARR, LASTNAMEARR
FROM DSN81110.EMP ;
END `
And this is the REXX that I used to call the stored Procedure
LOCAL_DB2 = ****
ADDRESS DSNREXX
RC = 'SUBCOM DSNREXX'
OUTTRAP TESTE_DATA.
TESTE_DATA2.
IF RC <> 0 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
"CONNECT " LOCAL_DB2
IF SQLCODE <> +0 THEN DO
MY_ERROR = 'ERROR CONNECTING TO DB2 ' LOCAL_DB2
END
EXECSQL "CALL *******.TESTE_EMP2(:TESTE_DATA.,:TESTE_DATA2.)"
IF (SQLCODE<>0) THEN
CALL SQL_ERROR
ADDRESS DSNREXX
"DISCONNECT"
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
EXIT
Right now I'm having the SQLCODE
-301 THE VALUE OF INPUT VARIABLE OR ARGUMENT NUMBER position-number CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE
