Page 1 of 1
execute stored proc in job?
Posted: Fri Aug 06, 2004 3:33 pm
by achang
Hello,
Is there a way to execute a stand alone stored procedure? I have a data stage job which creates a table. The stored procedure uses this table and deletes records from about 20 tables. Is there a way to execute this stored procedure after the datastage job finished creating the initial table?
Thanks
achang
Posted: Fri Aug 06, 2004 5:00 pm
by alokkum
You can wrap the stored procedure in a shell and call the shell in the after job subroutine to execute that shell which has that stored procedure wrapped with it.
or
you can call that procedure in your target OCI , SQL's after tab in the same job which creates that table.
Posted: Fri Aug 06, 2004 7:45 pm
by tonystark622
There's a new stage in v7 that will let you call a stored proc, I think. Check out the documentation for DRS (sp?) stage. Update: Now I'm not sure, but Ascential was working on an Oracle stored proc stage that was in beta in Feb. I haven't seen it yet, though./Update
Tony
Posted: Fri Aug 06, 2004 8:20 pm
by chulett
It
is available, Tony - in
7.5. In addition to the DRS (Dynamic Relational Stage) which is a whole 'nuther animal, there is a new SP or Stored Procedure stage. It allows usage of stored procedures as Source, Target or Lookup stages. Haven't taken it for a spin yet, but it is definitely there.
Oracle only right now, but other databases supported 'soon'.
Posted: Tue Aug 10, 2004 4:51 am
by suma
Hi,
U can execute a stored Procedure from Datastage.You will hv to choose the update action as call stored procedure from the target Stage.
Before that you will have to import the corresponding stored procedure
thro manager ,I think you must be aware of that.
As you have given a condition like you are creating a table in the target and then you have to fire the Procedure. You can call the stored procedure from the OCI Stage. In SQL tab you can choose after and call the procedure by CALL procname(param).
Posted: Tue Aug 10, 2004 5:25 am
by ray.wurlod
Stored procedures called from within DataStage jobs must process at least one row of data.
"Stand alone" suggests that your stored procedure does not fall into that category. If you have a licensed ODBC driver you might contemplate using a routine using BCI functions; otherwise you will need to use a "shell wrapper" as suggested.