DB2Driver; rollback closing resultsets

[Tor Heigre]

Hello

While testing our code on DB2 we have encountered a difference in the
behaviour of DB2Driver (com.ibm.db2.jc c.DB2Driver with driverType= 4)
compared to the drivers offered by Oracle 9i and Sybase SQL Anywhere 7.

Our system contains code like this (java pseudocode)

Connection con = <>
con.setAutoComm it(false);
PreparedStateme nt psel = con.prepareStat ement(<select from table A
where...>);
...
ResultSet res = psel.executeQue ry();
while (res.next())
{
boolean success = <Perform various inserts and updates in other tables
than table A>;
if (success)
con..commit();
else
con..rollback() :
}
...
try{con.setAuto Commit(true);}c atch (Exception err){}


The purpose of this structure is to catch and rollback errors, but also to
keep processing the remaining lines from the resultset.
This works fine in both Oracle and Sybase. But in DB2 a rollback call causes
the resultset to close.

Why does this happen ? is this behaviour a bug in the DB2Driver ? has it
been fixed ?

We need to decide our next step here, If we can't get DB2Driver to behave
the way we expect,
we have to consider looking at alternative drivers or rewriting out code
(which will be unplesant).

Comments are welcome.

Tor

[Ian]

Re: DB2Driver; rollback closing resultsets

Tor Heigre wrote:[color=blue]
> Hello
>
> While testing our code on DB2 we have encountered a difference in the
> behaviour of DB2Driver (com.ibm.db2.jc c.DB2Driver with driverType= 4)
> compared to the drivers offered by Oracle 9i and Sybase SQL Anywhere 7.
>
> Our system contains code like this (java pseudocode)
>
> Connection con = <>
> con.setAutoComm it(false);
> PreparedStateme nt psel = con.prepareStat ement(<select from table A
> where...>);
> ...
> ResultSet res = psel.executeQue ry();
> while (res.next())
> {
> boolean success = <Perform various inserts and updates in other tables
> than table A>;
> if (success)
> con..commit();
> else
> con..rollback() :
> }
> ...
> try{con.setAuto Commit(true);}c atch (Exception err){}
>
>
> The purpose of this structure is to catch and rollback errors, but also to
> keep processing the remaining lines from the resultset.
> This works fine in both Oracle and Sybase. But in DB2 a rollback call causes
> the resultset to close.
>
> Why does this happen ? is this behaviour a bug in the DB2Driver ? has it
> been fixed ?
>
> We need to decide our next step here, If we can't get DB2Driver to behave
> the way we expect,
> we have to consider looking at alternative drivers or rewriting out code
> (which will be unplesant).
>[/color]

This is language independent. I have run into this using perl, and I was
able to work around it by opening 2 separate connections to the database.
Example,

Connection con1 = <> // for reading only
Connection con2 = <> // for your actual transactions
con2.setAutoCom mit(false);
PreparedStateme nt psel = con1.prepareSta tement(<select from table A
where...>);
....
ResultSet res = psel.executeQue ry();
while (res.next())
{
boolean success = <Perform various inserts and updates in other tables
than table A>;
if (success)
con2..commit();
else
con2..rollback( ):
}
....
try{con2.setAut oCommit(true);} catch (Exception err){}



I can't speak to why this is different between the DB2/Oracle/Sybase
implementation. If this workaround is not sufficient you might want to
open a PMR with IBM.


Good luck,



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Sean McKeough]

Re: DB2Driver; rollback closing resultsets

A result set is part of the calling statements unit of work. Rollback
will always close all cursors for the application (including result sets).

