Using PocketQuery Plugin with DB2 on OS390

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

2 answers

1 accepted

1 vote
Accepted answer

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

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!

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!

Hi TtheB,

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

Best regards

Philipp

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 29, 2018 in Marketplace Apps

How to set up an incident workflow from the VP of Engineering at Sentry

Hey Atlassian community, I help lead engineering at Sentry, an open-source error-tracking and monitoring tool that integrates with Jira. We started using Jira Software Cloud internally last year, a...

1,336 views 0 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you