I am connecting to DB2 database on mainframe. It does not support the default validationQuery - "values 1" As such every 4th call fails. There is no way to turn of the validation either. I tried setting the testOnBorrow property to false. It did not help as when validationQuery is available it defaults to true.

public class DB2DataSourceCreator extends DataSourceCreator {

private static final String[] DRIVERS = new String[]{"com.ibm.db2.jcc.DB2Driver"};
private static final String VALIDATION_QUERY = "VALUES 1";

public DB2DataSourceCreator() {
    super("spring-cloud.db2.driver", DRIVERS, VALIDATION_QUERY);
}

} will it be possible to provide the query?

Comment From: snicoll

What is this VALUES 1? Is that the proper validation query for DB2 mainframe? is the database nalme DB2 UDB for AS/400 ?

I assume that you're using the Tomcat connection pool, you can change the validation query using spring.datasource.tomcat.validation-query. I am happy to change the default validation query but I need to be able to do thos only for that DB2 flavour.

Comment From: ap1256

Values 1 does not work on mainframe DB2. I get 'THE SQL STATEMENT IS NOT SUPPORTED. SQLCODE=-142, SQLSTATE=42612, DRIVER=3.64.106 SQL Code: -142, SQL State: 42612' I did try adding the environment property spring.datasource.tomcat.validation-query but that did not help. I searched to see if there was any standard validation query that would work on mainframe db2 and non-mainframe. Did not get a good answer. I use this one. Which works on both versions of DB2 - AIX, Linux and mainframe. I didn't test with UDB. SELECT 1 FROM sysibm.sysdummy1

Comment From: snicoll

Can you share a sample that demonstrates the issue please? That still doesn't answer my question and we don't have VALUES 1 anywhere in the code. The validation query we choose for DB2 is SELECT 1 FROM SYSIBM.SYSDUMMY1.

Comment From: ap1256

Oh. The snipet I put is from DB2DatasourceCreator in spring-cloud-spring-service-connector-1.2.3.RELEASE.jar jdbctemplate.zip Here is my sample code.

Comment From: snicoll

@ap1256 please create the issue in the spring cloud services project, we don't auto-configure the datasource that way. Thanks!

Comment From: ap1256

Here is the sample where I am not using the cloud connector. The Datasource is created using 'values 1' even though i have the validationQuery in the url and as property.

  OUT 2017-02-03 12:47:09.315  INFO 19 --- [           main] urceCloudServiceBeanFactoryPostProcessor : Reconfigured bean dataSource into singleton service connector org.apache.tomcat.jdbc.pool.DataSource@59494225{ConnectionPool[defaultAutoCommit=null; defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null; driverClassName=com.ibm.db2.jcc.DB2Driver; maxActive=4; maxIdle=100; minIdle=0; initialSize=0; maxWait=30000; testOnBorrow=true; testOnReturn=false; timeBetweenEvictionRunsMillis=5000; numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=60000; testWhileIdle=false; testOnConnect=false; password=********; url=jdbc:db2://************:446/****:user=******;password=*****;currentSchema=TPSDB01;; username=null; validationQuery=VALUES 1; validationQueryTimeout=-1; validatorClassName=null; validationInterval=3000; accessToUnderlyingConnectionAllowed=true; removeAbandoned=false; removeAbandonedTimeout=60; logAbandoned=false; connectionProperties=null; initSQL=null; jdbcInterceptors=null; jmxEnabled=true; fairQueue=true; useEquals=true; abandonWhenPercentageFull=0; maxAge=0; useLock=false; dataSource=null; dataSourceJNDI=null; suspectTimeout=0; alternateUsernameAllowed=false; commitOnReturn=false; rollbackOnReturn=false; useDisposableConnectionFacade=true; logValidationErrors=false; propagateInterruptState=false; ignoreExceptionOnPreLoad=false; }

jdbctemplate.zip

Comment From: philwebb

I can't get the sample to compile as the following dependencies aren't in maven central:

<dependency>
    <groupId>com.ibm.db2.driver</groupId>
    <artifactId>db2jcc</artifactId>
    <version>3.65.102</version>
</dependency>
<dependency>
    <groupId>com.ibm.db2.driver</groupId>
    <artifactId>db2jcc_license_cu</artifactId>
    <version>3.62.56</version>
</dependency>

Have you tried putting a breakpoint on org.apache.tomcat.jdbc.pool.DataSourceProxy.setValidationQuery to see where the query is coming from? As far as I can tell none of our driver definitions (in org.springframework.boot.jdbc.DatabaseDriver) declare such a validation query.

Comment From: snicoll

@philwebb the reporter said it was a cloud issue after all but unfortunately they deleted their comment.

Comment From: dbnex14

I am having same issue. My dataSource is set as below. Spring Boot app, deployed to Liberty server where dataSource is set

    <dataSource id="DB2DSe" jndiName="jdbc/myapiDB2DSe">
        <jdbcDriver libraryRef="Db2Lib" />
        <connectionManager maxPoolSize="1" minPoolSize="1" />
        <properties.db2.jcc 
            driverType="4"
            serverName="..." 
            portNumber="..." 
            currentFunctionPath="..."
            currentSchema="..." 
            databaseName="..." 
            user="..."
            password="..."
      validationQuery="select 1 from sysibm.sysdummy1" />
    </dataSource>

I can perform CRUD operations just fine, but, in ordeder to do healthchecks on db2, I also added validationQuery, but when I do health check, I get:

                "db2DS": {
                    "details": {
                        "database": "DB2",
                        "validationQuery": "isValid()",
                        "error": "org.springframework.dao.InvalidDataAccessApiUsageException: 
                        ConnectionCallback; DSRA1300E: Feature is not implemented: 
                        Connection.isValid; nested exception is java.sql.SQLFeatureNotSupportedException: 
                             DSRA1300E: Feature is not implemented: Connection.isValid"
                    },
                    "status": "DOWN"
                },
               ...

Comment From: wilkinsona

@dbnex14 That appears to be unrelated to this issue which, until your comment, made no mention of Liberty.

For Spring Boot to use the validation query it needs to be made available by the metadata for the DataSource. Spring Boot can retrieve it automatically for the most commonly uses connection pools. Without knowing which connection pool you're using, I can't say if it should work out of the box. If it's a Liberty-specific DataSource you will need to define your own org.springframework.boot.jdbc.metadata.DataSourcePoolMetadataProvider bean that knows how to retrieve the required metadata from Liberty's DataSource implementation.

If you have any further questions, please follow up on Stack Overflow or Gitter. As mentioned in the guidelines for contributing, we prefer to use GitHub issues only for bugs and enhancements.