DSXchange

hi All,

I have a requirement where in I need to invoke PL/SQL stored procedure 3 times. i.e. I need to invoke a stored procedure before I kickstart a job(Sequence), during sequence execution (thatz when control passes from one ETL job to another ETL job within the sequence) and lastly after the completion of execution of Datastage Sequence job.
This process of invoking PL/SQL stored procedure to be carried out for all Datastage Sequence jobs I have.
Is there any mechanism by which we could make this approach generic and available to all Datastage sequence job.
This question is more of a architecture design issue, I believe. Can anybody please help me in designing an right approach for this within Datastage.

Thanks & regards
Sudhindra P S

Set up a shell script to invoke sqlplus (search the forum for techniques). You can then invoke this script from the master control job sequence or from any other job sequence (via an Execute Command activity) or even from a before-job subroutine invoking ExecSH.

hi Ray,

I have undergone training under you sometime back. That was really an excellent one I would say.
I had designed an similar strategy before where in I could make use of Shell Script to do the activity of invoking PL/SQL stored procedure using Execute command stage or by using Shell script as Wrapper for ETL jobs.
But the complexity what I have here is am exposing ETL jobs as Webservices(SOAP over HTTP) so, I will not be able to use Shell Script as Wrapper for ETL jobs exposed as Webservices. Do you have any alternative suggestions for me on to how can I handle this using some Wrapper programs other than shell script programming.
Again thank you very much for responding to my query as your suggestion is of ultimate one in this forum.

Thanks & regards
Sudhindra P S

Webservice wasn't mentioned in the original question.

I did not suggest using a shell script as a wrapper to the job - I suggested using a shell script to execute sqlplus to call the stored procedures from DataStage. This should still be a valid approach in an SOA environment.