DSXchange

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

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.

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 ?

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.

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....

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....

I reccomend to search the forum. Calling stored procedure/ Executing SQL thru routine has been discussed many times out here.

i am using Datastage server 7.5 (server edition)... we have a stored procedure stage.

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.

Yes that's possible...but I would like to do it in the same job...

ray.wurlod wrote: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. ...

Then why not do it in the 'same job'.

JoshGeorge wrote:Then why not do it in the 'same job'.

Yes I am researching on how to use the stored procedure..

Thank You..

You DON'T NEED a stored procedure!!! Just use a DELETE command as the user-defined SQL.

ray.wurlod wrote:You DON'T NEED a stored procedure!!! Just use a DELETE command as the user-defined SQL. ...

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...


how can i prevent this...? the transaction size was set to 1 in the DB2 stage attached to Output link 1

By not doing it in the same job, as originally advised.

Or use a single target stage, not two.