英文:
How to get an array as output using rexx
问题
我正在测试 Db2 for z/OS 的一些功能，目前在尝试通过 REXX 调用存储过程时遇到了问题。
以下是我需要在存储过程中返回的数据类型的代码：
CREATE TYPE *******.ARRAY_TESTE AS VARCHAR(186) ARRAY[200];
这是我用来创建存储过程的DDL：
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
这是我用来调用存储过程的REXX代码：
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
目前我遇到了以下SQLCODE：
-301 输入变量或参数编号 position-number 的值无法按指定的方式使用，因为其数据类型不正确
英文:
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
答案1
得分: 1
这条消息是不言自明的。用户定义的数组数据类型目前不是REXX调用Db2存储过程所支持的数据类型之一。
请参考此文档页面以查看REXX支持的数据类型及其对应的SQL数据类型。
英文:
The message is self explanatory. The user-defined-array-datatype is not one of the supported data-types for REXX calling Db2 stored procedures, at current versions .
Refer to this documentation page to see the types that REXX supports and the corresponding SQL data type,
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论