The Spotfire database holds all user data and most of the configuration for the Spotfire system. In order to connect to the Spotfire database that Spotfire Server uses a data-base connection pool. See “Datadata-base Connectivity” on page 179.
The bootstrap.xml file contains the information the server needs to connect to the Spot-fire database and retrieve the configuration. Refer to “bootstrap.xml” on page 176.
After the server has retrieved the configuration from the database it will re-initialize its database connection pool using information from both the bootstrap.xml file, which is present on each server, and any database configuration set for the entire cluster, stored as part of the database persisted server configuration.
To configure the common database configuration:
Use the commands modify‐db‐config (page 267) and set‐db‐config (page 275).
Database Connectivity
Spotfire Server has a database connection pool implementation that is used for two things:
To connect to the Spotfire database
To connect to JDBC compliant data sources through Information Services
Each connection pool (either for Spotfire Server itself or for fetching data) has many parameters, where the following are of general interest:
The driver‐class parameter contains the JDBC driver class name. See “Database Connection URL Examples” on page 181.
The url parameter contains the JDBC connection URL. See “Database Connection URL Examples” on page 181.
The username parameter contains the name of the database user to connect as, if applicable.
The password parameter contains the password for the specified database user, if applicable. The password is always encrypted and must therefore be set using the bootstrap command (page 195). It cannot be set manually.
The min‐connections parameter contains the minimum number of allocated connections.
created by the server may be higher than the value of this parameter during high load, but all such extra connections will automatically be closed when the load decreases. By setting this parameter to zero or a negative value, connection pooling is effectively disabled and new connections will be continuously created, whenever needed.
The pooling‐scheme parameter defines the connection pooling algorithm to be used. There are two possible connection pooling algorithms that determine the way the connection pool operates, “DYNAMIC” and “WAIT”. The “WAIT”
algorithm is default. The pooling algorithms are described in detail below.
The “DYNAMIC” pooling scheme
When initialized, the connection pool creates a number of idle database connections equal to the min‐connections parameter. When the connection pool receives a request for a database connection, it checks if the pool contains any idle connections and uses one of those, if available. If there are no idle connections in the pool, it automatically creates a new database connection. There is no upper limit for how many connections a connection pool can have open at the same time.
Idle connections in the pool eventually time out if they aren’t used. The connec‐
tion‐timeout parameter defines how long time (given in seconds) a connection can stay idle in the connection pool before being closed and discarded.
The “WAIT” pooling scheme
When initialized, the connection pool creates a number of idle database connections equal to the min‐connections parameter. When the connection pool receives a request for a database connection, it checks if the pool contains any idle connections and uses one of those, if available:
If there are no idle connections in the pool and the number of already open connections is less than the max‐connections parameter, it creates a new database connection.
If the number of already open connections is equal to the max‐connections parameter, it waits for an active connection to be returned to the pool. If the request cannot be fulfilled within a number of seconds equal to the login‐timeout parameter, the request times out. In the server logs entries like this appear:
“Timeout while waiting for database connection after 10 seconds”.
Thus, in WAIT mode, the connection pool can never have more open (active or idle) connections than the value of the max‐connections parameter. Whenever a database connection is returned, it is put in the pool of idle connections, unless it is used imme-diately to fulfill an already waiting request.
Idle connections in the pool eventually time out if they aren’t used. The connec‐
tion‐timeout parameter defines how long time (given in seconds) a connection can stay idle in the connection pool before being closed and discarded.
This section contains database connection URL examples for connecting to the Spot-fire database.
Databases and JDBC drivers supported for running Spotfire Server
Oracle (DataDirect Driver)
Driver name: tibcosoftwareinc.jdbc.oracle.OracleDriver
Oracle (Oracle JDBC Thin Driver, ojdbc6.jar) Driver name: oracle.jdbc.OracleDriver
Microsoft SQL Server (DataDirect Driver)
Driver name: tibcosoftwareinc.jdbc.sqlserver.SQLServerDriver
Microsoft SQL Server (Microsoft JDBC Driver, sqljdbc4.jar) Driver name: com.microsoft.sqlserver.jdbc.SQLServerDriver
Microsoft SQL Server (jTDS JDBC Driver, jtds.jar) Driver name: net.sourceforge.jtds.jdbc.Driver
Database Connection URL Components
Component Description
API Specifies which API to use. This is always jdbc.
Database Driver Specifies which database driver to use to connect to the database. Default tibcospotfireinc, which will use the Spotfire DataDirect driver. If you have installed a different driver, you may provide this here.
Server Type Specifies the type of database server. Either sqlserver or oracle.
Note: Server Type is only applicable when using the DataDirect driver.
Hostname Specifies the hostname of the database server.
Port Specifies the port which the database server listens to; for example 1433.
Database Name or SID Specifies the name (MSSQL) or SID (Oracle) that defines your Spotfire database.
Options Specifies further options, separated with semicolons. Only necessary if you need to set something specific for your database server. This may include information such as a named Instance in an MSSQL server, for example. See example below.
For the different supported database drivers the URL components become:
Oracle (DataDirect Driver):
[API]:[DBDriver]:[ServerType]://[Hostname]:[Port];SID=[SID]
Example:
jdbc:tibcospotfireinc:oracle://dbsrv.example.com:1433;SID=spotfire_server Oracle (Vendor Driver, ojdbc6.jar):
[API]:[DBDriver]:[DriverType]://[Hostname]:[Port]:SID Example:
jdbc:oracle:thin:@dbsrv.example.com:1521:orcl Microsoft SQL Server (DataDirect Driver)
[API]:[DBDriver]:[ServerType]://[Hostname]:[Port];DatabaseName=[DBName]
Example:
jdbc:tibcosoftwareinc:sqlserver://dbsrv.example.com:1433;DatabaseName=
spotfire_server
Example using Integrated Authentication:
jdbc:tibcosoftwareinc:sqlserver://dbsrv.example.com:1433;DatabaseName=
spotfire_server;AuthenticationMethod=ntlm;LoadLibraryPath=c:/tibco/tss/6.5.0/tomcat/
lib
Note: Make sure that the LoadLibraryPath has the correct path to the tomcat/lib direc-tory in Spotfire Server installation direcdirec-tory.
Microsoft SQL Server (Vendor Driver, sqljdbc4.jar)
[API]:[DBDriver]://[Hostname]:[Port];DatabaseName=[DBName]
Example:
jdbc:sqlserver://dbsrv.example.com:1433;DatabaseName=spotfire_server;selectMethod=
cursor
Example: Making sure that the driver always returns will prevent infinite waits during adverse conditions
jdbc:sqlserver://dbsrv.example.com:1433;DatabaseName=spotfire_server;lockTimeout=
<X, where X is a good value>
Note: Due to a restriction in the vendor Microsoft SQL Server driver, you may need to add the option responseBuffering=adaptive to your connection string. This is neces-sary if you are going to store large analysis files in the library.
jdbc:sqlserver://dbsrv.example.com:1433;databaseName=spotfire_server;selectMethod=
cursor;responseBuffering=adaptive
Example: Using Integrated Authentication:
jdbc:sqlserver://dbsrv.example.com:1433;DatabaseName=spotfire_server;selectMethod=
cursor;integratedSecurity=true;
Note: For integrated authentication to work, you must place the file sqljdbc_auth.dll in a folder in the system path, such as C:\Windows\System32. This file is included with the vendor drivers from Microsoft.
jTDS (Vendor Driver, jtds.jar)
jdbc:jtds:sqlserver://[Hostname]:[Port]/[DatabaseName]
Example:
jdbc:jtds:sqlserver://dbsrv.example.com:1433/spotfire_server Microsoft SQL Server with Named Instance
If your database server uses a named Instance, you must add an option to the end of the connection string to identify it. Note that this option is not limited to the jTDS driver. You can add this option regardless of what driver you are using.
jdbc:jtds:sqlserver://[Hostname]:[Port]/[DatabaseName];instance=[InstanceName]
Example:
jdbc:jtds:sqlserver://dbsrv.example.com:1433/spotfire_server;instance=FirstInstance Note: To use other JDBC drivers than the DataDirect ones, you need to install them onto each Spotfire Server. Refer to the section “Install Database Drivers” on page 27 for more information about this.
Implementation
Spotfire Server can be deployed to multiple machines, creating a server cluster. This can be used to achieve failover and to balance load between Spotfire Servers. Each Spotfire Server in a cluster communicates with the Same Spotfire database.
While it is possible to allow users to connect directly to any of the Spotfire Servers, a more common approach is to use a load balancer. A load balancer is a computer that acts as a front for the Spotfire Servers. The clients connect to the load balancer, which then redirects their communication to an available Spotfire Server. The client has to communicate with the same server during the entire session; that is, sticky sessions or session affinity is maintained. The load balancer must support session affinity and be able to detect if a Spotfire Server becomes available or unavailable.
It is possible to use any load balancing technology that supports session affinity. Spot-fire Server is implemented using Apache Tomcat, which means that it supports load balancing via AJP (Apache JServ Protocol).
This chapter is a reference implementation for a load balancing setup using AJP and a load balancer implementation using Apache HTTP Server with the mod_jk module.
The Apache HTTP Server is not supported by TIBCO.
If you intend to use a login method that authenticates users with an external directory, this may have implications on how the load balancer should be set up.
The procedure is to set up one Spotfire Server to be load balanced. When traffic is being forwarded as it should, others can be added.
LB.1 Prerequisites
Spotfire Server
One or more Spotfire Servers installed. It may be a good idea to start with one server and then add more later.
Load Balancer
A load balancer that supports session affinity. If this is the Apache HTTP Server, it will need:
The mod_jk module installed and enabled.
Optional: If NTLM authentication is used, the mod_auth_sspi module installed and enabled.
To enable Spotfire Server to communicate with a load balancer using the AJP protocol the <server installation directory>/tomcat/conf/server.xml file has been edited.
The following connector section has been uncommented:
<!-- Enable this connector if you want to use a load balancer that supports the Apache JServ Protocol -->
<Connector port="8009"
protocol="AJP/1.3"
packetSize="65536"
URIEncoding="UTF-8"/>
Optionally, to prevents clients from connecting to Spotfire Server directly, forcing them to use the load balancer, HTTP communication can be turned off by commenting out the following connector section:
<Connector port="80"
maxHttpHeaderSize="16384"
connectionTimeout="30000"
enableLookups="false"
URIEncoding="UTF-8"
disableUploadTimeout="true"
server="TIBCO Spotfire Server"/>