Simulating a stored procedure function in DataStage

[bRycie]

Hi,

I am trying to simulate a Sybase stored procedure function using a DataStage routine.

I don't want to be using BCI as it is slow (I've tested this), and furthermore, I don't want to call a stored procedure
as we are hoping to have DataStage replace stored procedures.

What I want is a routine that connects to the Database (preferably NATIVELY ie Sybase Open Client)
and can execute an SQL script and return a result. (I don't even care about returning a result set of more than one row.)



HERE'S AN EXAMPLE OF WHAT I WANT TO DO.

/** If you can't open the attachment, please cut the paste the following code */

create proc sp_rabo_npv_delta (@ccy_cash_flow char(3),
@cash_flow_amount decimal(15,2),
@days int,
@ovrnghtday int,
@code_pay_receive char(1),
@npv_current decimal(15,2) output,
@delta_1bp decimal(15,4) output)
as

declare @yearbasis int,
@termlow float,
@termhigh float,
@yieldlow float,
@yieldhigh float,
@daysf float,
@i float,
@di float,
@yield float,
@inter_rate float,
@maxdays int,
@mindays int

/* Calculate npv and delta */

select @inter_rate = 0,
@yield = 0,
@termlow = 0,
@yieldlow = 0,
@termhigh = 0,
@yieldhigh = 0,
@daysf = @days


if not exists (select * from t_yield_code where currency_code = @ccy_cash_flow)
goto exitNoInterpol
else
select @yearbasis = year_basis from t_yield_code
where currency_code = @ccy_cash_flow

if (@days <= 0)
goto exitNoInterpol

if exists ( select * from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code
and c.currency_code = @ccy_cash_flow
and t.yield_term_day = @days)
begin
select @inter_rate = yield / 100.00 from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code and
c.currency_code = @ccy_cash_flow and
t.yield_term_day = @days
goto CalculateNPV_DI
end


select @maxdays = max(yield_term_day) from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code and
c.currency_code = @ccy_cash_flow


if (@days > @maxdays)
begin
select @inter_rate = yield / 100 from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code and
c.currency_code = @ccy_cash_flow and
t.yield_term_day = @maxdays
goto CalculateNPV_DI
end

select @mindays = min(yield_term_day) from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code and
c.currency_code = @ccy_cash_flow


if (@days < @ovrnghtday)
begin
select @inter_rate = yield / 100 from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code and
c.currency_code = @ccy_cash_flow and
t.yield_term_day = @mindays
goto CalculateNPV_DI
end

select @termlow = max(yield_term_day)
from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code
and c.currency_code = @ccy_cash_flow
and t.yield_term_day < @days

select @yieldlow = yield
from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code
and c.currency_code = @ccy_cash_flow
and t.yield_term_day = @termlow

select @termhigh = min(yield_term_day)
from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code
and c.currency_code = @ccy_cash_flow
and t.yield_term_day > @days

select @yieldhigh = yield
from t_yield_curve t, t_yield_code c
where t.yield_code = c.yield_code
and c.currency_code = @ccy_cash_flow
and t.yield_term_day = @termhigh

if @termlow = 0
select @inter_rate = @yieldhigh
else
if @termhigh = 0
select @inter_rate = @yieldlow
else
if @termhigh = @termlow
select @inter_rate = @yieldlow
else
select @inter_rate = ((((@daysf - @termlow) /
(@termhigh - @termlow)) * (@yieldhigh - @yieldlow)) + @yieldlow) / 100.00

/*
** select (@daysf - @termlow)
** select (@termhigh - @termlow)
** select (@yieldhigh - @yieldlow)
** select (@daysf - @termlow) / (@termhigh - @termlow)
** select (((@daysf - @termlow) / (@termhigh - @termlow)) * (@yieldhigh - @yieldlow))
** select @yearbasis
*/

CalculateNPV_DI:

If @yearbasis < @days
begin
select @i = power((1 + @inter_rate), (@daysf / convert(float, @yearbasis))) - 1
select @di = power(1 + (@inter_rate + .0001), (@daysf / convert(float, @yearbasis))) - 1
end
else
begin
select @i = @inter_rate * (@daysf / convert(float, @yearbasis))
select @di = (@inter_rate + .0001) * (@daysf / convert(float, @yearbasis))
end

select @npv_current = convert(decimal(15,2), (1 / (1 + @i)) * @cash_flow_amount)
select @delta_1bp = convert(decimal(15,4), round(((1 / (1 + @i)) * @cash_flow_amount) - (1 / (1 + @di)) * @cash_flow_amount, 4))

if @code_pay_receive = 'P'
select @npv_current = @npv_current * -1
else
select @delta_1bp = @delta_1bp * -1

/*
select @inter_rate
select @termhigh, @yieldhigh
select @termlow, @yieldlow
*/

return

exitNoInterpol:
if @code_pay_receive = 'P'
select @npv_current = @cash_flow_amount * -1
else
select @npv_current = @cash_flow_amount

select @i = 0,
@di = 0,
@delta_1bp = 0
return

 

[Starg]

Brycie,
Im a little confused about what you actually want? You want to call a stored procedure in a Sybase Open Client stage? if so its easy, simply change the QueryType from Generated to SQL Query, and in the SQL tab put in your Stored procedure (you can put placeholders in to use dynamic parameters). The only thing is that the stored procedure should output at least a single row (I output 0 or 1 based on the success of the stored procedure, which can then be easily trapped and trigger other events).
If that is not want you need to know, sorry
Starg

 

[giampiero]

bRycie,
I'm trying to simulate a stored procedure function using a Datastage routine, too.

Did you find how to do that?

Thanks

Giampiero