Re: cursor/prepared statement problem with WebLogic 10.3.2
Hi Matthias, we had a similar problem with OpenJPA 2 running on WebSphere 6.1 with DB2: no errors in the local dev environments, strange DB2/JDCB errors in integration. We did a lot of research before we discovered that our WebSphere in integration used different custom properties set on the DB2 JDBC driver configuration: resultSetHoldability=2 The default is 0 (which was the value used in the dev environments). See also: http://www-01.ibm.com/support/docview.wss?rs=404context=SS7K4Uuid=swg21287376loc=en_UScs=UTF-8lang=en HTH, Tobias -- View this message in context: http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5769782.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
Hi Tobias, thanks for your response. My problem is indeed similar to yours. The JDBC connection pool property I will go for is keepDynamic=1. In dependency to another parameter on the z/OS DB2 sub-system, this should bring us the prepared statement cache at Db2 (server) side. In previous releases of our software we also used prep statement, but only within transactions. JPA is re-using them over commits. With respect to resultSetHoldability=2, I saw in the attached link the this param should have value '1' for activation. What does '2' means here? - Kind regards Matthias -- View this message in context: http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5769960.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
munter wrote: With respect to resultSetHoldability=2, I saw in the attached link the this param should have value '1' for activation. What does '2' means here? Have a look at http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/rjvdsprp.htm 1 = HOLD_CURSORS_OVER_COMMIT 2 = CLOSE_CURSORS_AT_COMMIT Best wishes, Tobias -- View this message in context: http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5770005.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
I am also not familiar with spy logs. I approached a colleague who is good at this and here is what his suggestions are: 1 - Quick workaround: Turn off WLS statement caching (set cache size to 0) 2 - Get latest version of the rebranded driver from Oracle. That may fix the problem, allowing statement caching again. If not, file a DD Driver bug with Oracle. This should be replicable outside openJPA with simple jdbc calls (calling same prepared statement twice). If you just repeat the exact same jdbc calls (that you see in spy logs), then you should be able to replicate the issue. The question is whether this happens *every time* a prepared statement for the same SQL is re-used. It is either a simple driver bug (where the cleanup JDBC code WLS do under the covers is messing up the DBMS's cursor state), or if there is some other application activity that messes up the DBMS (not as likely). -- View this message in context: http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5767713.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
Hi Pinaki, even the approach of taking the JPA Call out of the session bean fails. Now it also fails, when no rollback is issued. My persistence.xml looks like this: ?xml version=1.0 encoding=UTF-8? persistence version=1.0 xmlns=http://java.sun.com/xml/ns/persistence; xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance; xsi:schemaLocation=http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd; persistence-unit name=LPMJPA transaction-type=JTA jta-data-sourcejdbc/lpmDs/jta-data-source mapping-fileobject-relational-mapping.xml/mapping-file /persistence-unit /persistence The transaction-type as I understand means container-managed, therefore I don't care about transactions in my code. Going for RESOURCE_LOCAL I need at least obtain a reference for the entity manager, correct? Thanks. I appreciate your help. - Kind regards Matthias -- View this message in context: http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5754916.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
If it is replicable on customer's dev/test environment, then is it possible to use spy log ? If you are using weblogic branded db2 driver then you can enable the spy log with following parameters in the URL: jdbc:weblogic:db2://rtdc60018:48056 becomes jdbc:weblogic:db2://rtdc60018:48056;spyAttributes=(log=(file)spy.log;timestamp=yes;logTName=yes) -- View this message in context: http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5755745.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
Hi Matthias, I think the annot 'TransactionAttributeType.REQUIRES' is not feasible here, Yes you are right. I overlooked that you rethrow the exception from the catch block. Unfortunately I am still not able to reproduce this on my system, but i happens regularly at client site. Check if both your local environment and client using RESOURCE_LOCAL transaction. - Pinaki -- View this message in context: http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5750344.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
Hi Pinaki,
pls find below the code:
public void myMethod(UserBean userBean) throws Exception {
final String taskName = someConstantInterface.TASK_XYZ_NAME;
TaskManagerLocal taskManager = null;
try {
taskManager = getTaskManager(); //retrieve manager ref from JNDI
taskManager.startTask(taskName);
taskLockAcquired = true;
// here certain business logic happens that may fail with an
exception ***/
taskManager.finishTask(taskName);
return retSentFiles;
} catch (Exception e) {
if (taskLockAcquired) {
taskManager.finishTask(taskName);
}
throw e;
}
}
-
Kind regards
Matthias
--
View this message in context:
http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5744103.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
Hi Matthias, The DB2 error code suggests that the SELECT query used a cursor but the associated database transaction of the cursor has been committed or rolled back. 1. Do you see within TaskManager.finishTask() any possibility of ending the database transaction before findOrCreateTask() ? 2. I can not fathom, from the presented information, the status of the transaction when finishTask() is invoked. Is it likely that the transaction has been rolled back even before finishTask() is invoked? - Pinaki -- View this message in context: http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5744409.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
Hi Pinaki,
my thoughts go in the same direction.
Here the code of the task manager bean:
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void finishTask(String name) {
Task task = findOrCreateTask(name);
task.setFailureCount(0);
task.setRunningSince(null);
task.setState(TaskState.IDLE);
//entityManager.flush();
logger.log(Log.DEBUG, Task + name + finished.);
}
private Task findOrCreateTask(String name) {
Task task = entityManager.find(Task.class, name);
if (null == task) {
task = new Task(name);
entityManager.persist(task);
}
return task;
}
...
I don't no actively close the db2 connection, the surrounding transaction in
myMethod(...) should be rolled back, by the container but after the method
left by exception and not before its internal catch block. Btw. 'myMethod()'
is tagged as Transaction.Requires (in ejb-jar.xml)
-
Kind regards
Matthias
--
View this message in context:
http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p577.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: cursor/prepared statement problem with WebLogic 10.3.2
Hi Matthias,
the surrounding transaction in myMethod(...) should be rolled back, by the
container but after the
method left by exception and not before its internal catch block.
I agree.
Here is another vista to explore
'myMethod()' is tagged as Transaction.Requires
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void finishTask(String name) {
Task task = entityManager.find(Task.class, name); // effectively
Say myMethod() is invoked in txn T1. So entityManager is in T1.
myMethod() bombed.
Internal catch block called finishTask().
finishTask() with REQUIRES_NEW is invoked on a new txn T2.
But same entityManager.find() is now invoked on txn T2, though T1 has not
ended yet.
Now that may make things hairy.
How about trying?
@TransactionAttribute(TransactionAttributeType.REQUIRES)
public void finishTask(String name) {
-
Pinaki
--
View this message in context:
http://openjpa.208410.n2.nabble.com/cursor-prepared-statement-problem-with-WebLogic-10-3-2-tp5724027p5745513.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
