stored procedure

[jpockets]

I tried searching through the forums but the answers seem to be a little over my head. What i'm trying to do is i have a datastage job that goes

oracle-----> transformer-----> SQL once the job completes i want to trigger a sql query that executes 4 stored procedures.

SQL Query that i want to run after datastage job finishes

EXEC sp1
Go
Exec sp2
Go
EXEC sp3
GO
EXEC sp4

I'm not sure what the easiest way to complete this. Can i do it in the datastage job? Or do i need to build a sequence for this? Any help is greatly Appreciated

[chulett]

You could script this to run 'After Job' or create a separate job and use a Sequence job to run the two one after the other. Or you try executing them in the After SQL tab of the last stage.

[jpockets]

Chulett,
Thank-you for the reply.... If I go to Edit -- Job Properties--- General Tab and select After-job subroutine: and select ExecSH. In the input value box i'm not sure what I would write there to execute the stored procedure. I'm a basic user of datastage and still trying to learn as i go.

If I want to run all for stored procedures can i list them one after another?

[jpockets]

Also if i go to database and select the Stored Procedure Icon and i click on properties i get a the details. The Database Vendor in the drop down there is only Oracle, but the stored procedure is on an MYSQL database, i try to type in mysql but i get an error that says please enter a valid database vendor, is there any way around that?

[chulett]

The initial release only supported Oracle, so you won't be able to use the actual Stored Procedure stage for this, it seems.

[chulett]

That 'After Job' ExecSH executes a shell command, meaning either O/S command or a script. You need a script to connect to your db via the command line utility they provide and then issue the four proc calls you noted.

[chulett]

Why not try putting them in the After SQL tab of your last stage?