Stored Procedure issue

[Rick Smith]

We have a visual basic application that uses the iSeries for it's database. We are in the process of converting the database interfaces to stored procedures from individual SQL statements.

This is the scenario that is causing my problem:

Stored procedure A performs an insert using CS isolation level

Additional processing occurs within the VB application.

Stored procedure B performs a rollback.

The previously inserted record remains in the database and doesn't get rolled back. What settings or program options would I have to use so that a separate stored procedure can commit changes of another stored procedure that use the same connection?

Additional info: Connection method OLEDB, V5r4, Stored procedure compiled with *none for isolation level. Isolation level is handled within the SP for each statement. Also, this application didn't use commitment control previously, they decided to enhance it with the SP conversion.

Thanks in advance,
Rick

[SoftwareTrend]

Originally Posted by Rick Smith

Also, this application didn't use commitment control previously, they decided to enhance it with the SP conversion.

So the SP code is effectively processing no differently to the original SQL script calls and, as such, can not really do any better.

The most obvious consideration here is that you don't have a complete "transaction" at any one point. There is that little bit of VB code in between the SQL calls.

Is the VB code massaging the data or going back to windows for user input? Either way, why can't the first SQL script be run AFTER the little bit of VB code within a single SQL script?

[Rick Smith]

Originally Posted by SoftwareTrend

So the SP code is effectively processing no differently to the original SQL script calls and, as such, can not really do any better.

The most obvious consideration here is that you don't have a complete "transaction" at any one point. There is that little bit of VB code in between the SQL calls.

Is the VB code massaging the data or going back to windows for user input? Either way, why can't the first SQL script be run AFTER the little bit of VB code within a single SQL script?

I agree that the way the code is currently structured that it doesn't support unit of work processing very well. Before I tell people that they need to change the way they are processing, I'd like to investigate if there is a way to make this work without modifying the way application logic currently works.

[Lynne Noll]

I am interested in this concept.

All my stored procedures so far are complete units of work; they do their own commits and roll backs.

IBM provides support for a 'DUW' (distributed unit of work) so that commit can work across platforms, but I am not sure whether you have to buy anything--I know DB2 connect is supposed to support this. Basic CLI commits when you issue a commit command.

But first, are you sure that your connection method (JDBC, ODBC, OLEDB) data source is configured for commit control? If autocommit is turned on, it may be committing between invocations.

[Rick Smith]

Originally Posted by Lynne Noll

But first, are you sure that your connection method (JDBC, ODBC, OLEDB) data source is configured for commit control? If autocommit is turned on, it may be committing between invocations.

After doing some further research, I found that using the OLEDB provider was the problem. IBM states 'for complicated commitment control applications use ODBC" Once we changed the provider to ODBC the code worked fine.

Thanks everyone for your input.

Rick