How to use Input link values as stored procedure arguments?

[pelmen]

Hi all!
I have a small problem can anybody help me?
Env: DataStage ver 7.5 , MS SQL Server

On a DB server there is a
stored procedure proc1(arg1, arg2) - returns ResultSet [col1,col2...colN]

Job contains a stage (ODBC or Stored procedure) with:
Input link [input_col1,input_col2...input_colN]

How to call proc1(input_col2,...input_colN,...input_col1)
to receive ResultSet (Output limk) ?

Can it be done in ODBC-stage OR Stored procedure stage ?
Are there any special syntax capabilities to pass column values like proc1(?,?,?,?)?

Stored procedure in my case allows only "Oracle" Database vendor...
Does it works with MS SQL?

Waiting for your help!

[loveojha2]

Can it be done in ODBC-stage OR Stored procedure stage ?

Yes it can be done with ODBC stage.
I my case also stored procedure stage shows Oracle only.

How to call proc1(input_col2,...input_colN,...input_col1)
to receive ResultSet (Output limk) ?

Select Stored procedure and provide Stored procedure name it appears like DBName.schemaname.procname;1 Click apply.
In columns tab load the column definitions of ResultSet.
In parameters you will see the parameter that needs to be passed (remove @ symbol in front of the parameters if its there) and in Value provide the value you want to pass to the stored procedure.

I guess I included everything required.

Hope this will help you.

[pelmen]

I need to pass each Input row [input_col1,input_col2...input_colN]
as a parameter!
No one knows the value of Input column at design-time! Only at run - time values vill be passed...

pay attention to the Output link:
call proc1(input_col2,...input_colN,...input_col1)

How to refer from Output tab to the Input tab????

[chapichapo]

Hi!

I have the same problem... I've found only one solution which is not great : open the input file (with a job control) to get input values and put them in job parameters of the job that will call ODBC with the stored procedure ( {call stored_procedure(#param1#, #param2#)}; )and get the result sets then in output columns. I'm not very happy but it works... if someone have a better solution than this , please help us!

I wonder why it's so difficult to do these kind of things????

Someone coul tells me what is the best way to write stored procedures that will be only used with Datastage, I remember we can put error messages in stored procedure that will appear in the job log but i don't remember exactly how....

[Joe5]

Hi!

I have the same problem... :cry: I've found only one solution which is not great : open the input file (with a job control) to get input values and put them in job parameters of the job that will call ODBC with the stored procedure ( {call stored_procedure(#param1#, #param2#)}; )and get the result sets then in output columns. I'm not very happy but it works... if someone have a better solution than this , please help us!

I wonder why it's so difficult to do these kind of things????

Someone coul tells me what is the best way to write stored procedures that will be only used with Datastage, I remember we can put error messages in stored procedure that will appear in the job log but i don't remember exactly how....

I have the same problem, and it is a BIG issue. chapichapo's solution of calling using #parm# does seem to work, BUT, and it is a BIG one. as far as I can tell, you can only set the #parm# thru the use of a basic program that reads a file ( to get the value for the parm) & sets the #parm# value. since the #parm# is a job parm it can only be set at the start of a job. the BAD news is that there seems to be about a 2 second latency everytime an input rec is read and the job called. this is no big deal if you have few input records, but if you have thousands, the jog could take days to finish. I am fairly new to datastage, so hopefully I have missed something. if anyone can tell me a way around this, it would be greatly appreciated. we really need this & we are not scheduled to go to 7.5.1.a for a while. much thanks,

[ray.wurlod]

Welcome aboard. :D

There is necessarily an overhead in calling stored procedures. So you try not to; manage the computation within DataStage using local variables and expressions, and you avoid the latency.

[loveojha2]

So you try not to; manage the computation within DataStage using local variables and expressions, and you avoid the latency.

Ray can you please explain how this can be achieved through local variables and expressions.

Thanks in advance.

[ray.wurlod]

Not really, because I have no idea what you want to do.

By "local variables" I mean, of course, stage variables in a Transformer stage. And expressions, well these might be stage variable initialization expressions, stage variable derivation expressions, output link constraint expressions or output column derivation expressions.

