Originally posted by: SystemAdmin
I am trying to use the Stored Procedure stage (NOT an ODBC stage) to call a DB2 stored procedure. This procedure accepts a single input and returns a cursor to a result set. The result set contains a number of numeric and character fields.
My question centers around how to map the result set to an output link. The stage documentation indicates that a CursorOutput parameter should be used "when the stored procedure returns a record or a result set." However, the parameter entry tab insists that every parameter be mapped to a column, presumably to one on the output link in this case.
The columns on my output link are defined as the several fields in the result set (shown at the bottom of this post.) As far as I can tell, there is no way to declare a "result set" type output column and there is no "result set stage" that I can pipe the result to.
How should I set up the parameter list, parameter mapping and output links and columns in order to get this stored procedure stage to work properly?
For reference, I have included the stored procedure code below. Thank you for your assistance.
code:1:aa83541951 CREATE PROCEDURE GITDBA.SELCAT (IN TABLENAME VARCHAR(30) )
RESULT SETS 1
LANGUAGE SQL
COLLID D4R
WLM ENVIRONMENT SDD40H1
RUN OPTIONS 'NOTEST(NONE,
,,*)'
P1: BEGIN
DECLARE FIELD VARCHAR(10);
DECLARE CURSOR1 CURSOR WITH RETURN FOR
SELECT TBCREATOR, TBNAME, NAME, COLNO, COLTYPE, LENGTH
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = TABLENAME
AND TBCREATOR = 'INV'
ORDER BY COLNO;
OPEN CURSOR1;
END P1
Column definitions:
TBCREATOR CHAR(8)
TBNAME VARCHAR(18)
NAME VARCHAR(18)
COLNO SMALLINT
COLTYPE CHAR(8)
LENGTH SMALLINT
[/code:1:aa83541951]
#IBMSterlingTransformationExtender#DataExchange#IBM-Websphere-Transformation-Extender