From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED], "Research Triangle Java User's Group mailing list."<[EMAIL PROTECTED]>
Date: Fri, 23 Jul 2004 16:18:25 -0400
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
Subject: re: [Juglist] Java and DB2 Transactions
So you turned off commiting the two transactions with the specified connection until the end. But, what about checking if either of the transactions
failed?
I would be using hibernate or JDO instead of creating your own classes for talking with a SQL database.
David M. Bloom
---
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