It all depends on your algorithms where you do things.

[Joe5]

So you try not to; manage the computation within DataStage using local variables and expressions, and you avoid the latency.

Ray can you please explain how this can be achieved through local variables and expressions.

Thanks in advance.

thanks for the reply. Yes, you are correct regarding the latency with SPs. That is NOT the latency that is the issue (it is only 10 ms per call). There are many times in a design when you MUST use a SP. this can be for many reasons, but sometimes it is necessary. Please assume that this question is for the situation when it is absolutly necessary.

WIth ODBC stage there does NOT appear to be any good way to access a SP in any sort of 'normal' way. The way that I am referring to is:
1. read some input data (seq rec or RDB, it does not matter).
2. take several values from the input data & use then as the INPUT variables to the SP.
3. Call the SP.
4. the SP does lots of "complicated" stuff . . .
5. THe SP returns some resultant data items to DataStage.
6. DS continues to process those data items.

So far, tho only way I can figure out how to do this, is to use a #parms1# type paramater. these can only be set at the start of a Job.
the seq is:
1. run a Basic routine that reads a seq file to get INPUT data.
2. set the parms (#parms1#, #parms2#, etc) to the INPUT variables.
3. have the Basic routine Start up the DS JOB that has the SP call in it.
4 the DS Job uses ODBC Stage to call the SP using the parms (#parm1#,#parm2#,etc)

this works, but there is a 2-3 sec latency FOR EVERY RECORD READ. this seems to be the time it takes to start Each DS Job. Unfortunately That renders solution unusable. If you have any other way I can do this, your suggestions are greatly appreciated. DS seems to be a pretty good product & it would be dissapointing if you cannot call a SP with it (in a usable way).. thanks

[ray.wurlod]

Why can't you run one job that reads the sequential file, and uses the values thus found (perhaps decoded via a Transformer stage, for example Field(InLink.column, "=", 2, 1)) and feed these values to the ODBC stage that calls the stored procedure?

You may need to rearrange the file (so that all parameters for a call are on the one line) or to perform a vertical pivot within the DataStage job for this approach to be viable.

[raj_konig]

Folks,

If you guys are talking regarding passing parameters for the stored procedure as input values, then we can easily achieve this by creating user-defined transforms ans call those transforms in the procedure. this will allow you to given the input values during run-time.

rajesh

[Joe5]

Folks,

If you guys are talking regarding passing parameters for the stored procedure as input values, then we can easily achieve this by creating user-defined transforms ans call those transforms in the procedure. this will allow you to given the input values during run-time.

rajesh

rajesh & Ray, I appreciate your suggestions, but I am not sure how to code either of them. While I am very experienced (in IT), I am new to dataStage. If you could find the time to give me some detailed instructions on either of your solutions, I would greatly appreciated it.

I have spent many hours trying to make this work. I have even received some input from an IBM/Ascential consultant & and they knew of no other way. it is kind of suprising that DS would have this limitation, because if true, I view it as a MAJOR omission. I am hopeful that it is not.

The only way I have have found (to be able to pass parms to a SP & get results) is to:
1. Use ODBC Stage with an Output only link. the output is to a Seq file.
2. use #parm# job parms to dynamically pass the input variables.
3. the #parm# job parms is passed on the "Parameters" tab of the Output tab ( in ODBC Stage)
4. the SQL generated is {call MyStoredProc('#parm1#', '')};


I can get the SP to work if I have {call MyStoredProc(('inputValue',?)}; in an ODBC stage and do "user defined sql". I can capture the output from the SP (?) and send it to a transformer.

what I cannot do is have variable input. in this example, the input value is FIXED & therefore not what I want to do.

with the first example, the parm can only be set at the start of the Job & it has the horrible performance issues mentioned in the earlier post.

A detailed solution would be greatly appreciated. if it is easier, and you could do a POC & export the Job & send it to me, that may be the quickest way to let me know how to do it. thanks alot, Joe

[ray.wurlod]

Sorry I am really busy at the moment and don't have the time - particularly unpaid time - to do detailed POCs. Maybe someone else does.
My previous post on this thread should be sufficient guidance for an experienced DataStage developer.