Executing DB2 Stored Procedure with 1 input & multiple r

[horserider]

Hello All,

I have searched this forum but couldn't get any thread related to my issue with a possible resolution. Also I have searched the documentation but couldn't find any related to DB2 Stored Execution with STP Stage.

I have a stored procedure that takes an input parameter and then should return me more than 1 row as a output. I want to pass the input and then read the output of the stored procedure execution.

Below is my DB2 Stored procedure

INPUT-PARM1 Char(01) Mkt Code

OUTPUT PARAMETERS
Type Name Length Description
Output OUTPUT-PARM1 Char (01) 01 ERROR-FLAG PIC X (01).
Output OUTPUT-PARM2 Integer 01 ERROR-MESSAGE-ID PIC S9
Output OUTPUT-PARM3 VChar(2000) 1 ERROR-PARM
Output OUTPUT-PARM4 VChar(367) 01 FATAL-ERROR-MSG


Resultant Set Columns:
S.No Name Length
1 MARKET_C Char (01)
2 DIVISION_ID_C Char (01)
3 VEHLIN_C Char (03)
4 REGION_ID_C Char (02)
5 REGFOR_CALYRMTH_Y Date
6 REGFOR_FLDSLSSHR_P Small Integer

BELOW ARE THE COMPLETE SQL CODE


SQL -1

EXEC SQL
DECLARE REG_SALES_CSR CURSOR WITH RETURN FOR
SELECT DISTINCT
B09.MARKET_C
,B09.DIVISION_ID_C
,B09.VEHLIN_C
,B09.REGION_ID_C
,B09.REGFOR_CALYRMTH_Y
,(CASE
WHEN REGFOR_FLDSLSSHR_P IS NOT NULL
THEN REGFOR_FLDSLSSHR_P
ELSE REGFOR_FORMSHR_P
END)
FROM MRTMB09_REGFOR B09
,MRTMA20_REGION A20
WHERE B09.MARKET_C = :WS-MARKET-CODE
AND B09.MARKET_C = A20.MARKET_C
AND B09.DIVISION_ID_C = A20.DIVISION_ID_C
AND A20.REGTYP_ID_C IN ( 'A', 'S' )
AND B09.REGFOR_CALYRMTH_Y > :WS-FORECAST-MONTH
AND A20.REGION_ID_C = B09.REGION_ID_C
ORDER BY B09.DIVISION_ID_C
,B09.VEHLIN_C
,B09.REGION_ID_C
WITH UR
END-EXEC.

SQL-2

EXEC SQL
SELECT ERRMES_DESC_X
INTO :MRTMB10.ERRMES-DESC-X
FROM MRTMB10_ERRMES
WHERE ERRMES_CODE_C = :ERRMES-CODE-C
END-EXEC

SQL-3

EXEC SQL
OPEN REG_SALES_CSR
END-EXEC

SQL -4

SELECT "Y"
INTO :WS-DUMMY
FROM MRTMA23_MARKET
WHERE MARKET_C = :WS-INP1-MARKET


Problem I am facing

All I want is pass input and read the output that I get

(1) When I import the Definition from Manager, I have 1 input parameter
and 4 OUTPUT PARAMETERS. The 4 OUTPUT PARAMETER are
only the error code returned from Stored Procedure as described
above.

(2) How and where should I defined my Resultant Set Columns in
Stored Procedure Stage so that I can read the ACTUAL ROWS that are
being returned?

(3) Is the Stored Procedure Stage setup in a different way or any special
patch available to handle multiple row output? Or the Stored Procedure
should be written in a different way?