setAutoCommit and connection pooling

Joe Luthman

setAutoCommit and connection pooling
We are using DBConnectionBroker with Java applications, in order to ensure connection pooling is being used.
When we execute a Java command such as

con.setAutoCommit( true )

exactly when will the implied commit take place?    And using what Java method?   

Is it really true that be using the setAutoCommit, I can avoid having to explicitly invoke the commit method?

Joe Luthman                     260 Hayes Hall
DBA                             Information Technology Services
419-372-7750                    Bowling Green State University
                                Bowling Green, Ohio 43403

Andy Seuffert

Re: setAutoCommit and connection pooling
(in response to Joe Luthman)
Joe,
I cannot 100% verify what DB2 Connect will do in this situation, but I can tell you that as far as ODBC is concerned, when a JDBC driver issues a setAutoCommit(true) this will map to an ODBC call of SQLSetConnectOption fOption=SQL_AutoCommit(ON). According to the ODBC specification and the way our (NEON Systems Shadow Direct) JDBC and ODBC driver work (and I assume as well for DB2 Connect), this will cause an immediate commit to occur. So to answer your question, Yes, you can avoid issuing an explicit commit call if you are also calling setAutoCommit to True.

thanks,

Andy Seuffert
NEON Systems, Inc.

[login to unmask email]
http://www.neonsys.com < http://www.neonsys.com/ >

Tel: (281) 491-4200
Fax: (281) 242-3880


-----Original Message-----
From: Joe Luthman [mailto:[login to unmask email]
Sent: Friday, December 28, 2001 10:18 AM
To: [login to unmask email]
Subject: setAutoCommit and connection pooling


We are using DBConnectionBroker with Java applications, in order to ensure connection pooling is being used.
When we execute a Java command such as

con.setAutoCommit( true )

exactly when will the implied commit take place? And using what Java method?

Is it really true that be using the setAutoCommit, I can avoid having to explicitly invoke the commit method?



Joe Luthman 260 Hayes Hall
DBA Information Technology Services
419-372-7750 Bowling Green State University
Bowling Green, Ohio 43403 DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can

James Campbell

Re: setAutoCommit and connection pooling
(in response to Andy Seuffert)
Each statement will have its own commit.

To quote from the VA Java docs: " If a connection is in auto-commit mode, then all its SQL
statements will be executed and committed as individual transactions. Otherwise, its SQL
statements are grouped into transactions that are terminated by a call to either the method
commit or the method rollback. By default, new connections are in auto-commit mode. The
commit occurs when the statement completes or the next execute occurs, whichever
comes first. In the case of statements returning a ResultSet, the statement completes
when the last row of the ResultSet has been retrieved or the ResultSet has been closed. In
advanced cases, a single statement may return multiple results as well as output
parameter values. In these cases the commit occurs when all results and output parameter
values have been retrieved. "
James Campbell

On 28 Dec 2001 at 11:17, Joe Luthman wrote:

> We are using DBConnectionBroker with Java applications, in order to
> ensure connection pooling is being used.
> When we execute a Java command such as
>
> con.setAutoCommit( true )
>
> exactly when will the implied commit take place?And using what Java
> method?
>
> Is it really true that be using the setAutoCommit, I can avoid having
> to explicitly invoke the commit method?
>
> Joe Luthman260 Hayes Hall
> DBAInformation Technology Services
> 419-372-7750Bowling Green State University
> Bowling Green, Ohio
> 43403 To change your
> subscription
> http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-
> [login to unmask email]