Finally got a setQueryTimeout() testcase to work with DB2. Had to write a standalone testcase that fires off multiple executeUdpate() calls, so the 2..n caller gets the expected timeout exception. Need to update the TestQueryTimeout.java code next to use a second thread to cause this exception to occur.

Example output when using DB2SimpleDataSourceExample -
Statement = update qtimeout set versionField = 2 where mod(id,2)=0
com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=3.50.152
at com.ibm.db2.jcc.b.wc.a(wc.java:55)
at com.ibm.db2.jcc.b.wc.a(wc.java:126)
at com.ibm.db2.jcc.b.tk.c(tk.java:1901)
at com.ibm.db2.jcc.t4.db.p(db.java:800)
at com.ibm.db2.jcc.t4.db.h(db.java:227)
at com.ibm.db2.jcc.t4.db.b(db.java:45)
at com.ibm.db2.jcc.t4.t.b(t.java:38)
at com.ibm.db2.jcc.t4.sb.g(sb.java:120)
at com.ibm.db2.jcc.b.tk.M(tk.java:1351)
at com.ibm.db2.jcc.b.tk.a(tk.java:2246)
at com.ibm.db2.jcc.b.tk.c(tk.java:601)
at com.ibm.db2.jcc.b.tk.executeUpdate(tk.java:585)
at DB2SimpleDataSourceExample.main(DB2SimpleDataSourceExample.java:103)

Example output when using MSSQLSimpleDataSourceExample against MS SQL 2005 SP1 -
Statement = update OPENJPA.dbo.QTimeout set versionField = 2 where id = 1
SQL Message = The query has timed out.
SQL State =
SQL Code = 0
Stack trace = com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeInterruptException(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.interrupt(Unknown Source)
at com.microsoft.sqlserver.jdbc.TimeoutTimer.run(Unknown Source)
at java.lang.Thread.run(Thread.java:613)

NOTE: There are no valid SQL State or Error Code being returned from the MS SQL 2000/2005 JDBC v1.2 driver.

Example output when using MSSQLSimpleDataSourceExample against MS SQL 2005 SP1 with the v2.0 JDBC driver (sqljdbc_2.0.1803.100_enu) for SQL Server 2000/2005/2008 -
Statement = update OPENJPA.dbo.QTimeout set versionField = 2 where id = 1
SQL Message = The query has timed out.
SQL State = HY008
SQL Code = 0
Stack trace = com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at com.microsoft.sqlserver.jdbc.TDSCommand.checkForInterrupt(IOBuffer.java:4214)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:70)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1449)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:786)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:685)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:642)
at MSSQLSimpleDataSourceExample.main(MSSQLSimpleDataSourceExample.java:95)

When using the MS SQL Server JDBC v2.0 driver and the Delay.dll test code on a SQL Server 2005 SP1 system, we can now catch the query timeout exceptions (only UPDATE tests testQueryTimeout31c and testQueryTimeout33c) and rethrow them as a QueryTimeoutException -

2134 TRACE [main] openjpa.jdbc.SQL - <t 11165595, conn 3833531> [1072 ms] spent
4255 test TRACE [main] Tests - testQueryTimeout31c() - Caught expected Exception =
<openjpa-2.0.0-SNAPSHOT-r422266:763301M nonfatal store error> org.apache.openjpa.persistence.QueryTimeoutException: A query statement timeout has occurred.
at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4379)
...
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: The query has timed out.

[code=0, state=HY008]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:236)
...

Patch in OPENJPA-1029 is required in order to use the MS SQL JDBC drivers, instead of jTDS.

Patch that completes the query timeout support for DB2 and MS SQL Server.
Note: This patch also contains the patch in OPENJPA-1029, but I'll reattach once that patch has been applied.

Updated patch with the latest SQLServerDictionary changes made in OPENJPA-1029.
Note: The testQueryTimeout31c() and testQueryTimeout33c() tests will only pass if the MS SQL JDBC v2.0 driver is used, as the 1.2 driver doesn't return a sql code that we can use to detect and rethrow the right exception type/text.

Example output from Oracle 11.1 -
Statement = update qtimeout set versionField = 2 where id = 1
SQL Message = ORA-01013: user requested cancel of current operation

