Calling stored procedure.

r.julia · Post by **r.julia** » Thu Jan 25, 2007 3:16 am

Hi,

I need to call a stored procedure(sybase and teradata). Would it be better to use a routine or normal ljob creation? Could someone show me the stage required if I would need to create a job?

This is what I did...

Teradata API -TRANSFORMER -TERADATA

It doesnt seem to make sense to me though...as I only require the job or routine to call the stored procedure.

I saw some code on this site:

CALL ROOTDB.STOREDPROCEDURENAME(arg)

Please advise.

Regards,
Julia

sb_akarmarkar · Post by **sb_akarmarkar** » Thu Jan 25, 2007 3:47 am

Hi,

You can use ODBC stage to call store procedure.

In ODBC stage import --> store procedure defination - >Provide arguments.

In ODBC parameter tab provide value or parameter in value section...

Thanks,
Anupam

r.julia · Post by **r.julia** » Thu Jan 25, 2007 4:41 am

Hi,
Thanks for the reply. The procedure should insert some values(all values are specified in the procedure) into sybase and teradata database.Thats what the procedure should do.

Would it be fine to have a stage like this:

/-->SYBASE
ODBC --/-> TERADATA

In the ODBC stage, import the stored procedure like you said, then what about teradata and sybase? IS there anything necessary to do there, or simply loading the necessary columns?

Thanks
Julia

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 8:31 am

Try with one database first. Just load the columns. When one works, build the same in a seperate job for the second. Keep them seperate. You dont want the failure of one to effect the smooth execution of the other.

r.julia · Post by **r.julia** » Thu Jan 25, 2007 10:19 am

Hi Guru,
Thanks for the reply.Assuming Im trying with Teradata,

ODBC ---> TERADATA

In the ODBC stage, do I select using Stored procedure in the Ouputs-General Tab and then enter the stored proc name in the text box? Or theres no need to do this..instead in the target Teradata stage, call the stored procedure: CALL SP_NAME(param) in the SQL-before tab?

Please advise.

Thanks
Julia

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 10:27 am

Well that depends on what the Stored Procedure is doing. Are you retrieving columns from it to load it somewhere or just executing the Stored Procedure that does a few tasks which, from DataStage stand point, your not concerned about?

r.julia · Post by **r.julia** » Thu Jan 25, 2007 10:37 am

The stored procedure is inserting some values into a specific column. The job should call the stored procedure and populate the column.

DSguru2B · Post by **DSguru2B** » Thu Jan 25, 2007 10:56 am

What version of DataStage do you have.? If 7.5x then you must be having STP stage. Use that.
If you are expecting return parameters then doing a simple "call stp" from before/after sql tab is out of question.
Calling a parameter from odbc stage has its limitations. It can only support IN IN/OUT parameters. When you import your stored procedure, do you get any warnings. If your return value is instantiated as a Return or OUT parameter, it will not work.

r.julia · Post by **r.julia** » Sat Jan 27, 2007 2:13 pm

DSguru2B wrote:What version of DataStage do you have.? If 7.5x then you must be having STP stage. Use that.
If you are expecting return parameters then doing a simple "call stp" from before/after sql tab is out of question.
Calling a parameter from odbc stage has its limitations. It can only support IN IN/OUT parameters. When you import your stored procedure, do you get any warnings. If your return value is instantiated as a Return or OUT parameter, it will not work.

I have Stored procedure stage but the problem is it doesnt call a teradata procedure. So using the SP stage is out of the question. The procedure simply inserts into a database, so I thought using the call sp_name("xxx") is the only option as far as Im concerned, but it doesnt work..Am I doing something wrong?

DSguru2B · Post by **DSguru2B** » Sat Jan 27, 2007 2:19 pm

Do it in the before/after sql of DRS stage.

r.julia · Post by **r.julia** » Sat Jan 27, 2007 3:00 pm

DSguru2B wrote:Do it in the before/after sql of DRS stage. ...

Do I need to call the procedure in both before and after or just BEFORE?
Sorry to bother you..

DSguru2B · Post by **DSguru2B** » Sat Jan 27, 2007 3:23 pm

Just one place. Either before OR after.

r.julia · Post by **r.julia** » Mon Jan 29, 2007 3:07 am

Hi Guru,

This is what I did.

TeradataAPI ->(Transformer optional) -> Teradata

In the Source table, I loaded the table and connected to the teradata database, in the target,however, I called the stored procedure to do the inserting in the SQL-before. Its not working unfortunately. The table definition for both the source and the target are the same. I just expect the content to be different.

After calling the SP -> CALL SP_NAME("3455"), 3455 should be inserted in the target DB.

Heres the SP:

REPLACE PROCEDURE FootballClubs.AstonVilla (
)
BEGIN

INSERT INTO FootballClubs.Team( PlayerName )
VALUES ( 'King' );
END;

King should be inserted into the target DB.
Please advise.

chulett · Post by **chulett** » Mon Jan 29, 2007 7:31 am

I'm just curious... are you doing this as a learning exercise? Is that just a made up example or the actual SP you are trying to run?

I'm wondering why you wouldn't simply encapsulate the 'functionality' of that SP directly into your job?

DSguru2B · Post by **DSguru2B** » Mon Jan 29, 2007 7:46 am

When you say its not working, what do you mean? Is it not executing at all, are you getting syntax error. Look into your log file. Plus looking into your stored proc. code, it does a direct insert. Your design will be simple. Something like
TeraData API ------->Seq. File/Transformer
You will place your stored proc. call in either the before or after sql tab. Try fully qualifying your stored proc. name.
And if it does not work, give us error messages.