Is this part of a EJB/session bean?
If so, do you have it set to Bean for the transaction-type in the
ejb-jar.xml or Container? If you have it set to container managed
transactions and do not specify a transaction attribute, one could presume
that it would be "Required".
I'd really recommend upgrading from WS 3.5 at this point.
-andy
> From: "Cohen, Meg" <[EMAIL PROTECTED]>
> Reply-To: "Research Triangle Java User's Group mailing
> list."<[EMAIL PROTECTED]>
> Date: Fri, 23 Jul 2004 11:14:01 -0400
> To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
> Subject: [Juglist] Java and DB2 Transactions
>
> We have a daemon running on WebSphere version 3.5 against DB2 for OS/390
> version 7.1.1. WebSphere and DB2 both run on an OS/390 mainframe, and the
> code for the daemon is deployed on the mainframe as well. This daemon is
> basically an event notification process. It reads records from a DB2 table,
> processes them, and then inserts the processed record into a log table and
> deletes it from the first table.
>
> We're trying to execute two separate direct SQL calls as a single
> transaction. The class that makes the call inherits from a parent class
> which takes care of getting the database connection, setting AutoCommit to
> false, and then either committing or rolling back the transaction,
> explicitly.
>
> The code in the parent class that creates the connection and handles the
> commit or rollback is:
> Connection con = null;
> Vector result = null;
> boolean success=false;
> try
> {
>
> con=ConnectionManager.getConnectionManager().getConnection();
> con.setAutoCommit(false);
> result = callSP(con, param);
> success=true;
> return result;
> }
> finally
> {
> try
> {
> if (con != null)
> {
> if (success)
> {
> con.commit();
> }
> else
> {
> con.rollback();
> }
> ConnectionManager.leaveConnection(con);
> }
> }
> catch (SQLException e)
> {
> }
> }
>
> callSP is an abstract method in the parent. In this case, the child class
> implements callSP method with the the following code:
>
> try
> {
> sql = "INSERT INTO TABLE1 (CFK_EVENT_TYPE_ID, CPK_EVENT_ID,
> " +
> "C_TS, CFK_USERID, CFK_PHYNO, CFK_MRNO, " +
> "C_KEY_01_VALUE, C_KEY_02_VALUE, C_KEY_03_VALUE,
> C_KEY_04_VALUE, " +
> "C_KEY_05_VALUE, C_KEY_06_VALUE, C_KEY_07_VALUE,
> C_KEY_08_VALUE, " +
> "C_KEY_09_VALUE, C_KEY_10_VALUE, C_KEY_11_VALUE,
> C_KEY_12_VALUE, " +
> "C_KEY_13_VALUE, C_KEY_14_VALUE, C_KEY_15_VALUE,
> C_KEY_16_VALUE) " +
> "SELECT CFK_EVENT_TYPE_ID, CPK_EVENT_ID, " +
> "C_TS, CFK_USERID, CFK_PHYNO, CFK_MRNO, " +
> "C_KEY_01_VALUE, C_KEY_02_VALUE, C_KEY_03_VALUE,
> C_KEY_04_VALUE, " +
> "C_KEY_05_VALUE, C_KEY_06_VALUE, C_KEY_07_VALUE,
> C_KEY_08_VALUE, " +
> "C_KEY_09_VALUE, C_KEY_10_VALUE, C_KEY_11_VALUE,
> C_KEY_12_VALUE, " +
> "C_KEY_13_VALUE, C_KEY_14_VALUE, C_KEY_15_VALUE,
> C_KEY_16_VALUE " +
> "FROM TABLE2 " +
> "WHERE CPK_EVENT_ID = ?";
> st1 = con.prepareStatement(sql);
> st1.setInt(1, eventID.intValue());
> st1.execute();
>
> sql = "DELETE FROM TABLE2 " +
> "WHERE CPK_EVENT_ID = ?";
>
> st2 = con.prepareStatement(sql);
> st2.setInt(1, eventID.intValue());
> st2.execute();
> }
> finally
> {
> if(st1!=null) st1.close();
> if(st2!=null) st2.close();
> }
>
> Because we have AutoCommit set to false, we were under the assumption that
> these two SQL calls would be treated as a single logical unit of work, and
> would either both be committed or both be rolled back. What we're seeing
> happen, though, is occasionally the record will be inserted into TABLE1 and
> not delete into TABLE2. This causes issues because this code is part of an
> event notification system which reads from TABLE2, processes a record, then
> inserts into TABLE1 and deletes from TABLE2. There's a unique index on
> TABLE1, so that if the record doesn't get deleted from TABLE2, it will be
> processed a second time, but the insert to TABLE1 will fail, which causes
> the entire daemon to come to a screeching halt.
>
> This only happens occasionally, but seems to always happen when we've
> brought WebSphere down. It does not happen, though, everytime we bring it
> down.
>
> We've tried to re-create this scenario using the WebSphere Test Environment
> in Visual Age for Java. Basically, in our tests, we've stepped through the
> code until the first SQL call has executed, and then stoped the WebSphere
> Test Environment. When we go look at DB2, the record has NOT been inserted
> into DB2, which is what we expect to happen. Obviously, though, something
> different is happening once the code is deployed to the mainframe.
>
> Has anyone tried anything similar to this? If so, did you encounter these
> problems? Is there a something other than setting AutoCommit to false that
> we need to be doing in the code?
>
> Thanks,
> Meg Cohen
> Applications Analyst Programmer
> Information Services Division
> UNC Health Care System
> voice: (919) 966-7605 / fax: (919) 966-2110
> mailto:[EMAIL PROTECTED]
> -----------------------------------------------------------------------
> In accordance with UNC Health Care System
> Policy the opinion(s) expressed in this document
> do not necessarily represent the opinion(s) of
> UNC Health Care System or its management
> -----------------------------------------------------------------------
>
>
>
>
> _______________________________________________
> Juglist mailing list
> [EMAIL PROTECTED]
> http://trijug.org/mailman/listinfo/juglist_trijug.org
_______________________________________________
Juglist mailing list
[EMAIL PROTECTED]
http://trijug.org/mailman/listinfo/juglist_trijug.org
