Transaction Handling

[palmeal]

I have created a Job Server which has an ODBC task and a sequential file task on it.
The ODBC task connects to a Sybase 12.5 database and attempts to run a stored procedure. The Transaction Handling is set to None so I expected the default isolation of the database to be used. It is set to 1 on the Sybase Server (i.e. Read Committed).
When I run the Job Server it fails at the stored procedure (sp) stage. Part of the sp runs before it aborts. I know this as I have added logging to a sql table. The sp has no explicit transaction control.
If I go through all of the Transaction Handling options the only one that the sp will run to completion is Auto-Commit which going by the description in the help is not what I want.
Putting a begin..commit transaction statement around the code line made no difference.

Why doesn't the specification of None or Read Committed work in this case ?

A snippet of the stored procedure code follow below. The first 2 inserts to t_tuning_log are written but the final one after the insert statement is not - as said above this works with no problems when auto_commit is set :-

create procedure dbo.migrate_ptfo_data (@external_cd char(12) )
as
begin
set nocount on

insert t_tuning_log values (getdate(), @external_cd)

create table #time_perd
(
seq_no numeric(9,0) identity,
time_perd numeric(9,0),
start_date datetime,
end_date datetime,
calc_freq char(1)
)

insert t_tuning_log values (getdate(), 'After Create temp table')

insert #time_perd ( time_perd, start_date, end_date, calc_freq)
select time_perd, start_date, end_date, calc_freq
from t_time_perd t
where calc_freq = 'N'
and end_date = (select end_date from
t_time_perd t2, t_time_perd_type tp
where t.end_date = t2.end_date
and t2.calc_freq = 'Q'
and tp.period_type = 'Q'
and tp.period = 1
and tp.period_year = datepart(yy,t.end_date)
and t2.time_perd = tp.time_perd_fk)
and start_date = (select end_date from
t_time_perd t2, t_time_perd_type tp
where t.start_date = t2.end_date
and t2.calc_freq = 'Q'
and tp.period = 1
and tp.period_year = datepart(yy,t.end_date) - 1
and t2.time_perd = tp.time_perd_fk)


insert t_tuning_log values (getdate(), 'After initial insert')

[kcbland]

There's two uses for a stored procedure:

1. "do" something
2. stream output

Using the ODBC stage to "do" something is not how the stage is intended. It's meant to run a stored procedure to stream output back to the stage. Thus, you are required to have an output link even though no rows are expected.

I would guess the reason your post was ignored all day was because no one wanted to say that you are misusing the tool so that any issues are your own to solve. I'm known for very poor analogies, so here's one:

You're using a screwdriver to hammer a nail. The recommendation is to use the correct tool for the job. Since you're using Sybase, telling you to upgrade to 7.5 to used the stored procedure stage is worthless because only Oracle is supported. So, write a korn shell script to execute isql and do your own trapping and running of the stored procedure when you need to "do" something.

Just a little clarification, if your stored procedure: drops constraints, rebuilds indexes, grants, revokes, inserts data into tables, deletes rows, updates rows, etc. IT'S DOING SOMETHING. You have NO METADATA, tuning, or awareness of WHAT IS GOING ON when you turn over processing to a stored procedure. Your DS stop button doesn't work in Director if the stored procedure is off doing something. All control is out of DS's hands.

[palmeal]

Thanks for the reply Kenneth. Guess I'm mis-using the tool due to my Comapny's reluctance to provide me with any training thinking that I can pick up everything quickly from the available documentation. Not surprised that I'm doing things against the grain - it's not the easiest tool to pick up but once I get something working the solution seems so simple - e.g. Job Control and passing parameters - not very intuative but when working very straightforward. Anyway I digress !!!


I received a note from a different post stating that 7.5.1 now includes the Stored Procedure Stage for Sybase. Is this version widely available or in beta ? Has anyone used this and noticed any problems with it ?


viewtopic.php?t=91446&highlight=
Posted: Fri Mar 04, 2005 5:54 pm Reply with quote Back to top
--------------------------------------------------------------------------------
DataStage 7.5.1 now GA includes the Stored Procedure Stage with support added for Sybase. DB2 was also added with this release.

Steve

[palmeal]

OR

Should I be attempting just to use user defined SQL breaking my stored procedures into more Job Server tasks ?

[chulett]

As noted in the post you quoted, it is GA or Generally Available. You'd have to check at the eSupport site to see if it was available for your particular platform.

And, yes, my general advice to peoples is to replace stored procedures like this with a DataStage 'equivalent'.

Lastly - please avoid the use of user defined sql in DataStage unless you really need it. The people who come after you will thank you.