Hi Meg,
First, your basic understanding for transactions is correct. I assume
that while your catch blocks here for SQLException are empty, you take
proper action in the real code. I might suggest checking for warnings as
well.
But the more I look at your post, the more questions I have. The first,
I guess, is your "daemon". How exactly is that running? If you're starting
up a thread, you may interfere with WebSpheres's control of things. From
another response, you're not using EJB's of any description. Regardless,
you should be able to see DB2's logs, possibly WebSphere's, and possibly
journals to get an idea what is happening.
When you say this:
> 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.
do you mean that the problem *only* occurs when you bring
WebSphere down, and then only sometimes, or does it sometimes
also occur while WebSphere is running? A J2EE container often caches
data, but sometimes problematic asumptions can be made when outside
processes change data as well. How do you bring it down? Is it "graceful"
or just killing it?
I see you are using execute(). While execute() should be capable of
running almost anything, it's much more normal practice to use
executeUpdate() when altering data. I'd suggest changing to that at least
briefly to get the update counts so you know what JDBC thinks is happening.
The other thing, which ends up an "I don't know" because I would never
program that way, is that you are closing the Statements before issuing the
commit() or rollback(). "I don't know" if that has any impact or not, but
it looks odd to me.
Sorry to make this a query rather than an update to your post, but all
of these seem to me to be areas that need looking into. HTH, or at least
gives you some ideas.
Joe Sam
Joe Sam Shirah - http://www.conceptgo.com
conceptGO - Consulting/Development/Outsourcing
Java Filter Forum: http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International? http://www.jguru.com/faq/I18N
Que Java400? http://www.jguru.com/faq/Java400
----- Original Message -----
From: "Cohen, Meg" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, July 23, 2004 11:14 AM
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]
_______________________________________________
Juglist mailing list
[EMAIL PROTECTED]
http://trijug.org/mailman/listinfo/juglist_trijug.org