Tor Heigre wrote:[color=blue]
> Hello
>
> While testing our code on DB2 we have encountered a difference in the
> behaviour of DB2Driver (com.ibm.db2.jc c.DB2Driver with driverType= 4)
> compared to the drivers offered by Oracle 9i and Sybase SQL Anywhere 7.
>
> Our system contains code like this (java pseudocode)
>
> Connection con = <>
> con.setAutoComm it(false);
> PreparedStateme nt psel = con.prepareStat ement(<select from table A
> where...>);
> ..
> ResultSet res = psel.executeQue ry();
> while (res.next())
> {
> boolean success = <Perform various inserts and updates in other tables
> than table A>;
> if (success)
> con..commit();
> else
> con..rollback() :
> }
> ..
> try{con.setAuto Commit(true);}c atch (Exception err){}
>
>
> The purpose of this structure is to catch and rollback errors, but also to
> keep processing the remaining lines from the resultset.
> This works fine in both Oracle and Sybase. But in DB2 a rollback call causes
> the resultset to close.
>
> Why does this happen ? is this behaviour a bug in the DB2Driver ? has it
> been fixed ?
>
> We need to decide our next step here, If we can't get DB2Driver to behave
> the way we expect,
> we have to consider looking at alternative drivers or rewriting out code
> (which will be unplesant).
>
> Comments are welcome.
>
> Tor
>
>[/color]

[Mark A]

Re: DB2Driver; rollback closing resultsets

"Sean McKeough" <mckeough@nospa m.ca.ibm.com> wrote in message
news:bqo1l4$1e6 $1@hanover.toro lab.ibm.com...[color=blue]
> A result set is part of the calling statements unit of work. Rollback
> will always close all cursors for the application (including result sets).
>[/color]
I don't know about "result sets" but a declared cursor can be held open past
the commit/rollback using the WITH HOLD option.

[Dmitri Liakh]

Re: DB2Driver; rollback closing resultsets

May be the following IBM doc link will help you (holdability section).

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/cjvrsush.htm

In short: in db2 there are 2 types of the cursors, "with hold" cursors
should cross the transaction bounadries.

We do use "with hold" in SQL stored procedures, but JDBC stored
procedures seem to work fine without any "holdabilit y" modifications
(7.2 fixpack 6 and 8.1 fixpack 3).

regards,
dmitri

[Sean McKeough]

Re: DB2Driver; rollback closing resultsets

With hold will keep the cursor open through a commit, but not a rollback.

More details...
WITH HOLD
Maintains resources across multiple units of work. The effect of
the WITH HOLD cursor attribute is as follows:

*****Forr units of work ending with COMMIT:
o Open cursors defined WITH HOLD remain open. The cursor
is positioned before the next logical row of the results table.

If a DISCONNECT statement is issued after a COMMIT
statement for a connection with WITH HOLD cursors, the held cursors must
be explicitly closed or the connection will be assumed to have performed
work (simply by having open WITH HELD cursors even though no SQL
statements were issued) and the DISCONNECT statement will fail.
o All locks are released, except locks protecting the
current cursor position of open WITH HOLD cursors. The locks held
include the locks on the table, and for parallel environments, the locks
on rows where the cursors are currently positioned. Locks on packages
and dynamic SQL sections (if any) are held.
o Valid operations on cursors defined WITH HOLD
immediately following a COMMIT request are:
+ FETCH: Fetches the next row of the cursor.
+ CLOSE: Closes the cursor.
o UPDATE and DELETE CURRENT OF CURSOR are valid only for
rows that are fetched within the same unit of work.
o LOB locators are freed.
******** For units of work ending with ROLLBACK:
o All open cursors are closed.
o All locks acquired during the unit of work are released.
o LOB locators are freed.
[color=blue]
> Mark A wrote:
>[color=green]
>> "Sean McKeough" <mckeough@nospa m.ca.ibm.com> wrote in message
>> news:bqo1l4$1e6 $1@hanover.toro lab.ibm.com...
>>[color=darkred]
>>> A result set is part of the calling statements unit of work. Rollback
>>> will always close all cursors for the application (including result
>>> sets).
>>>[/color]
>>
>> I don't know about "result sets" but a declared cursor can be held
>> open past
>> the commit/rollback using the WITH HOLD option.
>>
>>[/color]
>[/color]

[Sean McKeough]

Re: DB2Driver; rollback closing resultsets

With hold will keep the cursor open through a commit, but not a rollback.

Mark A wrote:[color=blue]
> "Sean McKeough" <mckeough@nospa m.ca.ibm.com> wrote in message
> news:bqo1l4$1e6 $1@hanover.toro lab.ibm.com...
>[color=green]
>>A result set is part of the calling statements unit of work. Rollback
>>will always close all cursors for the application (including result sets).
>>[/color]
>
> I don't know about "result sets" but a declared cursor can be held open past
> the commit/rollback using the WITH HOLD option.
>
>[/color]