Can Stored Procedure Stage access result set?

[horserider]

I am calling DB2 Stored Procedure through STP Stage (Transform).
The output parameter that I get contains only columns containing ERROR_FLAG from procedure. If SQL runs fine ERROR_FLAG returns Y else returns N.

The actual data that I want is in Result Set as Cursor. Can STP Stage access Result Set? How do I get the data back?

[horserider]

Anyone? Any idea?

[chulett]

If anyone did, they would have posted. Have you checked the docs - the stpstage.pdf document, specifically?

[horserider]

I have checked all documentation that I had access to and sniffed many forums, but still no luck.

The stored procedure that has been designed just output 1 row as OUTPUT PARM that indicates whether the SP ran OK or not. The actual data is stored in Cursor ResultSet. The STP Stage does mentions how to use the STP but I am unable to connect it with my issue.

[chulett]

Can't really help other than to suggest you dump the cursor if all you need to know is if it ran OK or not. For that, simply raise an error when there is a problem, DataStage will notice.

[bausba]

The STP stage can output the result from the SP running in database.
First dont close the cursor in the SP script leave it as open and then define the column names in the STP stage in the columns tab same as the column names stored in the cursor along with the other two columns which are automatically generated.

As soon as the data is available in cursor it outputs the data with the column names as defined in the STP stage.

[horserider]

OK, so here is some more info

(1) INPUT PARM : MKT_CODE CHAR(1)
(2) OUTPUT PARM : ERROR_FLAG CHAR(1)

Result Set Column (will always return more than 1 row for each input)
(1) MKT_C CHAR(1)
(2) DIV_C CHAR(1)
(3) REG_C CHAR(3)

Below is what I am doing

Source TEXT FILE > STP STAGE (Transformer Type) > TEXT FILE (Target)

Below are the details for STP Stage TABS:

In INPUT TAB I have
MKT_CODE CHAR(1)

In PARAMETER TAB I have
MKT_CODE CHAR(1) as INPUT
ERROR_FLAG CHAR(1) as OUTPUT

In OUTPUT TAB I have
ProcCode INTEGER (10)
ProcMess VARCHAR (128)
MKT_CODE CHAR (1)
ERROR_FLAG CHAR (1)

Plus, the below columns that will come from result set 9Cursor)
MKT_C CHAR(1)
DIV_C CHAR(1)
REG_C CHAR(3)

Now, when I run the STP by selecting "Procedure returns Multiple Rows"
I get an error saying "Invalid cursor state. SQLSTATE=24000". I thought that since I will get more than 1 row from cursor, I should check this option.

Plus I also get a warning regardless of "Procedure returns Multiple Rows" is checked or not/

How to address these 2 issue?

[bausba]

Please run the SP alone in the database and check whether it returns any rows or not.If it doesnt return any rows then something is wrong in the SP script while opening the cursor to move the data.

And looking at the first post you are calling SP inside the STP stage as Transform Procedure Type but if you want to output the rows from STP stage the Procedure Type should be Source.

I am calling DB2 Stored Procedure through STP Stage (Transform).
The output parameter that I get contains only columns containing ERROR_FLAG from procedure. If SQL runs fine ERROR_FLAG returns Y else returns N.

The actual data that I want is in Result Set as Cursor. Can STP Stage access Result Set? How do I get the data back?

[horserider]

(1)
The same stored procedure runs fine on DB2 Connect interface. I pass input parameter and it gives me the resultset.

(2)
The reason the Stored Procedure is transform type because it takes 1 INPUT PARAMETER.