Help Needed in calling DB2 Stored Procedure

[wittyme]

I have searched a lot and tried few trial and error methods with stored procedure stage but not able to be success in calling a DB2 Stored Procedure

I have 12 parameters, one Input, One Input/Output and 10 Output types. Like;

Column_Names|SQL_Type|I/O Type|Length
PARM|Char|IN|23
MSG|Char|OUT|193
INPUT|Char|INOUT|100
Remaining columns are DB2Messages fields which are empty

Requirement is that I have to enter value in INPUT parameter and retrieve the MSG

How can this be implemented in datastage?

Thanks,
LakiRay

[ray.wurlod]

Typically you map input columns to the stage onto IN and INOUT parameters, and output columns from the stage onto INOUT and OUT parameters.

[wittyme]

My job design would be


Stored Procedure Stage ---------> DataSet


I am using stored procedure transform type, import all the parameters and column mapping has to be done based on what you said, is that right?

What about the default proccode and procmess fields created, should I map them to any fields too?

Also, should the procedure call syntax be Generated or not.
CALL Stored_Proc_Name(:PARM,?,:INPUT,?,?,?,?,?,?,?,?,?);

I am not familiar with stored procedures so its all new

[chulett]

It would only be a "transform" type if it was in the middle of a job, meaning needing input and output links. Yours is just a source, so select that type.

Where will your input parameter(s) come from?

[wittyme]

The parameters do come from input. This Stored Procedure will be in middle of the job.

Should I need to use Generate Procedure Call.

So on the input tab, the columns should be only two (Parm, Input) - I/O Type Input

On the output tab, should all the in and out type columns present. There are also two extra columns Proc Code and Proc Mess

In the Stage/parameters tab, how should the mapping done, what about the two extra columns (Proc Code and Proc Mess)?

[chulett]

The parameters do come from input. This Stored Procedure will be in middle of the job.

Bit of an incomplete job design posted, eh? Back to a type of "transform". I'll let others with practical DB2 experience handle the other questions.

[wittyme]

I absolutely have no clue on how to achieve this. So basically I am looking for a detail explanation.

The design I gave is the test I am doing currently, I am passing the value manually at runtime using job parameters but in actual scenario I get those values from the input.

[chulett]

Engage your official support provider, if you haven't already. They should be perfectly happy to give you whatever help / explanation you need. This is part of the support your company pays for.

[wittyme]

I could able to achieve it. Just did trial and error methods and could able to get it after like 20 trials.

Maybe I didn't got it in first place as my dsxchange account expired and I renewed just for this to see if I can get help immediately.

bummer.. need to be more patience from next time before renewing the account back.

Thanks for your inputs though.