Closed connections from database cause error

Content

For performance reasons, in VDP Server, the JDBC and ODBC data sources keep a pool of open connections to the database. If the database is restarted, or if a connection expires, a query that uses the connection will receive an error. To avoid this, configure each data source’s pool of connections to validate the connection before using it. To configure the connection pool in from the Denodo Web Design Studio:

1. Select the data source.
2. Advanced tab.
3. Connection Pool Configuration section.

From the Denodo VDP Admin Tool:

1. Select the data source.
2. Click on "Connection Pool configuration".

Starting from Denodo 6, the connection validation is enabled by default. For previous versions, we recommend enabling it following the steps described below:

To enable it, select the checkbox Test connections checked. A ping query should also be provided.

This ping query is used to validate the connection by executing it before the connection is used in a query. The ping query should consume as little resources in the database as possible. If the validation fails, a new connection will be created. The connections will only be validated if the field “Ping query” contains a query. Otherwise, they will not even if this check box is selected.

By activating Test Connections, neither restarting the database nor an expired connection will cause a query to fail.

The Virtual DataPort Administration Guide provides additional details on how to configure the connection pool for JDBC and ODBC data sources.

References

Importing JDBC Sources

ODBC Sources