Call Stored procedures

[SWW]

How do I call a Stored Procedure in DS. I am trying to update a datetable with the most recent date.

My job uses that date in determining what data to pull from another table. I have included the matching criteria in the selection tab as follows:

sql_simulcast.dbo.Settlement.Location = 'CAL'
and
sql_simulcast.dbo.Settlement.Location = sql_simulcast.dbo.DateLookup.LocationCode
and
sql_simulcast.dbo.Settlement.Date > sql_simulcast.dbo.DateLookup.LkupDate

All the separate tables are defined and this statement works.

Before or after this runs I would like to update the DateLookup table.

Any suggestions?

Thanks

[chucksmith]

Look at the ODBC stage. For input, there is a stored procedure radio button. For output there is a "Call Stored Procedure" update action.

I am not sure of the options the SQL Server plugin offers, but check the documentation.

[ray.wurlod]

There isn't a "SQL Server plugin" for SELECT, so you use ODBC or OLEDB stage type. So you can happily use "Call Stored Procedure" as the method for retrieving data.

Calling a Stored Procedure before retrieving data is a different problem entirely, and not really supported directly by DataStage. Since you're on Windows, and don't have ODBC licensing issues, you could create a before-stage subroutine that uses BCI functions to CALL your stored procedure to update the DateLookup table.

Or you could create a DataStage job that does the same thing. Create a job sequence that calls that one first (to update the DateLookup table), and the other one depending on the successful completion of the first.

[dzdiver]

Not sure about SQL Server, but with Oracle OCI stage can call SPs and Functions just by embedding the SP call into a select statement of a reference link such as: -

SELECT funcX(:1, :2) REGION,
:1,
:2
FROM dual

Should you wish to call it only once during a job, you could create a "parallel job" inside your main job (or indeed seperate it and call it from some BASIC in a wrapper job) consisting of an OCI stage, a dummy transformer stage and a sequential file stage to output to. The OCI could have something like: -

select SPX(), 'dummy' from sys.dual

Pass the dummy through the transformer.
I have also use this for passing parameters out of jobs into files...such as dates for other jobs to pick up later.

rgds,
B.