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

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

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. 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.
  Wiki: Example Templates

•  https://community.tibco.com/wiki/tibco-spotfire-jdbc-data-access-connectivity-details#toc-12

Doc: Adding a data source template in the configuration tool

• https://docs.tibco.com/pub/spotfire_server/10.3.6/doc/html/TIB_sfire_server_tsas_admin_help/GUID-CC0AF293-611A-4D0E-A437-B52039A3BE44.html

Doc: JDBC Connection properties

• https://docs.tibco.com/pub/spotfire_server/10.3.6/doc/html/TIB_sfire_server_tsas_admin_help/GUID-036A7B5B-580F-4C87-B8D6-3877A6730FA4.html

Doc: XML settings for data source templates

• https://docs.tibco.com/pub/spotfire_server/10.3.6/doc/html/TIB_sfire_server_tsas_admin_help/GUID-1BE3A384-25F2-4CA1-966E-534B39239CD0.html

Doc: Server monitoring using JMX

• https://docs.tibco.com/pub/spotfire_server/10.3.7/doc/html/TIB_sfire_server_tsas_admin_help/GUID-BB4F323F-05F6-431E-9036-776053F30AA7.html