Dbcp - Validationquery for Different Databases

DBCP - validationQuery for different Databases

There is not only one validationQuery for all databases. On each database you have to use different validationQuery.

After few hours of googling and testing I have collected this table:

Database validationQuery notes

• hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
• Oracle - select 1 from dual
• DB2 - select 1 from sysibm.sysdummy1
• mysql - /* ping */ select 1
• microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
• postgresql - select 1
• ingres - select 1
• derby - values 1
• H2 - select 1
• Firebird - select 1 from rdb$database
• MariaDb - select 1
• Informix - select 1 from systables
• Hive - select 1
• Impala - select 1

I wrote about it on my blog - validation query for various databases.

In advance there is an example of class, which return validationQuery according to JDBC driver.

Or does anybody have better solution?

Efficient SQL test query or validation query that will work across all (or most) databases

The jOOQ manual's section about the DUAL table lists the following for jOOQ's select(inline(1)) query:

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1

-- MemSQL, Oracle
SELECT 1 FROM DUAL

-- CUBRID
SELECT 1 FROM db_root

-- Db2
SELECT 1 FROM SYSIBM.DUAL

-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird
SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY

-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

DBCP 1.3 Validation Query Degrading Performance

1) No. Try

testOnBorrow=true

testOnReturn=false

testWhileIdle=false

2)

you may get stale(broken) connection for escaping validation. however, you may 'tweak' timeBetweenEvictionRunsMillis ...that thread executes every 5 seconds to evict idle connection

3)

connection cannot be validated without validationQuery in DBCP 1.3

Dbcp connection pooling create more conncetion as expected

The default dbcp connection pool is 8 connections, so if you want to run 9 simultaneous queries one of them will be blocked.
From the javadocs, you should be able to read this from the getNumIdle() and getNumActive() methods, if you can get an instance of the BasicDataSource
or connect to the database and
run exec sp_who2 which will show you what is connected, and active, and whether any queries are being blocked.
One suggestion is their if you are aware about it DBCP,it has more issues and I would suggest you to use HikariCP, it has got the most of it (ideal for performance related tasks) here the link which will give you a useful for demo and lastly use Spring's SimpleJdbcTemplate which are more involved in simplicity.

Problem in Oracle JDBC Connection Configuration/JDBC Request - JMETER

1. I don't think your URL is correct, it should be something like:

   jdbc:oracle:thin:@your-oracle-hostname-or-ip-address:your-oracle-port:your-oracle-SID
   

2. Correct JDBC Driver fully qualified name is oracle.jdbc.driver.OracleDriver

3. The "Validation query" should be select 1 from dual

4. You will need to download Oracle JDBC Driver and drop it to JMeter Classpath

More information:

• OracleDriver Documentation
• DBCP - validationQuery for different Databases
• The Real Secret to Building a Database Test Plan With JMeter

Spring MVC refresh database beans in application context

What you want to do is to configure validation for your connections. When a connection is borrowed from the pool you want to make sure that that connection is valid. For this you can specify the validationQuery property on your datasource.

<bean id="datasourceAR_XXX" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" scope="singleton">
    <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
    <property name="url"><value>jdbc:oracle:thin:@XXX.XXX.com:1500:SERVICE</value></property>
    <property name="maxActive"><value>100</value></property>
    <property name="maxIdle"><value>10</value></property>
    <property name="username"><value>XXX</value></property>        
    <property name="password"><value>XXX</value></property>
    <property name="validationQuery" value="select 1 from dual" />
</bean>

See DBCP - validationQuery for different Databases for a list of possible validation queries for different databases.

There are some issues with Commons DBCP and it is pretty old (although there is a DBCP 2.x now). I would suggest moving to a different datasource like HikariCP this datasource is also a JDBC 4.x based datasource which allows for easier connection validation (it is part of the JDBC 4 spec).

<bean id="datasourceAR_XXX" class="com.zaxxer.hikari.HikariDataSource">
    <property name="datasourceClassName" value="oracle.jdbc.pool.OracleDataSource"/>
    <property name="maximumPoolSize" value="20" />
    <property name="username" value="XXX" />
    <property name="password" value="XXX" />
    <property name="datasourceProperties">
        <props>
            <prop key="serverName">XXX.XXX.com</prop>
            <prop key="port">1500</prop>
            <prop key="databaseName">SERVICE</prop>             
        </props>
    </property>
</bean>

If your oracle driver is new enough you don't need a validation query anymore as validation is provided by the driver instead of needing to be done with a query. Next to that you probably have better results with this pool.

Also you might have a bit of a large pool size, nice article/presentation about pool sizing can be found here.

Why there are two options for database connection pooling with tomcat (tomcat-dbcp and tomcat-jdbc)?

In summary

Notes from a member of the Tomcat commit team (see here):

Tomcat JDBC is Tomcat’s "home grown" database connection pooling and does not use poolPreparedStatements. Tomcat DBCP is Tomcat’s package renamed fork of Apache Commons DBCP 2. Tomcat DBCP is used by default.

The Default DBCP 2 Tomcat Pool

This is the newer of the two pools included in Tomcat and it is the one used by default. It is based on the Commons DBCP 2 pool, as described here.

You can see more details by visiting the official DBCP site.

Tomcat's Home-Grown JDBC Pool

The main documentation page for this is here.

You may see this referred to as "new" in some places in the Tomcat documentation - for example here:

So why do we need a new connection pool?

It was new at one point in time. It was superseded by the DBCP2 pool.

Which One to Use?

That is somewhat a matter of opinion and may also depend on your specific circumstances. You can start with Tomcat's default DBCP 2 pool, if you cannot decide.

Just to add: You can use either of the above pools with Tomcat, or you can use other pools such as HikariCP, c3p0 and so on. You don't have to choose only between the two Tomcat-provided pools.

---

Related Topics