Stored Procedure
Moderators: chulett, rschirm, roy
Stored Procedure
How do I do a stored procedure to do the following
IF #RERUN_IND#='Y' THEN
DELETE FROM TBL_A WHERE TBL_A.MONTH=#MONTH_IND#;
ELSE 0
Pls Advice
Thanks
IF #RERUN_IND#='Y' THEN
DELETE FROM TBL_A WHERE TBL_A.MONTH=#MONTH_IND#;
ELSE 0
Pls Advice
Thanks
Re: Stored Procedure
hello,
you have to create a procedure in database having input parameters that you require for your processing.
Next in Stored_Procedure stage goto --> syntax tab -->give the name for procedure --> uncheck generate procedure call ---> then call your procedure like for example
BEGIN p2('#val#'); END;
#val# is the input parameter for job which could be directly used as a value in your procedure.
you have to create a procedure in database having input parameters that you require for your processing.
Next in Stored_Procedure stage goto --> syntax tab -->give the name for procedure --> uncheck generate procedure call ---> then call your procedure like for example
BEGIN p2('#val#'); END;
#val# is the input parameter for job which could be directly used as a value in your procedure.
Re: Stored Procedure
Hello sachin,
Thanks but I am using the server edition and I believe the stored_proc is not available....
so can I use the statement I mentioned earlier in a routine ? is the sysntax correct ?
Thanks but I am using the server edition and I believe the stored_proc is not available....
so can I use the statement I mentioned earlier in a routine ? is the sysntax correct ?
sachin1 wrote:hello,
you have to create a procedure in database having input parameters that you require for your processing.
Next in Stored_Procedure stage goto --> syntax tab -->give the name for procedure --> uncheck generate procedure call ---> then call your procedure like for example
BEGIN p2('#val#'); END;
#val# is the input parameter for job which could be directly used as a value in your procedure.
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Re: Stored Procedure
The ODBC stage has stored procedure as one of its options in server edition.
cosec wrote:
Thanks but I am using the server edition and I believe the stored_proc is not available....
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
Re: Stored Procedure
Ok thanks ...but can I also use this SQL statement in a routine ?
JoshGeorge wrote:The ODBC stage has stored procedure as one of its options in server edition.
cosec wrote:
Thanks but I am using the server edition and I believe the stored_proc is not available....
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
I reccomend to search the forum. Calling stored procedure/ Executing SQL thru routine has been discussed many times out here.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
Re: Stored Procedure
i am using Datastage server 7.5 (server edition)... we have a stored procedure stage.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Why not just create a job to delete the rows? Use user-defined SQL, supply the date (as a job parameter) and refer to this in the WHERE clause of your DELETE statement.
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.
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Then why not do it in the 'same job'.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I tried the delete command in Output Link 1 and the insert in Output Link 2. But when I do this The table gets locked...ray.wurlod wrote:You DON'T NEED a stored procedure!!! Just use a DELETE command as the user-defined SQL. ...
how can i prevent this...? the transaction size was set to 1 in the DB2 stage attached to Output link 1
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: