Nope, it's not a bean at this point. Should it be? We're trying to get off of 3.5, but it's about 3rd on our Tech. Services group's priority list right now, after bringing up a new mainframe and moving our data center! Last I heard we're slated to be on 5.0 sometime this fall. Do you think that would make a difference in this instance?
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 ----------------------------------------------------------------------- -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 23, 2004 11:43 AM To: [EMAIL PROTECTED] Subject: Re: [Juglist] Java and DB2 Transactions 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 _______________________________________________ Juglist mailing list [EMAIL PROTECTED] http://trijug.org/mailman/listinfo/juglist_trijug.org
