execute stored proc in job?

achang · Post by **achang** » Fri Aug 06, 2004 3:33 pm

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

alokkum · Post by **alokkum** » Fri Aug 06, 2004 5:00 pm

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.

tonystark622 · Post by **tonystark622** » Fri Aug 06, 2004 7:45 pm

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

chulett · Post by **chulett** » Fri Aug 06, 2004 8:20 pm

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'.

suma · Post by **suma** » Tue Aug 10, 2004 4:51 am

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).

ray.wurlod · Post by **ray.wurlod** » Tue Aug 10, 2004 5:25 am

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.