Re: Cannot get QueryTimeout to work
Not sure if you use Statement.cancel() at all but this is an optional method for JDBC and requires a SQLFeatureNotSupportedException to be thrown if it is not supported (JDBC 4) Patrick Linskey wrote: Also, you may be interested in the OpenJPAEntityManager.cancelAll() method call, which uses different JDBC pathways to kill running queries. -Patrick
Re: Cannot get QueryTimeout to work
Statement.setQueryTimeout() is required to be implemented in order to achieve compliance with the JDBC specification. I would expect that current JDBC drivers from the majority of the JDBC driver companies have implemented this hopefully in a manner that works most of the time. Marc Prud'hommeaux wrote: FYI, setQueryTimeout() will meter call Statement.setQueryTimeout() on the underlying driver. JDBC drivers frequently either don't implement this, or their implementation is very flawed. Unfortunately, we haven't done detailed analyses of which drivers properly implement it or not, so I don't know if DB2 has a proper implementation of it. One way to test our whether it is supported is to write a small stand-alone test case that directly creates a JDBC Connection and tries to execute a slow or blocking SQL statement on a Statement after you call setQueryTimeout() and see if it correctly times out. If it doesn't, then the problem is with the driver. If it does, then there's something wrong with OpenJPA and we can investigate further. On Mar 22, 2007, at 4:47 PM, Don Brady wrote: Hi, I hope this is not off-topic on this list, but I cannot seem to get QueryTimeout to work, using the persistence.xml below under WebSphere 6.1 with a DB2 connection pool defined in WebSphere, under OpenJPA 0.9.6. It seems to just ignore the timeout specified and run to completion no longer how long it takes. I was wondering if the below should work or if there is another way of setting a query timeout. I cannot find one. Actually if I wrap it in a transaction, then I can set a timeout on the transaction and that does work. But I was avoiding using a transaction on the basis of advice in the book "Pro EJB" to omit transactions if they are not needed, in the case of a read-only query, because they may incur a performance penalty. Would that be true in OpenJPA for a large read-only query or should I just use a transaction and set the timeout on that? Thank you for any comments! Don persistence.xml contents: 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";> org.apache.openjpa.persistence.PersistenceProviderImpl jdbc/mship-prod jdbc/mship-prod META-INF/orm-prod.xml false value="sjvm" /> value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE" /> value="QueryTimeout=30"/>
RE: Cannot get QueryTimeout to work
Also, you may be interested in the OpenJPAEntityManager.cancelAll() method call, which uses different JDBC pathways to kill running queries. -Patrick -- Patrick Linskey BEA Systems, Inc. ___ Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it. > -Original Message- > From: Marc Prud'hommeaux [mailto:[EMAIL PROTECTED] On > Behalf Of Marc Prud'hommeaux > Sent: Thursday, March 22, 2007 5:09 PM > To: open-jpa-dev@incubator.apache.org > Subject: Re: Cannot get QueryTimeout to work > > > FYI, setQueryTimeout() will meter call > Statement.setQueryTimeout() on > the underlying driver. JDBC drivers frequently either don't > implement > this, or their implementation is very flawed. Unfortunately, we > haven't done detailed analyses of which drivers properly > implement it > or not, so I don't know if DB2 has a proper implementation of it. > > One way to test our whether it is supported is to write a > small stand- > alone test case that directly creates a JDBC Connection and tries to > execute a slow or blocking SQL statement on a Statement after you > call setQueryTimeout() and see if it correctly times out. If it > doesn't, then the problem is with the driver. If it does, then > there's something wrong with OpenJPA and we can investigate further. > > > > > On Mar 22, 2007, at 4:47 PM, Don Brady wrote: > > > Hi, > > > > I hope this is not off-topic on this list, but I cannot seem to > > get QueryTimeout to work, using the persistence.xml below under > > WebSphere 6.1 with a DB2 connection pool defined in WebSphere, > > under OpenJPA 0.9.6. > > > > It seems to just ignore the timeout specified and run to > > completion no longer how long it takes. > > > > I was wondering if the below should work or if there is another > > way of setting a query timeout. I cannot find one. > > > > Actually if I wrap it in a transaction, then I can set a timeout > > on the transaction and that does work. But I was avoiding > using a > > transaction on the basis of advice in the book "Pro EJB" to omit > > transactions if they are not needed, in the case of a read-only > > query, because they may incur a performance penalty. Would > that be > > true in OpenJPA for a large read-only query or should I just use a > > transaction and set the timeout on that? > > > > Thank you for any comments! > > > > Don > > > > > > persistence.xml contents: > > > > > > > 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"> > > > > > > > > > org.apache.openjpa.persistence.PersistenceProviderImpl > > > > jdbc/mship-prod > > > jdbc/mship-prod > > > META-INF/orm-prod.xml > > > false > > > > > >value="PROD" /> > > value="false" /> > > name="openjpa.RemoteCommitProvider" value="sjvm" /> > > > value="DefaultLevel=WARN, > Runtime=INFO, Tool=INFO, SQL=TRACE" /> > > > > value="db2(StoreCharsAsNumbers=false)" /> > > name="openjpa.TransactionMode" value="managed" /> > > name="openjpa.jdbc.TransactionIsolation" > > value="read-committed" /> > > > value="managed" /> > > name="openjpa.ConnectionFactoryProperties" > > value="QueryTimeout=30"/> > > > > > > > > > > > > > > Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.
Re: Cannot get QueryTimeout to work
FYI, setQueryTimeout() will meter call Statement.setQueryTimeout() on the underlying driver. JDBC drivers frequently either don't implement this, or their implementation is very flawed. Unfortunately, we haven't done detailed analyses of which drivers properly implement it or not, so I don't know if DB2 has a proper implementation of it. One way to test our whether it is supported is to write a small stand- alone test case that directly creates a JDBC Connection and tries to execute a slow or blocking SQL statement on a Statement after you call setQueryTimeout() and see if it correctly times out. If it doesn't, then the problem is with the driver. If it does, then there's something wrong with OpenJPA and we can investigate further. On Mar 22, 2007, at 4:47 PM, Don Brady wrote: Hi, I hope this is not off-topic on this list, but I cannot seem to get QueryTimeout to work, using the persistence.xml below under WebSphere 6.1 with a DB2 connection pool defined in WebSphere, under OpenJPA 0.9.6. It seems to just ignore the timeout specified and run to completion no longer how long it takes. I was wondering if the below should work or if there is another way of setting a query timeout. I cannot find one. Actually if I wrap it in a transaction, then I can set a timeout on the transaction and that does work. But I was avoiding using a transaction on the basis of advice in the book "Pro EJB" to omit transactions if they are not needed, in the case of a read-only query, because they may incur a performance penalty. Would that be true in OpenJPA for a large read-only query or should I just use a transaction and set the timeout on that? Thank you for any comments! Don persistence.xml contents: 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"> org.apache.openjpa.persistence.PersistenceProviderImpl jdbc/mship-prod jdbc/mship-prod META-INF/orm-prod.xml false value="QueryTimeout=30"/>
RE: Cannot get QueryTimeout to work
I don't know what behavior to expect out of JDBC drivers outside of a transaction. Regarding avoiding transactions: in most situations, you should not see any significant performance penalties for using transactions with OpenJPA. The authors of Pro EJB3 are all Oracle employees, so maybe there is something in their implementation that adds non-trivial overhead to transactions. -Patrick -- Patrick Linskey BEA Systems, Inc. ___ Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it. > -Original Message- > From: Don Brady [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 22, 2007 4:48 PM > To: open-jpa-dev@incubator.apache.org > Subject: Cannot get QueryTimeout to work > > Hi, > > I hope this is not off-topic on this list, but I cannot seem to get > QueryTimeout to work, using the persistence.xml below under WebSphere > 6.1 with a DB2 connection pool defined in WebSphere, under > OpenJPA 0.9.6. > > It seems to just ignore the timeout specified and run to > completion no > longer how long it takes. > > I was wondering if the below should work or if there is > another way of > setting a query timeout. I cannot find one. > > Actually if I wrap it in a transaction, then I can set a timeout on > the transaction and that does work. But I was avoiding using a > transaction on the basis of advice in the book "Pro EJB" to omit > transactions if they are not needed, in the case of a > read-only query, > because they may incur a performance penalty. Would that be true in > OpenJPA for a large read-only query or should I just use a > transaction > and set the timeout on that? > > Thank you for any comments! > > Don > > > persistence.xml contents: > > >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";> > > > > > org.apache.openjpa.persistence.PersistenceProviderImpl > > jdbc/mship-prod > > jdbc/mship-prod > > META-INF/orm-prod.xml > > false > > > value="PROD" /> >value="false" /> >name="openjpa.RemoteCommitProvider" value="sjvm" /> > value="DefaultLevel=WARN, > Runtime=INFO, Tool=INFO, SQL=TRACE" /> > > value="db2(StoreCharsAsNumbers=false)" /> >name="openjpa.TransactionMode" value="managed" /> >name="openjpa.jdbc.TransactionIsolation" > value="read-committed" /> > value="managed" /> >name="openjpa.ConnectionFactoryProperties" > value="QueryTimeout=30"/> > > > > > > > Notice: This email message, together with any attachments, may contain information of BEA Systems, Inc., its subsidiaries and affiliated entities, that may be confidential, proprietary, copyrighted and/or legally privileged, and is intended solely for the use of the individual or entity named in this message. If you are not the intended recipient, and have received this message in error, please immediately return this by email and then delete it.
