Stored Procedure - Forward Row Data -> doesn't work!!

joycerecacho · Post by **joycerecacho** » Thu Apr 11, 2013 4:13 pm

Hello Guys!

I have a problem.
I am using the Stored Procedure to connect DB2 database and there are some fields from Input tab that I want to map do Output and these fields are not used at the Procedure.
I checked the "Forward Row Data" option at the input tab to achieve this result but sometimes these values go to the output and sometimes the output for these fields is empty.

Why does it happen?

I am crazy trying to understand this situation.

Best regards my friends.

Joyce

prasannakumarkk · Post by **prasannakumarkk** » Thu Apr 11, 2013 9:19 pm

What type of parameter it is? In, in out.
In parameter mapping have you not used this columns? Can you Post the columns , parameter mapping

joycerecacho · Post by **joycerecacho** » Fri Apr 12, 2013 5:15 am

Hi!

I have 5 fields at the input.
3 of them, I use as parameter, the others I just want to map the values to the output.
And at the output should exist these 2 and the 7 that return from the procedure.

It depends on the register, sometimes the values maped to the output appears, and sometimes is empty.

I can't understand!!!

Thank u!

joycerecacho · Post by **joycerecacho** » Mon Apr 15, 2013 8:54 am

I noticed something interesting.
When it returns more the one row from the Procedure, only the first one maps the input fields to the Output, only the first row has values to these fields.
The other rows, don't. They are empty.

Why?!

Is it a kind of bug?!

Thank guys!

prasannakumarkk · Post by **prasannakumarkk** » Tue Apr 16, 2013 8:00 am

Doesnt it sound interesting. Your question made me to explore more on the stage, and here it is what i have read

http://pic.dhe.ibm.com/infocenter/iisin ... ectly.html

So in our case, is the cursor external or internal? it will be obvious that you have looped through cursor in order to perform some business logic inside a procedure. So internally cursor are looped and sent back to datastage and stored procedure stage pass the input data only for one record since it didnt iterate all the rows returned.
So we have two option now, bring the business logic coded in the stored procedure to datastage discarding the power of PL/SQL
Or allow it to pass for the first record and then split the record and join back with rest of columns using the key columns. Eagerly waiting for your reply to confirm really what has happened

thank you

prasannakumarkk · Post by **prasannakumarkk** » Tue Apr 16, 2013 8:25 am

Thanks for the reply. So it teaches us what is going on. Tip for a day

. Mark the post as workaround

chulett · Post by **chulett** » Tue Apr 16, 2013 9:29 am

joycerecacho wrote:But I really would like to understand WHY it happens. Is it a kind of Bug?

We would only know the answer to that if you involve your official support provider and ask them. Please let us know what you find out if you do.