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
Call Stored procedures
Moderators: chulett, rschirm, roy
Call Stored procedures
SWKYDERBY
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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.
I am not sure of the options the SQL Server plugin offers, but check the documentation.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.