Calling stored procedure.
Moderators: chulett, rschirm, roy
Calling stored procedure.
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
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
Datastage is pretty interesting
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
sp
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
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
Datastage is pretty interesting
sp
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
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
Datastage is pretty interesting
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?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
sp
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 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.
Datastage is pretty interesting
sp
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.
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.
Datastage is pretty interesting
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?
![Confused :?](./images/smilies/icon_confused.gif)
I'm wondering why you wouldn't simply encapsulate the 'functionality' of that SP directly into your job?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.