Stored Procedure Parameters

lstsaur · Post by **lstsaur** » Wed Jul 05, 2006 7:15 pm

Hello Forum,
For both DS 7.5 and 7.5x2 EE versions, DataStage STP uses a parameters grid to map input and output columns to the input and output parameters of the stored procedure. However, with the 7.5.1A version, STP Stage no longer provides the grid style and you have to manually type in parameters' information in the Stored Procedure Parameters box.

I have tried to type in all kinds of combinations such as deptno_arg-+-input-+-?-+-|+-+dname_arg-+-output-+-?-+-, but nothing worked.
I also have searched all possible on-line help and/or documentations, but I found nothing either regarding what's correct syntx to enter.

Can anyone shed some light on this. Thanks.

chulett · Post by **chulett** » Wed Jul 05, 2006 7:53 pm

Not sure how much this helps, but I haven't had to make use of the Parameters tab yet. When the Procedure Type is set to Source or Target (I've only had a need to use Target so far) the Column metadata is used instead.

It's only for a Procedure type of Transform that the Parameters tab comes into play. Best way to load those seems to be to first import the metadata of the SP into the repository and then use the 'Load' button to get them into the stage. Are you using the Stored Procedure in this fashion, a Transform type? Can you give the import/load mechanism a shot?

Oh, and what database?

lstsaur · Post by **lstsaur** » Wed Jul 05, 2006 10:37 pm

Craig,
Thanks for the suggestion. I am using Oracle 10g DB. I have been using the STP Stage (transform) exactly the way you suggested using the Load button to populate the parameters on DS 7.5 and 7.5x2 versions. All jobs using STP Stage(transform) are working as expected.

The problem is with 7.5.1A version, STP Stage (transform) no longer provides the Paramater tab on the Stage page. It has a Stage tab and a Properties tab. Within the Properties tab, there is a box for the Stored Procedure Parameter that's where you define your parameters as In, Out, IN/OUT, or Function to the Stored Procedure. There is no more 'LOAD' button on the page. That's where I am getting stuck now.

I have tried to type in all kinds of combinations such as deptno_arg-+-input-+-?-+-|+-+dname_arg-+-output-+-?-+- in that Stored Procedure Parameters box, but nothing worked.

Has anyone used 7.5.1A STP Stage? Please share your experience. Thanks.

chulett · Post by **chulett** » Wed Jul 05, 2006 11:14 pm

That's odd... I'm running 7.5.1A and my Stored Procedure stage has a Parameters tab on the Stage page.

Four tabs there - General, Syntax, Parameters and Error Codes. Perhaps something went wrong with your plug-in installation? You might want to try contacting Support and ask them what might cause something like that... and of course what to do about it.

sheema · Post by **sheema** » Mon Aug 21, 2006 2:47 pm

I am using Datastage 7.5 server edition.In the STP stage i do not have
Syntax, Parameters and Error Codes instead i just have all of these in the properties tab.so there is no way that i can map my parameters.I saw that you have faced the same problem.

Can you let me know,How you solved this problem.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 21, 2006 9:44 pm

Server or parallel?

chulett · Post by **chulett** » Mon Aug 21, 2006 10:29 pm

sheema wrote:I am using Datastage 7.5 server edition.

If you are using the original release of 7.5 then I would think that what you see is what you get. From what I recall, it isn't until the 7.5.1A release that something other than Oracle is supported by the stage and (apparently) the layout of the GUI was rearranged.