10 Advanced Procedures
10.4 Data Source Templates
10.4.2 XML Settings
The table below shows all settings available. Only the first three are mandatory set-tings:
type-name
driver
connection-url-pattern
If left out, all other settings will automatically use their default values.
Setting Description
type‐name A unique name for the configuration.
driver The JDBC driver Java class used for creating connections.
connection‐url‐pattern A pattern for the connection URL. The URL syntax is driver specific.
ping‐command A dummy command to test connections.
Default: SELECT 1
connection‐properties JDBC connection properties.
metadata‐provider Java class that provides database metadata.
See Spotfire Technology Network. Default: BasicJDBCMetadataProvider sql‐filter Java class that generates SQL.
See Spotfire Technology Network.
Default: BasicSQLFilter
sql‐runtime Java class that handles SQL execution. See Spotfire Technology Network.
Default: BasicSQLRuntime
fetch‐size A fetch size specifies the amount of data fetched with each database round trip for a query. The specified value is shown as the default value in Information Designer. May be changed at instance level.
Default: 10000
batch‐size A batch size specifies the amount of data in each batch update. The specified value is shown as the default value in Information Designer. May be changed at instance level.
Default: 100 max‐column‐name‐
length
The maximum length of a database column name. This limit is used when creating temporary tables.
Default: 30
table‐types Specify which table types to retrieve.
Default: TABLE, VIEW
supports‐catalogs Tells if the driver supports catalogs.
Default: true
supports‐schemas Tells if the driver supports schemas.
Default: true
supports‐procedures Tells if the driver supports stored procedures.
Default: false
supports‐distinct Tells if the driver supports distinct option in SQL queries.
Default: true
supports‐order‐by Tells if the driver supports order-by option in SQL queries.
Default: true
column‐name‐pattern Determines how a column name is written in the SQL query.
Default: “$$name$$”
table‐name‐pattern Determines how a table name is written in the SQL query.
Default: “$$name$$”
schema‐name‐pattern Determines how a schema name is written in the SQL query.
Default: “$$name$$”
catalog‐name‐pattern Determines how a catalog name is written in the SQL query.
Default: “$$name$$”
procedure‐name‐
pattern
Determines how a procedure name is written in the SQL query. Default: “$$name$$”
column‐alias‐pattern Determines how a column alias is written in the SQL query.
Default: “$$name$$”
string‐literal‐quote The character used as quote for string literals; SQL-92 standard.
max‐in‐clause‐size The maximum size of an SQL IN-clause. Larger lists are split into several clauses that are OR:ed together.
Default: 1000 condition‐list‐
threshold
A temporary table is used when executing an SQL query, where total size of a condition list is larger than this threshold value. A Data Base Administrator may prefer a lower value than the default. Depends on the maximum SQL query size.
Default: 10000
expand‐in‐clause If true, an SQL IN-clause will be expanded into OR conditions.
Default: false table‐expression‐
pattern
Determines how a table expression is written in the SQL query;
catalog and schema may be optional (surrounded by brackets).
Default: [$$catalog$$.][$$schema$$.]$$table$$
procedure‐expression‐
pattern
Determines how a procedure expression is written in the SQL query.
Default: [$$catalog$$.][$$schema$$.]$$procedure$$
procedure‐table‐jdbc‐
type
Integer representing the jdbc type identifying a table returned form a procedure as defined by java.sql.Types.
Default: 0 procedure‐table‐type‐
name
Display name for tables from procedure. This is currently not visible to the user in any UI.
Default: null
date‐format‐
expression
An expression that converts a date field to a string value on the format: YYYY‐MM‐DD, e.g., 2002‐11‐19. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the date field.
Default: $$value$$
date‐literal‐format‐
expression
An expression that converts a date literal on the format YYYY‐MM‐
DD to a date field value. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the date literal.
Default: '$$value$$' time‐format‐
expression
An expression that converts a time field to a string value on the format: HH:MM:SS, e.g. 14:59:00. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the time field.
Default: $$value$$
time‐literal‐format‐
expression
An expression that converts a time literal on the format HH:MM:SS to a time field value. Used in WHERE and HAVING clauses. The tag
$$value$$ is a placeholder for the time literal.
Default: '$$value$$' date‐time‐format‐
expression
An expression that converts a datetime field to string value on the format: YYYY‐MM‐DD HH:MM:SS, e.g 2002‐11‐19 14:59:00. Used in WHERE and HAVING clauses. The tag $$value$$ is a
placeholder for the date-time field.
Default: $$value$$
date‐time‐literal‐
format‐expression
An expression that converts a date-time literal on the format YYYY‐
MM‐DD HH:MM:SS to a date-time field value. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the date-time literal.
Type conversions needed when a join data source creates a temporary table for result from a subquery. For String conversion
%s will be replaced by the size of the string. A match-length attribute may be specified (see MySQL). Different String types may be needed dependant of the length of the string. Note that there must be a VARCHAR conversion for when the length of the string is unknown (255 in the example here). When several VARCHAR mappings are specified, the mapping that first matches the match-length is used.
Default: VARCHAR($$value$$) VARCHAR(255) INTEGER BIGINT REAL DOUBLE PRECISION DATE TIME TIMESTAMP temp‐table‐name‐
pattern
Determines how to format a temporary table name in an SQL command.
Default: $$name$$
10.4.2.1 Defining JDBC Connection Properties
The optional <connection-properties> parameter block in the <jdbc-type-settings>
configuration can be used to define JDBC connection properties parameters to be used when connecting to the data sources of the given type. A typical use case is to specify encryption and integrity checksum algorithms for secure database connections.
Each connection property consists of a key-value pair. The syntax for specifying JDBC connection properties for a connection pool is shown in the configuration exam-ple below.
If you need different JDBC connection properties for different data sources of the same type, just duplicate the <jdbc-type-setting> configuration, rename the configura-tions for each variant needed and define the proper JDBC connection properties. Make sure to update any already existing data sources so that they are of the correct type.
create‐temp‐table‐
command
SQL commands for creating a temporary table. This is used to store filter values (when more than condition‐list‐threshold) and to store result from subqueries. The syntax may vary between databases. $$name$$ is a placeholder for the table name.
$$column_list$$ is a placeholder for a column list on the format (name type, name type, ...).
Default: CREATE TEMPORARY TABLE $$name$$ $$column_list$$
drop‐temp‐table‐
command
SQL commands for deleting a temporary table. The syntax may vary between databases. $$name$$ is a placeholder for the table name.
Default: DROP TABLE $$name$$
data‐source‐
authentication
Default value data source authentication. (boolean). This value can be set (overridden) in the Information Interaction Designer.
Default: false
lob‐threshold Threshold when LOB values used as parameters in a WHERE clause, must be written in temporary tables. The default means no limit.
Default: ‐1 use‐ansii‐style‐outer‐
join
The default generated SQL uses the Oracle way with “(+)” to indicate joins. If this setting is set to true an attempt is made to rewrite it to standard ANSII format, making it possible to run on non Oracle databases
Default: false
credentials‐timeout Defines the time in seconds user credentials are cached on the server for a particular data source. Value must be between 900 (15 minutes) and 604800 (1 week). Applicable only if data‐source‐
authentication is set to true.
Default:86400 (24 hours)
Example: Defining JDBC Connection Properties for data source of type oracle. This example creates an encrypted connection to the database.
<jdbc-type-settings>
<type-name>oracle</type-name>
<display-name>Oracle</display-name>
<driver>oracle.jdbc.OracleDriver</driver>
<connection-url-pattern>jdbc:oracle:thin:@<host>:<port1521>:<sid></
connection-url-pattern>
<ping-command>SELECT 1 FROM DUAL</ping-command>
<connection-properties>
<connection-property>
<key>oracle.net.encryption_client</key>
<value>REQUIRED</value>
</connection-property>
<connection-property>
<key>oracle.net.encryption_types_client</key>
<value>( 3DES168 )</value>
</connection-property>
<connection-property>
<key>oracle.net.crypto_checksum_client</key>
<value>REQUIRED</value>
</connection-property>
<connection-property>
<key>oracle.net.crypto_checksum_types_client</key>
<value>( MD5 )</value>
</connection-property>
</connection-properties>
...
</jdbc-type-settings>
10.4.2.2 Advanced Connection Pool Configuration
Information Services uses the same underlying connection pool implementation as the Spotfire Server uses for connecting to its own database. The following special param-eters are available to configure some of the aspects of that connection pool:
spotfire.pooling.data.source.scheme
Corresponds to the pooling‐scheme parameter. See“Database Connectivity” on page 131”.
spotfire.pooling.data.source.connection.timeout
Corresponds to the connection‐timeout parameter. See“Database Connectivity”
on page 131”.
spotfire.pooling.data.source.login.timeout
Corresponds to the login‐timeout parameter. See“Database Connectivity” on page 131”.
spotfire.kerberos.login.context
Corresponds to the kerberos‐login‐context parameter.
All these parameters should be added as JDBC connection properties. However, they will never be used as real JDBC connection properties and will never be sent to a data-base server.
Example: Configuring a connection pool for Oracle databases
<jdbc-type-settings>
<type-name>oracle</type-name>
<display-name>Oracle</display-name>
<driver>oracle.jdbc.OracleDriver</driver>
<connection-url-pattern>jdbc:oracle:thin:@<host>:<port1521>:<sid></
connection-url-pattern>
<ping-command>SELECT 1 FROM DUAL</ping-command>
<connection-properties>
<connection-property>
<key>spotfire.pooling.data.source.scheme</key>
<value>WAIT</value>
</connection-property>
<connection-property>
<key>spotfire.pooling.data.source.connection.timeout</key>
<value>1800</value>
</connection-property>
<connection-property>
<key>spotfire.pooling.data.source.login.timeout</key>
<value>30</value>
</connection-property>
</connection-properties>
...
</jdbc-type-settings>
10.4.2.3 Using Kerberos Authentication for JDBC Data Sources
Configuration of Kerberos authentication for JDBC data source is performed in a sim-ilar way as for the connection to the Spotfire Database. See section “Spotfire Database using Kerberos Authentication” on page 77 for more information.
Example: Configuring a connection pool for Oracle databases
<jdbc-type-settings>
<type-name>oracle</type-name>
<display-name>Oracle</display-name>
<driver>oracle.jdbc.OracleDriver</driver>
<connection-url-pattern>jdbc:oracle:thin:@<host>:<port1521>:<sid></
connection-url-pattern>
<ping-command>SELECT 1 FROM DUAL</ping-command>
<connection-properties>
<connection-property>
<key>spotfire.kerberos.login.context</key>
<value>DatabaseKerberos</value>
</connection-property>
<connection-property>
<key>oracle.net.authentication_services</key>
<value>( KERBEROS5 )</value>
</connection-property>
</connection-properties>
...
</jdbc-type-settings>
10.4.2.4 Using Kerberos Authentication with Delegated Credentials
To make users authenticate to different data sources with their own single sign-on login information, the server can delegate the user authentication to the data source.
This is only possible if you use the Kerberos single sign-on method.
In order to set this up, there are a number of steps that must be taken. Each of these steps is described in detail below.
1 Set up Kerberos authentication as described in the section “Kerberos” on page 53.
Make sure that users are able to log in with this method.
2 Grant the Spotfire Server service account used for client authentication the right to delegate client credentials.
3 Create a JDBC data source template using Kerberos login
Grant the Spotfire Server Service Account the Right to Delegate Client Credentials
If your Window Domain is using Windows Server 2003 or later, grant constrained del-egation rights to the service account: Only the specified accounts can be delegated by the service account. If you are using an earlier version of Windows Server or can’t use this method, grant unconstrained delegation rights. Both methods are described below.
Note: In order for delegation to work, you must also ensure that no client user account in the domain has the setting Account is sensitive and cannot be delegated. By default, this is not set.
To enable constrained delegation:
1 On the Domain Controller, select Start > Programs > Administrative Tools.
2 Select Active Directory Users and Computers.
3 Locate the account.
4 Right-click the account name, and then click Properties to open the account properties.
5 On the Delegation tab, select Trust this user for delegation to specified services only.
Note: The Delegation tab is only visible for accounts that SPNs are mapped to.
6 Select Use any authentication protocol.
7 Click Add…
8 Click Users or Computers… and select the account that the TIBCO Spotfire Server has a keytab for and the SPNs are mapped to.
9 Select all services that apply and click OK.
10 Click Apply.
To enable unconstrained delegation for a on a Domain Controller in Windows 2000 Mixed or Native Mode:
1 On the Domain Controller, select Start > Programs > Administrative Tools.
2 Select Active Directory Users and Computers.
3 Locate the account.
4 Right-click the account name, and then click Properties to open the account properties.
5 Select the Account tab and select Account is trusted for delegation in the Account Options list.
6 Click Apply.
To Enable Unconstrained Delegation on a Domain Controller in Windows Server 2003 Mode:
1 On the Domain Controller, select Start > Programs > Administrative Tools.
2 Select Active Directory Users and Computers.
3 Locate the account.
4 Right-click the account name, and then click Properties to open the account properties.
5 On the Delegation tab, select Trust this user for delegation to any service (Kerberos only), and then click Apply.
Note: The Delegation tab is only visible for accounts that SPNs are mapped to.
Create an Information Services data source template using Kerberos login The default Information Services Data Source templates shipped with Spotfire Server are not configured to use Kerberos. You must therefore create a new data source tem-plate based on one shipped.
To Create an Information Services data source using Kerberos login:
1 Use the list‐ds‐template (page 184) command to list the existing data source templates and select one that matches the database you are setting up, for example Oracle.
2 Use the export‐ds‐template (page 179) command to export the definition of the selected data source template.
3 In a text editor, open the exported definition file.
Add the JDBC connection property key spotfire.connection.pool.factory.data.source with the value kerberos.data.source within the connection‐properties element. If there is no connection‐properties element, create one.
You may have to addThere may also be other connection properties you must add -
ens till 10.4.2.1 som ju innehåller generella instruktioner för hur man lägger till con-nection properties).
Example:
<jdbc-type-settings>
<type-name>oracle</type-name>
<driver>oracle.jdbc.OracleDriver</driver>
<connection-url-pattern>jdbc:oracle:thin:@<host>:<port1521>:<sid></
connection-url-pattern>
<ping-command>SELECT 1 FROM DUAL</ping-command>
<connection-properties>
<connection-property>
<key>spotfire.connection.pool.factory.data.source</key>
<value>kerberos.data.source</value>
</connection-property>
<connection-property>
<key>oracle.net.authentication_services</key>
<value>(KERBEROS5)</value>
</connection-property>
</connection-properties>
4 Use the add‐ds‐template command (page 147) to add the new data source template with a suitable name, like oracle_kerberos, using the modified template definition.
5 Import the configuration and restart the server as described in the “Configuration Workflow” on page 43.
10.4.2.5 Verifying a Data Source Template
To verify the data source template from TIBCO Spotfire:
1 Log into TIBCO Spotfire as an administrator.
2 Select Tools > Create Information Link....
3 Click on the Setup Data Source link.
4 Enter a name for the data source connection.
5 Specify the type of data source.
6 Enter the connection URL.
7 Enter max/min-values for the connection pool.
8 Enter a username and a password to connect to the database.
Note: Does not apply to Kerberos.
9 Click Save.
10 Click on the Data sources tab in the left pane.
Response: The data source name should appear in the tree to the left, ready for use.