SQL State = 72000
SQL Code = 1013
Stack trace = java.sql.SQLException: ORA-01013: user requested cancel of current operation

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1615)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1580)
at DirectQueryTimeoutTest.main(DirectQueryTimeoutTest.java:86)

Example junit test output testing for the exception -
997 TRACE [main] openjpa.Query - Executing query: [UPDATE QTimeout SET stringField = ? WHERE id = 1] with parameters:

997 TRACE [main] openjpa.jdbc.SQL - <t 9517133, conn 15562893> executing prepstmnt 1183826 UPDATE QTimeout SET stringField = ? WHERE id = 1 [params=(String) updated]
11144 TRACE [main] openjpa.jdbc.SQL - <t 9517133, conn 15562893> [10147 ms] spent
13305 test TRACE [main] Tests - testQueryTimeout31c() - Caught expected Exception =
<openjpa-2.0.0-SNAPSHOT-r422266:764279M nonfatal store error> org.apache.openjpa.persistence.QueryTimeoutException: A query statement timeout has occurred.
at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4379)
...
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01013: user requested cancel of current operation

[code=1013, state=72000]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:236)
...
NestedThrowables:java.sql.SQLException: ORA-01013: user requested cancel of current operation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

Splitting patch out into multiple files.
<OPENJPA-964-common.patch> - Updated patch for DB common files (sql-error-states-codes.xml, TestQueryTimeout.java and openjpa-persistence-jdbc/pom.xml). Should be applied to trunk first.

Updated db specific files. Should be applied to trunk after the <OPENJPA-964-common.patch> file and only updates the specific DBDictionary in each patch file.

For Informix - If the SQL request can be interrupted, the database server takes the following actions:
1. Discontinues execution of the current SQL request
2. Sets SQLCODE (sqlca.sqlcode) to a negative value (-213)
3. Returns control to the application
When the application regains control after an interrupted SQL request, any resources that are allocated to the SQL statement remain allocated. Any open databases, cursors, and transactions remain open. Any system-descriptor areas or sqlda structures remain allocated. The application program is responsible for the graceful termination of the program; it must release resources and roll back the current transaction.

Updated patches to cleanup some of the code changes in connectedConfiguration().
For DB2, now sets driverVendor (not sure wht this was missing.)
For MS SQL, picks up the latest changes in OPENJPA-1029.

Updated patch <OPENJPA-964-mssql.patch> for SQLServerDictionary which:
1) adds the matchErrorState() method and 2 imports needed for query timeout exception mapping
2) other changes are just Eclipse code formatting to cleanup white spaces and other problems

Example query timeout for Informix running the junit tests -
23784 TRACE [main] openjpa.jdbc.SQL - <t 3079897, conn 8779065> [7050 ms] spent
34562 test TRACE [main] Tests - testQueryTimeout31c() - Caught expected Exception =
<openjpa-2.0.0-SNAPSHOT-r422266:766044M nonfatal store error> org.apache.openjpa.persistence.QueryTimeoutException: A query statement timeout has occurred.
at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4379)
at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4335)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
at org.apache.openjpa.jdbc.kernel.SQLStoreQuery$SQLExecutor.executeUpdate(SQLStoreQuery.java:176)

Patch <OPENJPA-964-informix.patch> includes InformixDictionary updates for query timeout exception handling -
1) now properly sets VENDOR_IBM if the JCC driver is used
2) removes supportsQueryTimeout = false so we can set and catch timeouts
3) adds matchErrorState() to map a SQL Code = -213 to a QueryTimeoutException
This patch prereqs the <OPENJPA-964-common.patch> patch, which sets the SQL State = IX000 to check.

Updated patch <OPENJPA-964-common.patch> with:
1) SQL State added for Informix
2) TestQueryTimeout updates to support testing with Informix
3) New test-ids-jcc profile to make it easier to test Informix with the DB2 JCC driver

Jeremy, this JIRA is ready for your review. The <OPENJPA-964-common.patch> file needs to be applied first and then the dictionary specific updates (DB2, Oracle, MS SQL and Informix) can be applied afterwards in any order.

Committed patches OPENJPA-964-common.patch (4/17/09), OPENJPA-964-db2.patch, , OPENJPA-964-informix.patch, OPENJPA-964-mssql.patch, and OPENJPA-964-oracle.patch for Donald in trunk under revision 770297.

Thanks Jeremy.
I'll open another low priority JIRA to add Sybase support.