Configuration options to automatically detect and purge stale JDBC connections in TIBCO Spotfire

Products	Versions
Spotfire Server	All Versions

Description

The TIBCO Spotfire connection pool implementation attempts to detect and purge stale connections by default, and this functionality has been gradually improved in newer versions. This article describes the optional connection properties that can be used in a Data Source templates to automatically detect and purge the stale data source connections that are created using Information Designer in TIBCO Spotfire. This is not normally required, but if you see issues with stale connections then these connection properties can be used to have more control over the connection management.

Issue/Introduction

This article describes the optional connection properties that can be used in a Data Source templates to automatically detect and purge the stale data source connections that are created using Information Designer in TIBCO Spotfire.

Resolution

Datasource connections made through Information Designer in TIBCO Spotfire can be verified either periodically or when returned to the connection pool. The following connection properties can be used. All the key value properties should be added in the data source template as follows:

<connection-properties>
   <connection-property>
       <key>key_name</key>
       <value>value_corresponding_to_the_key</value>
   </connection-property>
   <connection-property>
   ...
   ...
   </connection-property>
   ...
   ...
</connection-properties>

Optional Connection Properties:

1. background-ping-query

Key: spotfire.pooling.data.source.background-ping-query (similar to <ping-command>)
Value: Any query, for example "select 1 from table_name"

The query will be executed every time a connection is retrieved from the connection pool

If ping query is mentioned, you can either use "spotfire.pooling.data.source.verify-connections-on-return" which checks for the data source connections when the connection is returned to the pool or use "spotfire.pooling.data.source.verify-connections-periodically" for verify periodically. Only one of the following 2 properties should be used

2. verify-connections-on-return

Key: spotfire.pooling.data.source.verify-connections-on-return
Value: A boolean value (Default : false)

3. verify-connections-periodically

Key: spotfire.pooling.data.source.verify-connections-periodically
Value: A boolean value(Default : false)

If you choose to verify connections periodically, set how often connections should be verified with the below property

4. connection-verification-interval-seconds

Key: spotfire.pooling.data.source.connection-verification-interval-seconds
Value: Default is 60 (in seconds)

If you still need to manually kill existing connections you can do so through a JMX connection. See Server monitoring using JMX for details.