1

We have a JAVA application that connects to DB2 throught openJPA to fetch data. Preceeding almost every SQL call, we get a pretty useless call that comes in as a separate transaction: "SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1"

DBA says he doesn't have any idea why this above useless SQL statement gets called before almost every call.

Any idea why this query gets called? Is there anything I can do to skip this?

It's been a while I am stuck with this issue with DBA complaining about dramatically increment in the number of transaction hitting DB2.

Any help would be appreciated. Thanks

user3262365
  • 75
  • 1
  • 10
  • Probably a connection validation query executed by a connection pool implementation, to ensure the connection hasn't gone stale before being handed to the application code. See "[How to test the connection to a db2 database](http://stackoverflow.com/questions/4733356/how-to-test-the-connection-to-a-db2-database)". – Andreas Sep 26 '16 at 21:57
  • See also "[DBCP - validationQuery for different Databases](http://stackoverflow.com/q/10684244/5221149)" and "[What is a validationQuery with respect to databases and JNDI?](http://stackoverflow.com/q/15393954/5221149)" – Andreas Sep 26 '16 at 22:02
  • So it says Validation query is run by the data source to validate that a Connection is still open before returning it. Means by data source is by default running this query every time? Is there anyway I can limit this query because it is creating a overhead on DB2. It is over flooding DB2 and it is the main concern. – user3262365 Sep 26 '16 at 22:30
  • It only runs the validation when your app wants a connection anyway, but your app may run many statements on the same invocation. Since the validation query is a dummy, it takes no time to run, while app statements will do actual work that takes DB2 time to execute. *Worst case* scenario is that you get as many validation queries as other statements, though that's unlikely, and they are minuscule to the *work* DB2 has to do for the other statements. – Andreas Sep 26 '16 at 22:35
  • Note that [DBCP](http://commons.apache.org/proper/commons-dbcp/configuration.html) (a commonly used pooling library) will call [`Connection.isValid()`](https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#isValid-int-) if no validation query is configured, and javadoc says: *The driver shall submit a query on the connection or use some other mechanism that positively verifies the connection is still valid when this method is called.* That is probably where the actual query is coming from: the DB2 JDBC driver. – Andreas Sep 26 '16 at 22:43
  • Which WebSphere version are you using? If you have datasource configured in WAS, make sure that on `Data sources > YOUR_DATASOURCE > WebSphere Application Server data source properties` page you have UNCHECKED both `Validate connections..` options. – Gas Sep 27 '16 at 12:06
  • I am using WAS7. I will check that for that configuration as you say. Thanks – user3262365 Sep 27 '16 at 21:52
  • It worked for me @Gas. I had to uncheck that property. Thanks a lot. Thank you all. – user3262365 Sep 27 '16 at 22:21

2 Answers2

0

The query is coming from the connection pool manager, validating it before handing it over to the application. I think your connection pool implementation is at worst broken, at best misconfigured.

To begin with,

The query submitted by the driver to validate the connection shall be executed in the context of the current transaction.

http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#isValid(int)

So if the validation query comes outside of your transaction, it's non-compliant.

Second, as of JDBC4, the connection pool implementation is not required to run a validation query, it can use other means of validating the connection isn't closed or stale. Check HikariCP configuration for the discussion on this topic. I can't give you guarantees that the pool manager you're using is capable of being configured differently, but certainly check it, and maybe check it with alternative pool managers.

Yuri Steinschreiber
  • 2,648
  • 2
  • 12
  • 19
0

If you have datasource configured in WAS, make sure that on Data sources > YOUR_DATASOURCE > WebSphere Application Server data source properties page you have UNCHECKED both Validate connections.. options.

Gas
  • 17,601
  • 4
  • 46
  • 93