Using PocketQuery Plugin with DB2 on OS390

[Tim]

Hi,

we are trying to use PocketQuery to connect to DB2 on a OS390 host. The connection seems to work, here is the log file:

2014-03-13 16:22:23,658 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: Connecting to database SAP_TEST - jdbc:db2://db2.example.com:5912/E3Q
2014-03-13 16:22:23,660 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: Start measuring execution time...
2014-03-13 16:22:23,661 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: Trying to connect to database SAP_TEST | jdbc:db2://db2.example.com:591
2/E3Q | COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver | db2intra
2014-03-13 16:22:23,662 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: Trying to load database driver COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver
2014-03-13 16:22:23,664 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: I could load the driver =)
2014-03-13 16:22:23,677 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: I'm connected =)
2014-03-13 16:22:23,678 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: End measuring execution time. Result: 18 ms.
2014-03-13 16:22:23,679 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: Successfully connected! Now executing query SAP_TEST_Department_Table
2014-03-13 16:22:23,681 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: Start measuring execution time...
2014-03-13 16:22:23,693 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: End measuring execution time. Result: 12 ms.
2014-03-13 16:22:23,694 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: Got an Exception disconnecting from the database
2014-03-13 16:22:23,696 DEBUG [TP-Processor67] [plugins.pocketquery.util.LogUtil] debug ====> PocketQuery says: There was an error: Error disconnecting the database.

The exception is

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10251][10308][3.62.56] java.sql.Connection.close() requested while a transaction is in progress on the connection. The transaction remains active, and the connection cannot be closed. ERRORCODE=-4471, SQLSTATE=null 
at com.ibm.db2.jcc.am.fd.a(fd.java:660)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:120)
at com.ibm.db2.jcc.am.jb.u(jb.java:1240)
at com.ibm.db2.jcc.am.jb.x(jb.java:1262)
at com.ibm.db2.jcc.am.jb.v(jb.java:1248)
at com.ibm.db2.jcc.am.jb.close(jb.java:1230)
at de.scandio.confluence.plugins.pocketquery.managers.SqlExternalDatabaseManager.disconnect(SqlExternalDatabaseManager.java:111) at 
[...]

In the release notes of Differences between the IBM Data Server Driver for JDBC and SQLJ and the IBM Informix JDBC Driver at http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.jccids.doc/com.ibm.db2.luw.apdv.java.doc/doc/r0052865.htmwe found the following note:

--- snipp ---

Closing connections with active transactions

When closing a connection during an active transaction, IBM Data Server Driver for JDBC and SQLJprevents the connection from closing and throws the following exception:

[ibm][db2][jcc][t4][10251] [10308][<var class="varname">driver version</var>] "java.sql.Connection.close() requested while a transaction is in progress on the connection. The transaction remains active, and the connection cannot be closed. ERRORCODE=-4471, SQLSTATE=<var class="varname">sqlstate</var>"

Whereas, the Informix JDBC driver closes the connection and automatically rolls back the active transaction.

--- snipp ---

This seems to be a common problem, which is discussed all over the net. On https://issues.apache.org/jira/browse/SOLR-2045we even found a patch for it which looks like this:

private void closeConnection() {
  try {
    if (conn != null) {
      if (conn.isReadOnly()) {
        LOG.info("connection is readonly, therefore rollback"); conn.rollback(); 
      }
      else { 
        LOG.info("connection is not readonly, therefore commit"); conn.commit();
      }

      conn.close();
    }
  }
  catch (Exception e) {
    LOG.error("Ignoring Error when closing connection", e); }
  }
}

So please, can you evaluate this patch and add it to

de.scandio.confluence.plugins.pocketquery.managers.SqlExternalDatabaseManager.disconnect(SqlExternalDatabaseManager.java:111) ?

Best regards Tim

[Felix Grund (Scandio) · Accepted solution]

Hi Tim,

this is an interesting issue. We're currently setting auto-commit to false in PocketQuery, which I assume is why you're experiencing this problem. From my point of view right now, we can safely run all statements in PocketQuery with auto-commit enabled (which is the default). We're actually only having read-only SELECT statements to handle and also we're not iterating through a result set anywhere, but query the database for a list of objects (using org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForList).

I will collaborate some more over this issue and do some testing. I'll release a new version 1.12 with some cool new features soon. A fix for your issue will be part of this release.

Thanks for using PocketQuery! Let me know if you need further help.

Regards,
Felix

[Felix Grund (Scandio)]

Find our new release 1.12 at the Atlassian Marketplace. There is also a blog article on the release. Please let me know if the new version solves your problem!

[Christian Koch]

Find our new release 1.12 at the Atlassian Marketplace. There is also a blog article on the release. Please let me know if the new version solves your problem!

[Philipp Göllner (Scandio)]

Hi TtheB,

i commited the change to the project. After code reveiw and tests we will release an new version shortly.

Best regards

Philipp